Query for product billing table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query for product billing table

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2011-07-25 : 13:47:27

Friends,
I have product billing table,

Item Code Product Running Bill Rate Qty Amount
1001 AC 1 4000 1 4000
1001 AC 1 4000 2 8000
1002 Ash 1 5000 1 5000
1003 kmar 1 2500 1 2500
1001 AC 2 4000 2 8000
1002 Ash 2 5000 2 10000
1003 kmr 2 2500 2 5000
1001 AC 3 4000 1 4000
1002 Ash 3 5000 1 5000
1003 kmar 3 2500 2 5000

Please find the expected output when i pass Item code, and Running Bill

Item Code=1001 and Running Bill =1
-----------------------------------------
Item Previous.Qty Pre.Amnt ThisQty This_Amount TotalQty Total Amount
1001 0 0 3 12000 3 12000

Item Code=1002 and Running =1
----------------------------------------
1002 0 0 1 5000 1 5000

Item Code=1001 and Running Bill =2
---------------------------------------
1001 3 12000 2 8000 5 20000

Item Code=1002 and Running Bill =2
-----------------------------------
1002 1 5000 2 10000 3 15000

Item Code=1001 and Running Bill =3
--------------------------------------
1001 5 20000 1 4000 6 24000

Pre.Qty = If bill number is 3, we have to sum Qty of bill < 3 ,if bill number is 1 previous qty is 0

Pre.Amount = If bill number is 4 we have to add the Amount of all the previous bills i.e sum of < 4

TotalQty = Pre.Qty + this qty
Total Amount = Pre Amount + this Amount

please help me to get the expected output.

thanks and looking forward

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-25 : 14:02:08

[code]
;With CTE (ItemCode,RunningBill,Qty,Amt)
AS
(
SELECT ItemCode,RunningBill,SUM(Qty),SUM(AMt)
FROM table
GROUP BY ItemCode,RunningBill
)

SELECT c.ItemCode,c1.PrevQty,c1.PrevAmt,c.Qty,c.Amt,COALESCE(c1.PrevQty,0) + c.Qty AS TotalQty,COALESCE(c1.PrevAmt,0) + c.Amt AS TotalAmt
FROM CTE c
OUTER APPLY (SELECT SUM(Qty) AS PrevQty,SUM(Amt) AS PrevAmt
FROM CTE
WHERE ItemCode=c.ItemCode
AND RunningBill < c.RunningBill
)c1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dhinasql
Posting Yak Master

195 Posts

Posted - 2011-07-26 : 04:46:01

quote:

Originally posted by visakh16


;With CTE (ItemCode,RunningBill,Qty,Amt)
AS
(
SELECT ItemCode,RunningBill,SUM(Qty),SUM(AMt)
FROM table
GROUP BY ItemCode,RunningBill
)

SELECT c.ItemCode,c1.PrevQty,c1.PrevAmt,c.Qty,c.Amt,COALESCE(c1.PrevQty,0) + c.Qty AS TotalQty,COALESCE(c1.PrevAmt,0) + c.Amt AS TotalAmt
FROM CTE c
OUTER APPLY (SELECT  SUM(Qty) AS PrevQty,SUM(Amt) AS PrevAmt 
              FROM CTE
              WHERE ItemCode=c.ItemCode
              AND RunningBill < c.RunningBill
              )c1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hello Visakh,

Thank you for your Reply.

My apologies there is some changes in column. Product_Code and SPB code is newly added

Item_Code Running Bill Rate Qty Amount Product_Code SPB Code
1001 1 4000 1 4000 5001 2001
1001 1 2000 2 4000 5001 2002
1001 1 1500 1 1500 5002 2002
1002 1 4000 1 4000 5001 2001
1003 1 2500 1 4000 5002 2001

1001 2 4000 2 8000 5001 2001
1001 2 4000 2 8000 5001 2002
1001 2 2500 2 5000 5002 2001

1001 3 4000 1 4000 5001 2001
1001 3 2500 1 2500 5002 2001
1001 3 2500 2 5000 5002 2001

Please find the expected output

ItemCode =1001 and Running Bill =1
-----------------------------------------
ItemCode Product Code SPB Code Previous.Qty Pre.Amnt ThisQty This_Amount TotalQty Total Amount
1001 5001 2001 0 0 1 8000 1 8000
1001 5001 2002 0 0 2 8000 2 8000
1001 5002 2001 0 0 0 0 0 0
1001 5002 2002 0 0 1 1500 1 1500

ItemCode =1001 and Running Bill =2
-----------------------------------------
ItemCode Product Code SPB Code Previous.Qty Pre.Amnt ThisQty This_Amount TotalQty Total Amount
1001 5001 2001 1 8000 1 4000 1 4000
1001 5001 2002 2 8000 2 8000 2 8000
1001 5002 2001 0 0 2 5000 2 5000
1001 5002 2002 1 1500 0 0 1 1500

ItemCode =1001 and Running Bill =3
-----------------------------------------
ItemCode Product Code SPB Code Previous.Qty Pre.Amnt ThisQty This_Amount TotalQty Total Amount
1001 5001 2001 1 8000 1 8000 1 8000
1001 5001 2002 2 8000 0 0 2 8000
1001 5002 2001 2 5000 3 7500 5 12500
1001 5002 2002 1 1500 0 0 1 1500

If The Product_Code, and SPB code is same we have to sum it and display.

If the product_code and SPB code is differ we have to display it separately.

Thanks and looking forward to hear from you...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-26 : 12:12:20

[code]
;With CTE (ItemCode,RunningBill,Product_Code,[SPB code],Qty,Amt)
AS
(
SELECT t.ItemCode,t.RunningBill,f.Product_Code,f.[SPB code],SUM(t1.Qty),SUM(t1.AMt)
FROM (SELECT DISTINCT ItemCode,RunningBill FROM table) t
CROSS JOIN (SELECT DISTINCT Product_Code,[SPB code] FROM Table)f
LEFT JOIN table t1
ON t1.itemCode = t.ItemCode
AND t1.RunningBill = t.RunningBill
AND t1.Product_code = f.Product_code
AND t1.[SPB Code] = f.[SPB Code]
GROUP BY t.ItemCode,t.RunningBill,f.Product_Code,f.[SPB code]
)

SELECT c.ItemCode,c.RunningBill,c.Product_Code,c.[SPB code],c1.PrevQty,c1.PrevAmt,c.Qty,c.Amt,COALESCE(c1.PrevQty,0) + c.Qty AS TotalQty,COALESCE(c1.PrevAmt,0) + c.Amt AS TotalAmt
FROM CTE c
OUTER APPLY (SELECT SUM(Qty) AS PrevQty,SUM(Amt) AS PrevAmt
FROM CTE
WHERE ItemCode=c.ItemCode
AND Product_Code = c.Product_Code
AND [SPB code] = [SPB code]
AND RunningBill < c.RunningBill
)c1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources