Help with a very complex query
-
Hi all, I am being struglling with a query from past 2-3 days and i m unable to find a solution to it. I am using sql 2000 and i am having the foll probs. I have a table "FG_STK" which stores the stock of all items branch wise. The schema is FG_ID,BranchID,Item_Code,Item_Batch_No,Item_Lot_No,Recon_Code,CLD_Qty,Sku_Qty and other table called Recon_Codes which has foll schema.. Recon_Code,Recon_Type,Recon_Description which has predefined values such as 100 CR Opening Stock 200 DR Stock Removed 8000 DR Damage Material Transfer 9000 DR Expired Material Transfer and in FG_STK the data stored as 1, 2001,350154,Mar809,Mar1208,100,100,0 (item 350154 at branch 2001 with batch Mar809 and Lot Mar1208 with Opening stock of 100 Clds) 2,2002,350154,Feb0809,Feb1208,8000,0,200 (item 350154 with loose qty 200 at branch 2002 with batch Feb0809 and Lot Feb1208 is removed for the purpose of transfer because its damaged) The rows in FG_STk with recon code not starting with 8 and 9 are considered as good stock, while rows with recon code starting with 8 are considered as damaged and with 9 are considered as expired. Now to fetch good clds i did the following Select item.Brand,item.Category,item.Item_Desc as Item,stock_data.batchno,stock_data.lotno,isnull((SUM(CR) - SUM(DR)),0) as Total_Clds,isnull((SUM(CRSKU)-SUM(DRSKU)),0) AS Tot_Sku From( SELECT Case Recon_Type When 'CR' Then SUM(CLD_Qty) When 'DR' Then 0 END CR, Case Recon_Type When 'CR' Then 0 When 'DR' Then SUM(CLD_Qty) END DR, Case Recon_Type When 'CR' Then SUM(Sku_Qty) When 'DR' Then 0 END CRSKU, Case Recon_Type When 'CR' Then 0 When 'DR' Then SUM(Sku_Qty) END DRSKU,Item_Code,Item_Batch_No as batchno,Item_Lot_No as lotno FROM FG_STK INNER JOIN Recon_Codes ON FG_STK.Recon_Code = Recon_Type.Recon_Code where BranchID=@branch_ID GROUP BY Recon_Type,Item_Code ,Item_Batch_No,Item_Lot_No )stock_data inner join view_Full_Item_Details item on stock_data.Item_Code=item.code group by stock_data.Item_Code,Item_Desc,stock_data.batchno,stock_data.lotno,item.Brand,item.Category having ((SUM(CR) - SUM(DR))<>0 or (SUM(CRSKU)-SUM(DRSKU))<>0) Now t
-
Hi all, I am being struglling with a query from past 2-3 days and i m unable to find a solution to it. I am using sql 2000 and i am having the foll probs. I have a table "FG_STK" which stores the stock of all items branch wise. The schema is FG_ID,BranchID,Item_Code,Item_Batch_No,Item_Lot_No,Recon_Code,CLD_Qty,Sku_Qty and other table called Recon_Codes which has foll schema.. Recon_Code,Recon_Type,Recon_Description which has predefined values such as 100 CR Opening Stock 200 DR Stock Removed 8000 DR Damage Material Transfer 9000 DR Expired Material Transfer and in FG_STK the data stored as 1, 2001,350154,Mar809,Mar1208,100,100,0 (item 350154 at branch 2001 with batch Mar809 and Lot Mar1208 with Opening stock of 100 Clds) 2,2002,350154,Feb0809,Feb1208,8000,0,200 (item 350154 with loose qty 200 at branch 2002 with batch Feb0809 and Lot Feb1208 is removed for the purpose of transfer because its damaged) The rows in FG_STk with recon code not starting with 8 and 9 are considered as good stock, while rows with recon code starting with 8 are considered as damaged and with 9 are considered as expired. Now to fetch good clds i did the following Select item.Brand,item.Category,item.Item_Desc as Item,stock_data.batchno,stock_data.lotno,isnull((SUM(CR) - SUM(DR)),0) as Total_Clds,isnull((SUM(CRSKU)-SUM(DRSKU)),0) AS Tot_Sku From( SELECT Case Recon_Type When 'CR' Then SUM(CLD_Qty) When 'DR' Then 0 END CR, Case Recon_Type When 'CR' Then 0 When 'DR' Then SUM(CLD_Qty) END DR, Case Recon_Type When 'CR' Then SUM(Sku_Qty) When 'DR' Then 0 END CRSKU, Case Recon_Type When 'CR' Then 0 When 'DR' Then SUM(Sku_Qty) END DRSKU,Item_Code,Item_Batch_No as batchno,Item_Lot_No as lotno FROM FG_STK INNER JOIN Recon_Codes ON FG_STK.Recon_Code = Recon_Type.Recon_Code where BranchID=@branch_ID GROUP BY Recon_Type,Item_Code ,Item_Batch_No,Item_Lot_No )stock_data inner join view_Full_Item_Details item on stock_data.Item_Code=item.code group by stock_data.Item_Code,Item_Desc,stock_data.batchno,stock_data.lotno,item.Brand,item.Category having ((SUM(CR) - SUM(DR))<>0 or (SUM(CRSKU)-SUM(DRSKU))<>0) Now t
It's quite hard to say the exact solution with such small amount of data, but could you simply add the conditions to the inline view and use case to differentiate good, expired and damaged amounts. If I understood the idea correctly you would use double condition in case. Something like:
WHEN RECON_TYPE = 'CR' THEN ...
...
WHEN RECON_TYPE = 'DR' AND RECON_CODE NOT IN (8000, 9000) THEN ...
...
WHEN RECON_TYPE = 'DR' AND RECON_CODE = 8000 THEN ...
...
WHEN RECON_TYPE = 'DR' AND RECON_CODE = 9000 THEN ...
...Also if you have the possibility to re-check the E/R-model, I think there would be easier ways to handle the data than how it's currently done.
The need to optimize rises from a bad design.My articles[^]