CODEWITHSUNDEEP
sql
Henry
Henry

Reputation: 167

sum qty with different date SQL Server

I have 2 tables,

table 1 is transaction table

+----------+-----------+---------+------------+-----+
| IDOutlet | IDProduct | TrxType |  TrxDate   | Qty |
+----------+-----------+---------+------------+-----+
|      101 | ASD11     |       2 | 11/11/2015 |  15 |
|      101 | ASD11     |       3 | 11/14/2015 |  -3 |
|      101 | ASD11     |       3 | 11/17/2015 |  -6 |
|      101 | ASD11     |       2 | 11/22/2015 |   7 |
|      101 | ASD11     |       3 | 11/26/2015 |  -2 |
|      101 | ASD11     |       2 | 12/3/2015  |   1 |
|      101 | ASD11     |       3 | 12/9/2015  |  -3 |
|      101 | ASD11     |       3 | 12/11/2015 |  -2 |
|      101 | ASD11     |       2 | 12/12/2015 |   5 |
|      101 | FFD34     |       2 | 11/11/2015 |   9 |
|      101 | FFD34     |       3 | 11/14/2015 |  -3 |
|      101 | FFD34     |       2 | 11/16/2015 |   3 |
|      101 | FFD34     |       3 | 11/19/2015 |  -4 |
|      101 | FFD34     |       3 | 11/23/2015 |  -3 |
|      102 | FFD34     |       2 | 11/26/2015 |   2 |
|      102 | FFD34     |       2 | 11/28/2015 |   4 |
|      102 | FFD34     |       3 | 11/29/2015 |  -5 |
|      102 | FFD34     |       3 | 12/1/2015  |  -1 |
+----------+-----------+---------+------------+-----+

Table 2 is opnametable

+----------+-----------+------------+-----------+
| IDOutlet | IDProduct | OpnameDate | QtyOpname |
+----------+-----------+------------+-----------+
|      101 | ASD11     | 11/20/2015 |         5 |
|      101 | FFD34     | 11/30/2015 |         5 |
|      102 | FFD34     | 11/30/2015 |         1 |
+----------+-----------+------------+-----------+

And I want the result like this

+----------+-----------+------------+---------+
| IDOutlet | IDProduct | OpnameDate | Sum Qty |
+----------+-----------+------------+---------+
|      101 | ASD11     | 11/20/2015 |       6 |
|      101 | FFD34     | 11/20/2015 |       5 |
|      102 | FFD34     | 11/30/2015 |       1 |
+----------+-----------+------------+---------+

Upvotes: 1

Views: 53

Answers (2)

Nithila Shanmugananthan
Nithila Shanmugananthan

Reputation: 336

Try this 

select o.IDOutlet,o.IDProduct,sum(t.Qty) as [Sum Qty]
from opnametable o left outer join transaction1 t on  o.IDOutlet=t.IDOutlet
and o.IDProduct=t.IDProduct and t.trxdate<o.OpnameDate
group by o.IDOutlet,o.IDProduct

Upvotes: 0

Hart CO
Hart CO

Reputation: 34774

You can use a date comparison in your JOIN criteria:

SELECT  T2.IDOutlet,T2.IDProduct,T2.OpnameDate,SUM(T1.Qty) AS Sum_Qty
FROM opnametable T2
LEFT JOIN transaction T1
  ON T2.IDOUtlet = T1.IDOutlet
 AND T2.IDProduct = T1.IDProduct
 AND T1.TrxDate  <= T2.OpnameDate 
GROUP BY T2.IDOutlet,T2.IDProduct,T2.OpnameDate

I'm assuming the dates are stored in an appropriate date datatype, and that you want to include OpnameDate.

Upvotes: 2

Related Questions