July 3, 2003 at 6:21 pm
Can someone please tell me how I can accomplish the following.
I have a table with the following format:
PK_ID, CONTACT_ID, TRX_YEAR, AMOUNT
1, abc, 2003, 6000.00
2, abc, 2002, 4000.00
I want to retrieve transactions for the current & prior year grouped by CONTACT_ID as follows:
CONTACT_ID, YTD AMOUNT, PRIOR YEAR AMOUNT
abc, 6000.00, 4000.00
Any help would be GREATLY appreciated!
Regards,
-Joe
July 3, 2003 at 10:41 pm
Joe, Try:
SELECT CONTACT_ID ,
[YTD AMOUNT] = AMOUNT,
[PRIOR YEAR AMOUNT] = (SELECT AMOUNT
FROM TRANSTBL t2
WHERE t2.CONTACT_ID = t1.CONTACT_ID
AND t2.TRX_YEAR = t1.TRX_YEAR - 1)
FROM TRANSTBL t1
WHERE TRX_YEAR = DATEPART(year, GETDATE())
Cheers,
- Mark
July 4, 2003 at 6:04 am
Or
SELECT CONTACT_ID,
SUM(CASE WHEN TRX_YEAR = YEAR(GETDATE()) THEN AMOUNT ELSE 0 END) AS 'YTD AMOUNT',
SUM(CASE WHEN TRX_YEAR = YEAR(GETDATE())-1 THEN AMOUNT ELSE 0 END) AS 'PRIOR YEAR AMOUNT'
GROUP BY CONTACT_ID
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply