Not grouping through latest dates Code Answer

Hello Developer, Hope you guys are doing great. Today at Tutorial Guruji Official website, we are sharing the answer of Not grouping through latest dates without wasting too much if your time.

The question is published on by Tutorial Guruji team.

I have 3 tables as follows:

BarcodeGroupMap

enter image description here

CategoryMaster

enter image description here

ProductMaster:

enter image description here

From these 3 tables i have made following query:

select bgm.BarcodeItem,
cm.CategoryID,cm.CategoryName,pm.ProductName,pm.ProductID,bgm.EffectFrom,bgm.Groups
from BarcodeGroupMap bgm,CategoryMaster cm,ProductMaster pm where
bgm.ProductID=pm.ProductID  and bgm.categoryID=cm.CategoryID

This query results into following:

enter image description here

Now, as we can see in query result barcode is repeating,

I just wanted to show one barcode once, according to Its latest Createddate from Barcodegroupmap table.

For this i made following query :

select bgm.BarcodeItem,
cm.CategoryID,cm.CategoryName,pm.ProductName,pm.ProductID,bgm.EffectFrom,bgm.Groups
from BarcodeGroupMap bgm,CategoryMaster cm,ProductMaster pm where
bgm.ProductID=pm.ProductID  and bgm.categoryID=cm.CategoryID and
bgm.BarcodeItem= select BarcodeItem from  BarcodeGroupMap bm1  where
CreatedDate=  (select top 1 CreatedDate from  BarcodeGroupMap bm2  
)order by bm1.BarcodeItem

But its not giving me correct result.

Please help me.

I just want to display one barcode item only one time according to its latested createddate in Barcodegroupmap.

Answer

I have no way of actually trying this query, because I do not have relevant tables and data, but this should get you started:

SELECT BarcodeItem, temp.CategoryID, cm.CategoryName, temp.ProductID, pm.ProductName, EffectFrom FROM ( 
  SELECT BarcodeItem, CategoryID, ProductID, CONVERT(date, EffectFrom) as EffectFrom,
    RANK() OVER (PARTITION BY BarcodeItem ORDER BY EffectFrom DESC) dest_rank
    FROM BarcodeGroupMap
  ) temp
  inner join CategoryMaster cm on cm.CategoryID = temp.CategoryID
  inner join ProductMaster pm on pm.ProductID = temp.ProductID
  where temp.dest_rank = 1
We are here to answer your question about Not grouping through latest dates - If you find the proper solution, please don't forgot to share this with your team members.

Related Posts

Tutorial Guruji