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.
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'