November 10, 2003 at 1:13 pm
How about:
SELECT rt.account_id, rt.account_status, DATEDIFF(DAY,rt.status_creation_datetime,GETDATE())-COUNT(*)
FROM Result_Table rt, Calendar_Table ct
WHERE ct.DateField BETWEEN rt.status_creation_datetime AND GETDATE()
GROUP BY rt.account_id, rt.account_status, rt.status_creation_datetime
Brian
November 10, 2003 at 1:16 pm
What you can maybe do is before you do your select statement is get a count of your results table put it into a variable @var1.
Have a second variable that keeps an increment @var2.
Once you captured the count you may use the WHILE @var1 <> @var2
BEGIN
SELECT...
@var2 = @var2 + 1
...
END
November 11, 2003 at 6:08 am
quote:
SELECT rt.account_id, rt.account_status, DATEDIFF(DAY,rt.status_creation_datetime,GETDATE())-COUNT(*)FROM Result_Table rt, Calendar_Table ct
WHERE ct.DateField BETWEEN rt.status_creation_datetime AND GETDATE()
GROUP BY rt.account_id, rt.account_status, rt.status_creation_datetime
Brian,
your solution will not return data for results that do not have any calendar values between status_creation_datetime and getdate()
Try this
SELECT rt.account_id, rt.account_status,
DATEDIFF(DAY,rt.status_creation_datetime,GETDATE()) -
SUM(CASE WHEN ct.DateField IS NULL THEN 0 ELSE 1 END)
FROM Result_Table rt
LEFT OUTER JOIN Calendar_Table ct
ON ct.DateField BETWEEN rt.status_creation_datetime AND GETDATE()
GROUP BY rt.account_id, rt.account_status, rt.status_creation_datetime
Edited by - davidburrows on 11/11/2003 06:08:57 AM
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply