SQL Date range SUM

Question

Post reply

SQL Date range SUM

A Little Help Please

SSCarpal Tunnel

Points: 4764
More actions
September 18, 2008 at 3:12 am

#212715

I have the following SQL:
SELECTA1.ACCOUNTID,
A1.ACCOUNT,
A4.ENGAGEMENT_DATE A4_ENGAGEMENT_DATE,
A2.SECTOR A2_SECTOR,
A2.ANNUALAMOUNT A2_ANNUALAMOUNT
SUM(d2.commission) AS Commission
FROMACCOUNT A1
LEFT OUTER JOIN QG_ACCMARKETCAP A3 ON (A1.ACCOUNTID=A3.ACCOUNTID)
LEFT OUTER JOIN ECS_KEY_DATES A4 ON (A1.ACCOUNTID=A4.ACCOUNTID)
LEFT OUTER JOIN QG_ACCEXT A2 ON (A1.ACCOUNTID=A2.ACCOUNTID)
LEFT OUTER JOIN New_Aug_Client_TBL A5 ON (a1.accountid = a5.accountid)
LEFT OUTER JOIN accountbook.NewbookdbV2.dbo.tblstocks D1 ON (d1.isin COLLATE SQL_Latin1_General_CP1_CI_AS = a2.isin COLLATE SQL_Latin1_General_CP1_CI_AS )
LEFT OUTER JOIN accountbook.NewbookdbV2.dbo.tbldeals d2 ON d1.sedol = d2.sedol
LEFT OUTER JOIN intopic.inv_rms.dbo.value_add_book_tbl zqs1 ON zqs1.vab_ric = a5.ric
WHERE(A1.STATUS='Client')
AND(A2.ISPUBLICISEDCLIENT='T')
GROUP BYA1.ACCOUNTID,
A1.ACCOUNT,
A4.ENGAGEMENT_DATE,
A2.SECTOR A2_SECTOR,
A2.ANNUALAMOUNT
ORDER BYA1.ACCOUNT
QUESTION:
How whould I get the TOTAL SUM of the d2.commission for a date range? The date range would be on a column d2.trade_date...
Somthing like
d2.trade_date> getdate() -730

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

I think something using BETWEEN would be my suggestion:

AND d2.trade_date BETWEEN getdate() -730 AND getdate()

or

AND d2.trade_date BETWEEN '01/01/2007' and '01/01/2008'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

A Little Help Please SSCarpal Tunnel Points: 4764 More actions · Answer 2

Thanks

AND d2.trade_date BETWEEN getdate() -730 AND getdate()

worked fine