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.
| Author |
Topic |
|
cathsith9
Starting Member
6 Posts |
 Posted - 2012-06-27 : 21:55:16
|
hi all..i have 2 tables below :table headergrvnoa | date |1 | 6 march 2012 |2 | 20 march 2012 |3 | 1 June 2012 |4 | 3 june 2012 |5 | 25 june 2012 |table detailsgrvnoa | ProdID | QTY |1 | 002 | 60 |1 | 003 | 90 |2 | 004 | 70 |3 | 001 | 10 | 3 | 002 | 10 | 3 | 003 | 20 |4 | 004 | 10 |5 | 003 | 80 |I want create a report to get the total QTY of each item for the last month I received only, like this :ProdID | QTY | Date | 001 | 10 | June 2012 | 002 | 10 | March 2012 |003 | 100 | June 2012 |004 | 10 | June 2012 |But I cannot get the result that I want, this is the last query I try:SELECT PRODID ,sum(details.Qty) ,max(header.date)from detailsinner join headeron detail.GRVNOA=header.GRVNOAgroup by details.PRODIDThe current result set is :ProdID | QTY | Date | 001 | 10 | June 2012 | 002 | 70 | March 2012 |003 | 190 | June 2012 |004 | 80 | June 2012 |Do you guys could help me with this join table problem?thank you..  |
|
|
flamblaster
Constraint Violating Yak Guru
384 Posts |
Posted - 2012-06-27 : 23:06:37
|
| Why exactly would prodid 003 have a total QTY of 100 for March 2012? It seems to me that the QTY for March 2012 on ProdId 003 would be 90 because grvnoa 1 is the only one that has a record for March out of grvnoa 1,3 and 5. |
 |
|
|
cathsith9
Starting Member
6 Posts |
Posted - 2012-06-27 : 23:22:32
|
| oops, my mistake.. sorry. thanks for the correction. i already fixed it.it should be qty on June because it was the last month of the receiving (003), i only need the last month qty of the last received. |
|
|
|
flamblaster
Constraint Violating Yak Guru
384 Posts |
Posted - 2012-06-27 : 23:24:41
|
| Cool...out of curiosity, what did it come out to be? |
|
|
|
|
|
|
Need Help To Get Total Result From 2 tables