Business Date Calculation

  • 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

  • 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

  • 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

  • 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