CODEWITHSUNDEEP
sqlms-accessgroup-byaggregate-functions
Avagut
Avagut

Reputation: 994

SQL Group By Having Where Statements

I have a MS Access table tracking quantities of products at end month as below. I need to generate the latest quantity for a specified ProductId at a specified date e.g. The Quantity for ProductId 1 on 15-Feb-12 is 100, The Quantity for ProductId 1 on 15-Mar-12 is 150. 

ProductId | ReportingDate | Quantity|  
1         | 31-Jan-12     | 100     |  
2         | 31-Jan-12     | 200     |  
1         | 28-Feb-12     | 150     |  
2         | 28-Feb-12     | 250     |  
1         | 31-Mar-12     | 180     |  
2         | 31-Mar-12     | 280     |

My SQL statement below bring all previous values instead the latest one only. Could anyone assist me troubleshoot the query.

SELECT Sheet1.ProductId, Max(Sheet1.ReportingDate) AS MaxOfReportingDate, Sheet1.Quantity
FROM Sheet1
GROUP BY Sheet1.ProductId, Sheet1.Quantity, Sheet1.ReportingDate, Sheet1.ProductId
HAVING (((Sheet1.ReportingDate)<#3/15/2012#) AND ((Sheet1.ProductId)=1))

Upvotes: 0

Views: 126

Answers (1)

StuartLC
StuartLC

Reputation: 107317

Here's @naveen's idea:

SELECT TOP 1 Sheet1.ProductId, Sheet1.ReportingDate AS MaxOfReportingDate, Sheet1.Quantity
FROM Sheet1
WHERE (Sheet1.ProductId = 1)
AND (Sheet1.ReportingDate < #2012/03/15#)
ORDER BY Sheet1.ReportingDate DESC

Although note that MsAccess selects top with ties, so this won't work if you have more than one row per ReportingDate, ProductId combo. (But at the same time, this means that the data isn't deterministic anyway)

Edit - I meant that if you have a contradiction in your data like below, you'll get 2 rows back.

ProductId | ReportingDate | Quantity|  
1         | 31-Jan-12     | 100    
1         | 31-Jan-12     | 200

Upvotes: 2

Related Questions