June 29, 2010 at 8:05 am
Supposed
transID, clientID, date, note
1, 1, 6/1/2010, note 11
2, 1, 6/30/2010, note 21
3, 2, 1/30/2009, note 32
4, 2, 1/30/2010, note 42
5, 2, 6/30/2010, note 52
The query should return
1, 1, 6/1/2010, note 11
2, 1, 6/30/2010, note 21
4, 2, 1/30/2010, note 42
5, 2, 6/30/2010, note 52
this is what I had try:
SELECT T1.transID, T1.clientID, T1.date, T1.note FROM
trans T1
JOIN trans T2 ON T1.clientID = T2.clientID AND T1.transID <> T2.transID
WHERE
DateAdd(Month, 6, T1.date) > T2.date AND T2.transID IN (SELECT MAX(transID) FROM trans group by clientID)
That gets me all the records occurred within 6 months of the latest one, EXCEPT the latest one (I want it to be included).
Any help would be appreciated.
Thanks
June 29, 2010 at 8:12 am
DDL Script to setup a table and some data would be helpfull.
Please click on the link at the bottom of my signature to find how the question should be presented in polite form
June 29, 2010 at 8:19 am
try:
;WITH selDates
As
(
SELECT clientID, DATEADD(month, -6, MAX([date])) as Date6MAgo
FROM trans group by clientID
)
SELECT T1.transID, T1.clientID, T1.[date], T1.note
FROM trans T1
JOIN selDates sd
ON sd.clientID = T1.clientID
WHERE T1.[Date] >= sd.Date6MAgo
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply