Home MS SQL How to Query Amount Based on Oldest Date
Reply: 0

MS SQL How to Query Amount Based on Oldest Date

user5128
1#
user5128 Published in September 19, 2018, 11:09 am

I'm joining two tables, tblAccount and tblInvoice. TblAccount has multiple invoices related to each account. I'm trying to query the TotalSavings amount based on the oldest InvoiceDate and if there are two amount values on the same date then I need to sum of those two amounts. I thought I was doing this correct but the below code doesn't give me the desired output.

SELECT TA.AccountID,
MIN(TI.InvoiceDate) AS InvoiceDate,
TI.TotalSavings
FROM tblAccount AS TA
LEFT JOIN tblInvoice AS TI
ON TA.AccountID=TI.AccountID
WHERE TI.TotalSavings>0
AND
TI.InvoiceDate IS NOT NULL
GROUP BY TA.AccountID, TI.TotalSavings
ORDER BY TA.AccountID

When i join two tables, i have the below results.

AccountID   Invoice Date    TotalSavings
ABC 6/30/2012   10
ABC 12/31/2013  20
ABC 6/1/2014    30
BCA 9/30/2011   40
BCA 1/31/2012   50
BCA 11/30/2011  60
CBA 3/1/2015    70
CBA 3/1/2015    80

I'm looking for an output like this

AccountID   Invoice Date    TotalSavings
ABC 6/30/2012   10
BCA 9/30/2011   40
CBA 3/1/2015    150

I would appreciate any help that can be offered on this forum.

share|improve this question

4 Answers 4

active oldest votes
up vote 2 down vote accepted
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.320234 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO