November 10, 2003 at 9:27 am
I need your expertise help!!!
I have a result table that stores the data for my report and a calendar table that stores weekend and holiday.
the result table has the following attributes:
account_id
account_status
status_creation_datetime
what I need to do is select all attributes from the result table. However, I have to perform a calculation of the status_creation_datetime against the system date excluding weekend and holiday from the calendar table to figure out the number of business days that an account is in a particular status.
for example:
SELECT
account_id,
account_status,
datediff(status_creation_datetime,getdate()) - (count(*) of weekend and holiday from calendar where nonworkdays is between status_creation_datetime and getdate())
i know this is a psudocode but i'm not sure how to accomplish this for all records in my result table. How do I loop through each record in the result table perform the calculation until all records have been calculated? Any help/suggestion you can provide is greatly appreciated.
Thanks
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 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply