asked
grouping based on critiria
I have a customer order log table like bellow Orderid lgdate amount type 1001 01/01/2015 0 Advance 1002 02/01/2015 200 Advance 1001 03/01/2015 300 Balance 1003 03/01/2015 200 Advance 1003 05/01/2015 0 Balance 1002 06/01/2015 250 Balance One customer order has one advance and one balance. Each customer order will close when it's balance received even it's balance is 'zero'. total amount of customer order is advance+balance.If I select a date i need to know how many orders are closed in that day.
eg.In 06/01/2015 order 1002 closed and total amount is 450. like 1002 06/01/2015 450. how to write a workable sql code for the above result.
You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
Something like this should work: declare @date date = '06/01/2015' --Needs a less ambiguous date format select a.Orderid , a.amount as Advance , b.amount as Balance , a.amount + b.amount as Total from ( select Orderid , amount from tablename where type = 'Advance' and lgdate = @date ) a inner join ( select Orderid , amount from tablename where type = 'Balance' and lgdate = @date ) b on b.Orderid = a.Orderid order by a.OrderId;
This will group the OrderId's together and give you the total SELECT OrderID, SUM(amount)[Total] FROM TABLE GROUP BY OrderID if you join his back onto the table you can then get your result: SELECT * FROM TABLE x JOIN ( SELECT OrderID, SUM(amount)[Total] FROM TABLE GROUP BY OrderID ) y ON x.OrderID= y.OrderID WHERE lgdate= '2015-01-06' AND type = 'Balance'
20 People are following this question.
