Need help with Cursor to determine working and nonworking day 1

[varadha72]

Hi
Can someone help me here. I need to create a cursor, and this I am not sure of. I have never created one. As a BA never created a cursor. Please help.

DB Fields-
Table- Accounts
Acct_ID Acct_Name FiscalEnd_Date
101 US Bonds 10/31/2013
102 Small Cap 6/30/2014
103 Large Cap 3/31/2014

I have created a view that shows holidays for individual accounts globally (account specific)

View- Holidays
Acct_ID Holiday_Dates (including weekends)
1 1/1/2013 (holiday)
1 1/5/2013 (Saturday)
1 1/6/2013 (Sunday)....

Logic Validation-
If current date=FiscalEnd_date and current date= Hoilday
then fetch the next day, check if this day is a holiday, if not holiday then count the number of days between the FiscalEnd_Date and Current Date +1
else step to next date, and repeat until next working date is reached.

 

[gmmastros]

Please understand that using cursors are notoriously slow in SQL Server. If your view contained ALL dates with a bit column to indicate holidays, you would be able to perform this query without requiring a cursor. Alternatively, if you have a numbers table or calendar table, it could still be done without using a cursor.

Do you have any such tables in your database?


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Here's an example that you may find helpful.

Declare @Accounts Table(Acct_ID int, Acct_Name VarChar(100), FiscalEnd_Date DateTime)

Insert Into @Accounts Values(101, 'US Bonds' , '10/31/2013')
Insert Into @Accounts Values(102, 'Small Cap', '6/30/2014')
Insert Into @Accounts Values(103, 'Large Cap', '3/31/2014')

Declare @Holidays Table(Acct_ID Int, Calendar_Dates DateTime, IsHoliday Bit)
Insert Into @Holidays Values(101, '10/31/2013', 1)
Insert Into @Holidays Values(101, '11/1/2013', 1)
Insert Into @Holidays Values(101, '11/2/2013', 0)

Select  *,
        (
           Select Top 1 H.Calendar_dates 
           From   @Holidays H 
           Where  Calendar_Dates >= FiscalEnd_Date 
                  and IsHoliday = 0
        ) As NextWorkDay
From	@Accounts Accounts

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[varadha72]

Thanks I do not have calendar table. There is a function that reterieves that data, which I have used in the View.

 

[varadha72]

Hi Gammastros,

The query below actually checks for holidays only, correct, but if the day is Saturday, then it needs to check for next day, which is Sunday, then the next, if Monday is also holiday, then keep looping until the date is working. Not sure if the below query does that because the View returns only NonWorking dates for an account, how do I validate for working date.

 

[gmmastros]

I encourage you to try this query.

Declare @Accounts Table(Acct_ID int, Acct_Name VarChar(100), FiscalEnd_Date DateTime)

Insert Into @Accounts Values(101, 'US Bonds' , '10/31/2013')
Insert Into @Accounts Values(102, 'Small Cap', '6/30/2014')
Insert Into @Accounts Values(103, 'Large Cap', '3/31/2014')

Declare @Holidays Table(Acct_ID Int, Holiday_Dates DateTime)
Insert Into @Holidays Values(101, '10/31/2013')
Insert Into @Holidays Values(101, '11/1/2013')
Insert Into @Holidays Values(101, '11/2/2013')

; With Holidays As
(
    Select  Acct_ID,
            Holiday_Dates,
            1 As IsHoliday
    From    @Holidays

    Union All

    Select  A.Acct_ID,
            DateAdd(Day, 1, A.Holiday_Dates),
            0 As IsHoliday
    From    @Holidays As A
            Left Join @Holidays As B
              On A.Acct_ID = B.Acct_ID
              And A.Holiday_Dates = DateAdd(Day, -1, B.Holiday_Dates)
    Where   B.Acct_ID Is NULL
)
Select  *,
        (
          Select Top 1 Holidays.Holiday_dates 
          From   Holidays 
          Where  Holiday_Dates >= FiscalEnd_Date 
                 and IsHoliday = 0
        ) As NextWorkDay
From    @Accounts Accounts

You'll notice that this query uses table variables. This is so that I could test the query. If this query works for you, you should then remove the top part where the table variables are created and then change the query to use your real tables instead of the table variables.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

If I understand correctly, your Holiday view contains Saturdays and Sundays. The query will return the next non-holiday (which makes it a work day).

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[varadha72]

can you tell me how I could use field name in Insert statement instead of hardcoding the values.