Date Calculation

[CrystalVis]

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. Any help/suggestion you can provide is greatly appreciated.
Thanks

 

[Lekar]

I think your idea works:
[tt]
Select Account_ID, Account_Status, DateDiff(d, Status_Creation_DateTime, GetDate()) - (Select Count(*)
From Calendar Where NonWorkDays Between Status_Creation_DateTime And GetDate())
[/tt]

Lenin

 

[SQLSister]

Take your count stuff and put it in a function with a beigdate and end date as the aprameters. I feel sure this is something that will be needed more than once, so a Function is the way to go.

The function would return the count of holiday and weekend days in the time period.

Sicneyou have both inyour table it shouldn't be too hard to comeup with the calculation for the function.

something like
select @NonWorkingDays = Count (dateField) from Holidays where datefield between @StartDate and @endDate

then returnthe variable @nonworkingdays as part of your function.

SeeBOL for the syntax to create a function

 

[Lekar]

I missed the From clause:

[tt]
Select Account_ID, Account_Status, DateDiff(d, Status_Creation_DateTime, GetDate()) - (Select Count(*) From Calendar Where NonWorkDays Between Status_Creation_DateTime And GetDate())
From Result
[/tt]

 

[CrystalVis]

Thank you all for your respond. I'm populating the result table using a DTS package with various data pumps and SQL tasks. I'm not sure how to use a function in the SQL statement and pass the status_creation_datetime as a @startdate and getdate() as an @enddate. Additionally, do i have to use cursor to loop through the result table to process all the record. Thanks for your help.