Doing date math where the user defines which days to count and which to skip

  • I have a query I'm trying to write where we have a scheduled event that occurs x number of days. We store the last done date, and the interval between events. This would be a very simple statement with dateadd("d", interval, lastDoneDate). If Only. 🙂

    Users of the application have the ability to determine which days to include in the calendar, and which to skip. So if they don't operate on the weekends, or if they have holidays and training days, they can make those days unavailable, and we don't include those in our calculation.

    Here's a screen shot of what it would look like to an end user. Green is good, white is skipped. Feel free to let loose on the coloring and pretty bmp at the top 🙂

    Just so you get how the math works, if the last done date is March 5th, and the interval is 14 days, you count 14 green days, so the next due date would be March 29th.

    So far so good. Here's the fun part. This program is written in COBOL (bet you didn't think COBOL did GUI like that?) and the data is not native to SQL. It's actually normally stored in indexed binary files. We have a piece that converts that data to SQL, and converts our DML from COBOL statements into SQL queries. This allows us to write one piece of code essentially, and we can port it to our flat file system, MS SQL or Oracle without any real modification. It's a magical headache really.

    Why this is important is that the binary file actually stores this skip date table as a 12 x 31 two dimensional array instead of a row for each date. Each array record is unique based on the location (the facility number at the top of the screen), and the year. In the program you essentially can just start the file using the year of the last done date and the current location, then loop through the array one day at a time, counting up the number of green days using the interval from the schedule noted earlier.

    When this table is converted to SQL, it flattens the array, so that instead of getting a record per day in a table like Location, Date, Enabled etc, the record looks more like this:

    Location, Year, skip_flag_1_1, skip_flag_1_2, ..., skip_flag_2_1, skip_flag_2_2, ... , skip_flag_12_30, skip_flag_12_31.

    Every "month" has 31 days in this array (don't get me started).

    What I have so far:

    Using Pivot, I can pivot this table into a more normalized structure, and substring/replace the column names and turn them into a bonafide date. Then I just do something like:

    Select top 1 dueDate

    from (select top (interval) dueDate -- only get the number of days specified by the interval

    from pivot_table

    where dueDate > last_done_date --set the start point to the last done date

    and dueDateSkipped = 'N' -- only get the days that are green

    order dueDate asc)

    order dueDate desc -- sort high to low so that the top statement gets the last record from the inner select statement.

    To throw another wrench in this, the pivot only does half the work, because if someone from that location hasn't set up their schedule, there's no data in the table for that location or year. If we don't find the record for the next year, we switch back to regular date math to go the rest of the way (ie if the interval was 45 days, and we got to 30 before we hit the end of the records, we just do 15 more days after 12/31 of whatever year we are in).

    The only way I could think to do this in SQL was to create a table of dates that I could outer join to the pivot table, then edit the where constraint to say where isnull(dueDateSkipped, 'N') = 'N'. I'd be gauranteed never to run out of dates, but as soon as I ran out of skip records, it would just fill in with 'N' and count them up like normal.

    The problem is that this process is extremely slow. It takes a full minute to go through 4k records, where as I can read all 400k records in the table in under a second without this crazy math.

    So my final question:

    Is there a better way to do this?

    I can't change the structure of the skip table (yet). If I could, I think it would speed this process up incredibly, but I still don't know if doing the select top 1 desc from top 45 asc is the best way to do it even if I could get it changed.

    I'll post the actual code I'm using in the next comment.

  • A calendar table will solve this kind of thing neatly.

    Define a base calendar, of all days for the next X years (and some prior years as well), then add a UserDays join table between your Users table (or wherever you store that kind of thing) and the Calendar.

    Bare-bones version:

    create table dbo.Calendar (

    [Date] date primary key);

    GO

    insert into dbo.Calendar ([Date])

    select dateadd(day, Number-1000, '20120101')

    from dbo.Numbers -- table of numbers from 0 to 10k on my machine

    GO

    create table dbo.Calendar_Users (

    [Date] date not null,

    UserID int not null,

    constraint PK_CalendarUsers primary key ([Date], UserID));

    Get data into the Calendar_Users table, indicating which days they want to except. Then it's just:

    select count(*)

    from dbo.Calendar

    where [Date] not in (select [Date] from dbo.Calendar_Users where UserID = @CurrentUser)

    and [Date] between @StartDate and @EndDate;

    Or, if you need to figure out the EndDate value:

    select top (1) [Date]

    from (select top (@Days) [Date]

    from dbo.Calendar

    where [Date] not in (select [Date] from dbo.Calendar_Users where UserID = @CurrentUser)

    and [Date] >= @StartDate

    order by [Date])

    order by [Date] desc;

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • The problem is I don't have permission to change the structure of this table.

  • OK here is the code.

    And to GSquared, I actually do have a Calendar table, but it doesn't have the enabled/disabled switch in it. See below:

    Create the date table

    SET ANSI_NULLS ON;

    GO

    SET QUOTED_IDENTIFIER ON;

    GO

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_Date]') AND type in (N'U'))

    DROP TABLE [dbo].[tbl_Date]

    GO

    CREATE TABLE [dbo].[tbl_Date](

    [Identity] [int] IDENTITY(1,1) NOT NULL,

    [Date] [datetime] NOT NULL,

    [intDate] [int] NOT NULL,

    CONSTRAINT [PK_tbl_Date] PRIMARY KEY CLUSTERED

    (

    [intDate] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY];

    GO

    CREATE UNIQUE NONCLUSTERED INDEX [IX_tbl_Date] ON [dbo].[tbl_Date]

    (

    [Date] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    GO

    CREATE UNIQUE NONCLUSTERED INDEX [IX_tbl_Date_1] ON [dbo].[tbl_Date]

    (

    [Identity] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    GO

    GO

    with mycte as

    (

    select cast('1900-01-01' as datetime) DateValue

    union all

    select DateValue + 1

    from mycte

    where DateValue + 1 < '2150-12-31'

    )

    INSERT INTO [dbo].[tbl_Date]

    ([Date]

    ,[intDate])

    select DateValue, convert(int,CONVERT(varchar(8), DateValue, 112)) as NumericDate

    from mycte

    OPTION (MAXRECURSION 0)

    Here is the skip table, a small 7 row event table, and the udf's I'm using to get the next due date and to convert the int date into a date time

    /****** Object: View [dbo].[vw_SmallEventDue] Script Date: 10/04/2012 12:44:38 ******/

    DROP VIEW [dbo].[vw_SmallEventDue]

    GO

    /****** Object: UserDefinedFunction [dbo].[udf_GetDueDate] Script Date: 10/04/2012 12:44:38 ******/

    DROP FUNCTION [dbo].[udf_GetDueDate]

    GO

    /****** Object: Table [dbo].[tbl_DateSkip] Script Date: 10/04/2012 12:44:37 ******/

    DROP TABLE [dbo].[tbl_DateSkip]

    GO

    /****** Object: Table [dbo].[tbl_Event] Script Date: 10/04/2012 12:44:37 ******/

    DROP TABLE [dbo].[tbl_Event]

    GO

    /****** Object: UserDefinedFunction [dbo].[udf_ConvertIntDate] Script Date: 10/04/2012 12:44:38 ******/

    DROP FUNCTION [dbo].[udf_ConvertIntDate]

    GO

    /****** Object: UserDefinedFunction [dbo].[udf_ConvertIntDate] Script Date: 10/04/2012 12:44:38 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    Create FUNCTION [dbo].[udf_ConvertIntDate] (@date_in INT)

    RETURNS datetime

    AS

    BEGIN

    DECLARE @date_out datetime

    set @date_in =case @date_in

    when 0 then 19000101

    when 20202020 then 19000101

    else @date_in

    end

    SET @date_out = CONVERT(datetime, CAST(@date_in AS CHAR(8)), 101)

    RETURN @date_out

    END

    GO

    /****** Object: Table [dbo].[tbl_Event] Script Date: 10/04/2012 12:44:37 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[tbl_Event](

    [event_id] [int] IDENTITY(1,1) NOT NULL,

    [division_id] [smallint] NOT NULL,

    [event_last_done_date] [int] NOT NULL,

    [event_day_interval] [int] NOT NULL

    ) ON [PRIMARY]

    GO

    SET IDENTITY_INSERT [dbo].[tbl_Event] ON

    INSERT [dbo].[tbl_Event] ([event_id], [division_id], [event_last_done_date], [event_day_interval]) VALUES (1, 1, 20111215, 180)

    INSERT [dbo].[tbl_Event] ([event_id], [division_id], [event_last_done_date], [event_day_interval]) VALUES (2, 1, 20120529, 30)

    INSERT [dbo].[tbl_Event] ([event_id], [division_id], [event_last_done_date], [event_day_interval]) VALUES (3, 1, 20120305, 14)

    INSERT [dbo].[tbl_Event] ([event_id], [division_id], [event_last_done_date], [event_day_interval]) VALUES (4, 1, 20120606, 30)

    INSERT [dbo].[tbl_Event] ([event_id], [division_id], [event_last_done_date], [event_day_interval]) VALUES (5, 1, 20121021, 90)

    INSERT [dbo].[tbl_Event] ([event_id], [division_id], [event_last_done_date], [event_day_interval]) VALUES (6, 1, 20120101, 30)

    INSERT [dbo].[tbl_Event] ([event_id], [division_id], [event_last_done_date], [event_day_interval]) VALUES (7, 1, 20120901, 5)

    SET IDENTITY_INSERT [dbo].[tbl_Event] OFF

    /****** Object: Table [dbo].[tbl_DateSkip] Script Date: 10/04/2012 12:44:37 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[tbl_DateSkip](

    [date_skip_div] [smallint] NOT NULL,

    [date_skip_year] [smallint] NOT NULL,

    [date_skip_flg_1_1] [char](1) NULL,

    [date_skip_flg_1_2] [char](1) NULL,

    [date_skip_flg_1_3] [char](1) NULL,

    [date_skip_flg_1_4] [char](1) NULL,

    [date_skip_flg_1_5] [char](1) NULL,

    [date_skip_flg_1_6] [char](1) NULL,

    [date_skip_flg_1_7] [char](1) NULL,

    [date_skip_flg_1_8] [char](1) NULL,

    [date_skip_flg_1_9] [char](1) NULL,

    [date_skip_flg_1_10] [char](1) NULL,

    [date_skip_flg_1_11] [char](1) NULL,

    [date_skip_flg_1_12] [char](1) NULL,

    [date_skip_flg_1_13] [char](1) NULL,

    [date_skip_flg_1_14] [char](1) NULL,

    [date_skip_flg_1_15] [char](1) NULL,

    [date_skip_flg_1_16] [char](1) NULL,

    [date_skip_flg_1_17] [char](1) NULL,

    [date_skip_flg_1_18] [char](1) NULL,

    [date_skip_flg_1_19] [char](1) NULL,

    [date_skip_flg_1_20] [char](1) NULL,

    [date_skip_flg_1_21] [char](1) NULL,

    [date_skip_flg_1_22] [char](1) NULL,

    [date_skip_flg_1_23] [char](1) NULL,

    [date_skip_flg_1_24] [char](1) NULL,

    [date_skip_flg_1_25] [char](1) NULL,

    [date_skip_flg_1_26] [char](1) NULL,

    [date_skip_flg_1_27] [char](1) NULL,

    [date_skip_flg_1_28] [char](1) NULL,

    [date_skip_flg_1_29] [char](1) NULL,

    [date_skip_flg_1_30] [char](1) NULL,

    [date_skip_flg_1_31] [char](1) NULL,

    [date_skip_flg_2_1] [char](1) NULL,

    [date_skip_flg_2_2] [char](1) NULL,

    [date_skip_flg_2_3] [char](1) NULL,

    [date_skip_flg_2_4] [char](1) NULL,

    [date_skip_flg_2_5] [char](1) NULL,

    [date_skip_flg_2_6] [char](1) NULL,

    [date_skip_flg_2_7] [char](1) NULL,

    [date_skip_flg_2_8] [char](1) NULL,

    [date_skip_flg_2_9] [char](1) NULL,

    [date_skip_flg_2_10] [char](1) NULL,

    [date_skip_flg_2_11] [char](1) NULL,

    [date_skip_flg_2_12] [char](1) NULL,

    [date_skip_flg_2_13] [char](1) NULL,

    [date_skip_flg_2_14] [char](1) NULL,

    [date_skip_flg_2_15] [char](1) NULL,

    [date_skip_flg_2_16] [char](1) NULL,

    [date_skip_flg_2_17] [char](1) NULL,

    [date_skip_flg_2_18] [char](1) NULL,

    [date_skip_flg_2_19] [char](1) NULL,

    [date_skip_flg_2_20] [char](1) NULL,

    [date_skip_flg_2_21] [char](1) NULL,

    [date_skip_flg_2_22] [char](1) NULL,

    [date_skip_flg_2_23] [char](1) NULL,

    [date_skip_flg_2_24] [char](1) NULL,

    [date_skip_flg_2_25] [char](1) NULL,

    [date_skip_flg_2_26] [char](1) NULL,

    [date_skip_flg_2_27] [char](1) NULL,

    [date_skip_flg_2_28] [char](1) NULL,

    [date_skip_flg_2_29] [char](1) NULL,

    [date_skip_flg_2_30] [char](1) NULL,

    [date_skip_flg_2_31] [char](1) NULL,

    [date_skip_flg_3_1] [char](1) NULL,

    [date_skip_flg_3_2] [char](1) NULL,

    [date_skip_flg_3_3] [char](1) NULL,

    [date_skip_flg_3_4] [char](1) NULL,

    [date_skip_flg_3_5] [char](1) NULL,

    [date_skip_flg_3_6] [char](1) NULL,

    [date_skip_flg_3_7] [char](1) NULL,

    [date_skip_flg_3_8] [char](1) NULL,

    [date_skip_flg_3_9] [char](1) NULL,

    [date_skip_flg_3_10] [char](1) NULL,

    [date_skip_flg_3_11] [char](1) NULL,

    [date_skip_flg_3_12] [char](1) NULL,

    [date_skip_flg_3_13] [char](1) NULL,

    [date_skip_flg_3_14] [char](1) NULL,

    [date_skip_flg_3_15] [char](1) NULL,

    [date_skip_flg_3_16] [char](1) NULL,

    [date_skip_flg_3_17] [char](1) NULL,

    [date_skip_flg_3_18] [char](1) NULL,

    [date_skip_flg_3_19] [char](1) NULL,

    [date_skip_flg_3_20] [char](1) NULL,

    [date_skip_flg_3_21] [char](1) NULL,

    [date_skip_flg_3_22] [char](1) NULL,

    [date_skip_flg_3_23] [char](1) NULL,

    [date_skip_flg_3_24] [char](1) NULL,

    [date_skip_flg_3_25] [char](1) NULL,

    [date_skip_flg_3_26] [char](1) NULL,

    [date_skip_flg_3_27] [char](1) NULL,

    [date_skip_flg_3_28] [char](1) NULL,

    [date_skip_flg_3_29] [char](1) NULL,

    [date_skip_flg_3_30] [char](1) NULL,

    [date_skip_flg_3_31] [char](1) NULL,

    [date_skip_flg_4_1] [char](1) NULL,

    [date_skip_flg_4_2] [char](1) NULL,

    [date_skip_flg_4_3] [char](1) NULL,

    [date_skip_flg_4_4] [char](1) NULL,

    [date_skip_flg_4_5] [char](1) NULL,

    [date_skip_flg_4_6] [char](1) NULL,

    [date_skip_flg_4_7] [char](1) NULL,

    [date_skip_flg_4_8] [char](1) NULL,

    [date_skip_flg_4_9] [char](1) NULL,

    [date_skip_flg_4_10] [char](1) NULL,

    [date_skip_flg_4_11] [char](1) NULL,

    [date_skip_flg_4_12] [char](1) NULL,

    [date_skip_flg_4_13] [char](1) NULL,

    [date_skip_flg_4_14] [char](1) NULL,

    [date_skip_flg_4_15] [char](1) NULL,

    [date_skip_flg_4_16] [char](1) NULL,

    [date_skip_flg_4_17] [char](1) NULL,

    [date_skip_flg_4_18] [char](1) NULL,

    [date_skip_flg_4_19] [char](1) NULL,

    [date_skip_flg_4_20] [char](1) NULL,

    [date_skip_flg_4_21] [char](1) NULL,

    [date_skip_flg_4_22] [char](1) NULL,

    [date_skip_flg_4_23] [char](1) NULL,

    [date_skip_flg_4_24] [char](1) NULL,

    [date_skip_flg_4_25] [char](1) NULL,

    [date_skip_flg_4_26] [char](1) NULL,

    [date_skip_flg_4_27] [char](1) NULL,

    [date_skip_flg_4_28] [char](1) NULL,

    [date_skip_flg_4_29] [char](1) NULL,

    [date_skip_flg_4_30] [char](1) NULL,

    [date_skip_flg_4_31] [char](1) NULL,

    [date_skip_flg_5_1] [char](1) NULL,

    [date_skip_flg_5_2] [char](1) NULL,

    [date_skip_flg_5_3] [char](1) NULL,

    [date_skip_flg_5_4] [char](1) NULL,

    [date_skip_flg_5_5] [char](1) NULL,

    [date_skip_flg_5_6] [char](1) NULL,

    [date_skip_flg_5_7] [char](1) NULL,

    [date_skip_flg_5_8] [char](1) NULL,

    [date_skip_flg_5_9] [char](1) NULL,

    [date_skip_flg_5_10] [char](1) NULL,

    [date_skip_flg_5_11] [char](1) NULL,

    [date_skip_flg_5_12] [char](1) NULL,

    [date_skip_flg_5_13] [char](1) NULL,

    [date_skip_flg_5_14] [char](1) NULL,

    [date_skip_flg_5_15] [char](1) NULL,

    [date_skip_flg_5_16] [char](1) NULL,

    [date_skip_flg_5_17] [char](1) NULL,

    [date_skip_flg_5_18] [char](1) NULL,

    [date_skip_flg_5_19] [char](1) NULL,

    [date_skip_flg_5_20] [char](1) NULL,

    [date_skip_flg_5_21] [char](1) NULL,

    [date_skip_flg_5_22] [char](1) NULL,

    [date_skip_flg_5_23] [char](1) NULL,

    [date_skip_flg_5_24] [char](1) NULL,

    [date_skip_flg_5_25] [char](1) NULL,

    [date_skip_flg_5_26] [char](1) NULL,

    [date_skip_flg_5_27] [char](1) NULL,

    [date_skip_flg_5_28] [char](1) NULL,

    [date_skip_flg_5_29] [char](1) NULL,

    [date_skip_flg_5_30] [char](1) NULL,

    [date_skip_flg_5_31] [char](1) NULL,

    [date_skip_flg_6_1] [char](1) NULL,

    [date_skip_flg_6_2] [char](1) NULL,

    [date_skip_flg_6_3] [char](1) NULL,

    [date_skip_flg_6_4] [char](1) NULL,

    [date_skip_flg_6_5] [char](1) NULL,

    [date_skip_flg_6_6] [char](1) NULL,

    [date_skip_flg_6_7] [char](1) NULL,

    [date_skip_flg_6_8] [char](1) NULL,

    [date_skip_flg_6_9] [char](1) NULL,

    [date_skip_flg_6_10] [char](1) NULL,

    [date_skip_flg_6_11] [char](1) NULL,

    [date_skip_flg_6_12] [char](1) NULL,

    [date_skip_flg_6_13] [char](1) NULL,

    [date_skip_flg_6_14] [char](1) NULL,

    [date_skip_flg_6_15] [char](1) NULL,

    [date_skip_flg_6_16] [char](1) NULL,

    [date_skip_flg_6_17] [char](1) NULL,

    [date_skip_flg_6_18] [char](1) NULL,

    [date_skip_flg_6_19] [char](1) NULL,

    [date_skip_flg_6_20] [char](1) NULL,

    [date_skip_flg_6_21] [char](1) NULL,

    [date_skip_flg_6_22] [char](1) NULL,

    [date_skip_flg_6_23] [char](1) NULL,

    [date_skip_flg_6_24] [char](1) NULL,

    [date_skip_flg_6_25] [char](1) NULL,

    [date_skip_flg_6_26] [char](1) NULL,

    [date_skip_flg_6_27] [char](1) NULL,

    [date_skip_flg_6_28] [char](1) NULL,

    [date_skip_flg_6_29] [char](1) NULL,

    [date_skip_flg_6_30] [char](1) NULL,

    [date_skip_flg_6_31] [char](1) NULL,

    [date_skip_flg_7_1] [char](1) NULL,

    [date_skip_flg_7_2] [char](1) NULL,

    [date_skip_flg_7_3] [char](1) NULL,

    [date_skip_flg_7_4] [char](1) NULL,

    [date_skip_flg_7_5] [char](1) NULL,

    [date_skip_flg_7_6] [char](1) NULL,

    [date_skip_flg_7_7] [char](1) NULL,

    [date_skip_flg_7_8] [char](1) NULL,

    [date_skip_flg_7_9] [char](1) NULL,

    [date_skip_flg_7_10] [char](1) NULL,

    [date_skip_flg_7_11] [char](1) NULL,

    [date_skip_flg_7_12] [char](1) NULL,

    [date_skip_flg_7_13] [char](1) NULL,

    [date_skip_flg_7_14] [char](1) NULL,

    [date_skip_flg_7_15] [char](1) NULL,

    [date_skip_flg_7_16] [char](1) NULL,

    [date_skip_flg_7_17] [char](1) NULL,

    [date_skip_flg_7_18] [char](1) NULL,

    [date_skip_flg_7_19] [char](1) NULL,

    [date_skip_flg_7_20] [char](1) NULL,

    [date_skip_flg_7_21] [char](1) NULL,

    [date_skip_flg_7_22] [char](1) NULL,

    [date_skip_flg_7_23] [char](1) NULL,

    [date_skip_flg_7_24] [char](1) NULL,

    [date_skip_flg_7_25] [char](1) NULL,

    [date_skip_flg_7_26] [char](1) NULL,

    [date_skip_flg_7_27] [char](1) NULL,

    [date_skip_flg_7_28] [char](1) NULL,

    [date_skip_flg_7_29] [char](1) NULL,

    [date_skip_flg_7_30] [char](1) NULL,

    [date_skip_flg_7_31] [char](1) NULL,

    [date_skip_flg_8_1] [char](1) NULL,

    [date_skip_flg_8_2] [char](1) NULL,

    [date_skip_flg_8_3] [char](1) NULL,

    [date_skip_flg_8_4] [char](1) NULL,

    [date_skip_flg_8_5] [char](1) NULL,

    [date_skip_flg_8_6] [char](1) NULL,

    [date_skip_flg_8_7] [char](1) NULL,

    [date_skip_flg_8_8] [char](1) NULL,

    [date_skip_flg_8_9] [char](1) NULL,

    [date_skip_flg_8_10] [char](1) NULL,

    [date_skip_flg_8_11] [char](1) NULL,

    [date_skip_flg_8_12] [char](1) NULL,

    [date_skip_flg_8_13] [char](1) NULL,

    [date_skip_flg_8_14] [char](1) NULL,

    [date_skip_flg_8_15] [char](1) NULL,

    [date_skip_flg_8_16] [char](1) NULL,

    [date_skip_flg_8_17] [char](1) NULL,

    [date_skip_flg_8_18] [char](1) NULL,

    [date_skip_flg_8_19] [char](1) NULL,

    [date_skip_flg_8_20] [char](1) NULL,

    [date_skip_flg_8_21] [char](1) NULL,

    [date_skip_flg_8_22] [char](1) NULL,

    [date_skip_flg_8_23] [char](1) NULL,

    [date_skip_flg_8_24] [char](1) NULL,

    [date_skip_flg_8_25] [char](1) NULL,

    [date_skip_flg_8_26] [char](1) NULL,

    [date_skip_flg_8_27] [char](1) NULL,

    [date_skip_flg_8_28] [char](1) NULL,

    [date_skip_flg_8_29] [char](1) NULL,

    [date_skip_flg_8_30] [char](1) NULL,

    [date_skip_flg_8_31] [char](1) NULL,

    [date_skip_flg_9_1] [char](1) NULL,

    [date_skip_flg_9_2] [char](1) NULL,

    [date_skip_flg_9_3] [char](1) NULL,

    [date_skip_flg_9_4] [char](1) NULL,

    [date_skip_flg_9_5] [char](1) NULL,

    [date_skip_flg_9_6] [char](1) NULL,

    [date_skip_flg_9_7] [char](1) NULL,

    [date_skip_flg_9_8] [char](1) NULL,

    [date_skip_flg_9_9] [char](1) NULL,

    [date_skip_flg_9_10] [char](1) NULL,

    [date_skip_flg_9_11] [char](1) NULL,

    [date_skip_flg_9_12] [char](1) NULL,

    [date_skip_flg_9_13] [char](1) NULL,

    [date_skip_flg_9_14] [char](1) NULL,

    [date_skip_flg_9_15] [char](1) NULL,

    [date_skip_flg_9_16] [char](1) NULL,

    [date_skip_flg_9_17] [char](1) NULL,

    [date_skip_flg_9_18] [char](1) NULL,

    [date_skip_flg_9_19] [char](1) NULL,

    [date_skip_flg_9_20] [char](1) NULL,

    [date_skip_flg_9_21] [char](1) NULL,

    [date_skip_flg_9_22] [char](1) NULL,

    [date_skip_flg_9_23] [char](1) NULL,

    [date_skip_flg_9_24] [char](1) NULL,

    [date_skip_flg_9_25] [char](1) NULL,

    [date_skip_flg_9_26] [char](1) NULL,

    [date_skip_flg_9_27] [char](1) NULL,

    [date_skip_flg_9_28] [char](1) NULL,

    [date_skip_flg_9_29] [char](1) NULL,

    [date_skip_flg_9_30] [char](1) NULL,

    [date_skip_flg_9_31] [char](1) NULL,

    [date_skip_flg_10_1] [char](1) NULL,

    [date_skip_flg_10_2] [char](1) NULL,

    [date_skip_flg_10_3] [char](1) NULL,

    [date_skip_flg_10_4] [char](1) NULL,

    [date_skip_flg_10_5] [char](1) NULL,

    [date_skip_flg_10_6] [char](1) NULL,

    [date_skip_flg_10_7] [char](1) NULL,

    [date_skip_flg_10_8] [char](1) NULL,

    [date_skip_flg_10_9] [char](1) NULL,

    [date_skip_flg_10_10] [char](1) NULL,

    [date_skip_flg_10_11] [char](1) NULL,

    [date_skip_flg_10_12] [char](1) NULL,

    [date_skip_flg_10_13] [char](1) NULL,

    [date_skip_flg_10_14] [char](1) NULL,

    [date_skip_flg_10_15] [char](1) NULL,

    [date_skip_flg_10_16] [char](1) NULL,

    [date_skip_flg_10_17] [char](1) NULL,

    [date_skip_flg_10_18] [char](1) NULL,

    [date_skip_flg_10_19] [char](1) NULL,

    [date_skip_flg_10_20] [char](1) NULL,

    [date_skip_flg_10_21] [char](1) NULL,

    [date_skip_flg_10_22] [char](1) NULL,

    [date_skip_flg_10_23] [char](1) NULL,

    [date_skip_flg_10_24] [char](1) NULL,

    [date_skip_flg_10_25] [char](1) NULL,

    [date_skip_flg_10_26] [char](1) NULL,

    [date_skip_flg_10_27] [char](1) NULL,

    [date_skip_flg_10_28] [char](1) NULL,

    [date_skip_flg_10_29] [char](1) NULL,

    [date_skip_flg_10_30] [char](1) NULL,

    [date_skip_flg_10_31] [char](1) NULL,

    [date_skip_flg_11_1] [char](1) NULL,

    [date_skip_flg_11_2] [char](1) NULL,

    [date_skip_flg_11_3] [char](1) NULL,

    [date_skip_flg_11_4] [char](1) NULL,

    [date_skip_flg_11_5] [char](1) NULL,

    [date_skip_flg_11_6] [char](1) NULL,

    [date_skip_flg_11_7] [char](1) NULL,

    [date_skip_flg_11_8] [char](1) NULL,

    [date_skip_flg_11_9] [char](1) NULL,

    [date_skip_flg_11_10] [char](1) NULL,

    [date_skip_flg_11_11] [char](1) NULL,

    [date_skip_flg_11_12] [char](1) NULL,

    [date_skip_flg_11_13] [char](1) NULL,

    [date_skip_flg_11_14] [char](1) NULL,

    [date_skip_flg_11_15] [char](1) NULL,

    [date_skip_flg_11_16] [char](1) NULL,

    [date_skip_flg_11_17] [char](1) NULL,

    [date_skip_flg_11_18] [char](1) NULL,

    [date_skip_flg_11_19] [char](1) NULL,

    [date_skip_flg_11_20] [char](1) NULL,

    [date_skip_flg_11_21] [char](1) NULL,

    [date_skip_flg_11_22] [char](1) NULL,

    [date_skip_flg_11_23] [char](1) NULL,

    [date_skip_flg_11_24] [char](1) NULL,

    [date_skip_flg_11_25] [char](1) NULL,

    [date_skip_flg_11_26] [char](1) NULL,

    [date_skip_flg_11_27] [char](1) NULL,

    [date_skip_flg_11_28] [char](1) NULL,

    [date_skip_flg_11_29] [char](1) NULL,

    [date_skip_flg_11_30] [char](1) NULL,

    [date_skip_flg_11_31] [char](1) NULL,

    [date_skip_flg_12_1] [char](1) NULL,

    [date_skip_flg_12_2] [char](1) NULL,

    [date_skip_flg_12_3] [char](1) NULL,

    [date_skip_flg_12_4] [char](1) NULL,

    [date_skip_flg_12_5] [char](1) NULL,

    [date_skip_flg_12_6] [char](1) NULL,

    [date_skip_flg_12_7] [char](1) NULL,

    [date_skip_flg_12_8] [char](1) NULL,

    [date_skip_flg_12_9] [char](1) NULL,

    [date_skip_flg_12_10] [char](1) NULL,

    [date_skip_flg_12_11] [char](1) NULL,

    [date_skip_flg_12_12] [char](1) NULL,

    [date_skip_flg_12_13] [char](1) NULL,

    [date_skip_flg_12_14] [char](1) NULL,

    [date_skip_flg_12_15] [char](1) NULL,

    [date_skip_flg_12_16] [char](1) NULL,

    [date_skip_flg_12_17] [char](1) NULL,

    [date_skip_flg_12_18] [char](1) NULL,

    [date_skip_flg_12_19] [char](1) NULL,

    [date_skip_flg_12_20] [char](1) NULL,

    [date_skip_flg_12_21] [char](1) NULL,

    [date_skip_flg_12_22] [char](1) NULL,

    [date_skip_flg_12_23] [char](1) NULL,

    [date_skip_flg_12_24] [char](1) NULL,

    [date_skip_flg_12_25] [char](1) NULL,

    [date_skip_flg_12_26] [char](1) NULL,

    [date_skip_flg_12_27] [char](1) NULL,

    [date_skip_flg_12_28] [char](1) NULL,

    [date_skip_flg_12_29] [char](1) NULL,

    [date_skip_flg_12_30] [char](1) NULL,

    [date_skip_flg_12_31] [char](1) NULL

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    CREATE UNIQUE CLUSTERED INDEX [idx_DateSkip0] ON [dbo].[tbl_DateSkip]

    (

    [date_skip_div] ASC,

    [date_skip_year] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    GO

    INSERT [dbo].[tbl_DateSkip] ([date_skip_div], [date_skip_year], [date_skip_flg_1_1], [date_skip_flg_1_2], [date_skip_flg_1_3], [date_skip_flg_1_4], [date_skip_flg_1_5], [date_skip_flg_1_6], [date_skip_flg_1_7], [date_skip_flg_1_8], [date_skip_flg_1_9], [date_skip_flg_1_10], [date_skip_flg_1_11], [date_skip_flg_1_12], [date_skip_flg_1_13], [date_skip_flg_1_14], [date_skip_flg_1_15], [date_skip_flg_1_16], [date_skip_flg_1_17], [date_skip_flg_1_18], [date_skip_flg_1_19], [date_skip_flg_1_20], [date_skip_flg_1_21], [date_skip_flg_1_22], [date_skip_flg_1_23], [date_skip_flg_1_24], [date_skip_flg_1_25], [date_skip_flg_1_26], [date_skip_flg_1_27], [date_skip_flg_1_28], [date_skip_flg_1_29], [date_skip_flg_1_30], [date_skip_flg_1_31], [date_skip_flg_2_1], [date_skip_flg_2_2], [date_skip_flg_2_3], [date_skip_flg_2_4], [date_skip_flg_2_5], [date_skip_flg_2_6], [date_skip_flg_2_7], [date_skip_flg_2_8], [date_skip_flg_2_9], [date_skip_flg_2_10], [date_skip_flg_2_11], [date_skip_flg_2_12], [date_skip_flg_2_13], [date_skip_flg_2_14], [date_skip_flg_2_15], [date_skip_flg_2_16], [date_skip_flg_2_17], [date_skip_flg_2_18], [date_skip_flg_2_19], [date_skip_flg_2_20], [date_skip_flg_2_21], [date_skip_flg_2_22], [date_skip_flg_2_23], [date_skip_flg_2_24], [date_skip_flg_2_25], [date_skip_flg_2_26], [date_skip_flg_2_27], [date_skip_flg_2_28], [date_skip_flg_2_29], [date_skip_flg_2_30], [date_skip_flg_2_31], [date_skip_flg_3_1], [date_skip_flg_3_2], [date_skip_flg_3_3], [date_skip_flg_3_4], [date_skip_flg_3_5], [date_skip_flg_3_6], [date_skip_flg_3_7], [date_skip_flg_3_8], [date_skip_flg_3_9], [date_skip_flg_3_10], [date_skip_flg_3_11], [date_skip_flg_3_12], [date_skip_flg_3_13], [date_skip_flg_3_14], [date_skip_flg_3_15], [date_skip_flg_3_16], [date_skip_flg_3_17], [date_skip_flg_3_18], [date_skip_flg_3_19], [date_skip_flg_3_20], [date_skip_flg_3_21], [date_skip_flg_3_22], [date_skip_flg_3_23], [date_skip_flg_3_24], [date_skip_flg_3_25], [date_skip_flg_3_26], [date_skip_flg_3_27], [date_skip_flg_3_28], [date_skip_flg_3_29], [date_skip_flg_3_30], [date_skip_flg_3_31], [date_skip_flg_4_1], [date_skip_flg_4_2], [date_skip_flg_4_3], [date_skip_flg_4_4], [date_skip_flg_4_5], [date_skip_flg_4_6], [date_skip_flg_4_7], [date_skip_flg_4_8], [date_skip_flg_4_9], [date_skip_flg_4_10], [date_skip_flg_4_11], [date_skip_flg_4_12], [date_skip_flg_4_13], [date_skip_flg_4_14], [date_skip_flg_4_15], [date_skip_flg_4_16], [date_skip_flg_4_17], [date_skip_flg_4_18], [date_skip_flg_4_19], [date_skip_flg_4_20], [date_skip_flg_4_21], [date_skip_flg_4_22], [date_skip_flg_4_23], [date_skip_flg_4_24], [date_skip_flg_4_25], [date_skip_flg_4_26], [date_skip_flg_4_27], [date_skip_flg_4_28], [date_skip_flg_4_29], [date_skip_flg_4_30], [date_skip_flg_4_31], [date_skip_flg_5_1], [date_skip_flg_5_2], [date_skip_flg_5_3], [date_skip_flg_5_4], [date_skip_flg_5_5], [date_skip_flg_5_6], [date_skip_flg_5_7], [date_skip_flg_5_8], [date_skip_flg_5_9], [date_skip_flg_5_10], [date_skip_flg_5_11], [date_skip_flg_5_12], [date_skip_flg_5_13], [date_skip_flg_5_14], [date_skip_flg_5_15], [date_skip_flg_5_16], [date_skip_flg_5_17], [date_skip_flg_5_18], [date_skip_flg_5_19], [date_skip_flg_5_20], [date_skip_flg_5_21], [date_skip_flg_5_22], [date_skip_flg_5_23], [date_skip_flg_5_24], [date_skip_flg_5_25], [date_skip_flg_5_26], [date_skip_flg_5_27], [date_skip_flg_5_28], [date_skip_flg_5_29], [date_skip_flg_5_30], [date_skip_flg_5_31], [date_skip_flg_6_1], [date_skip_flg_6_2], [date_skip_flg_6_3], [date_skip_flg_6_4], [date_skip_flg_6_5], [date_skip_flg_6_6], [date_skip_flg_6_7], [date_skip_flg_6_8], [date_skip_flg_6_9], [date_skip_flg_6_10], [date_skip_flg_6_11], [date_skip_flg_6_12], [date_skip_flg_6_13], [date_skip_flg_6_14], [date_skip_flg_6_15], [date_skip_flg_6_16], [date_skip_flg_6_17], [date_skip_flg_6_18], [date_skip_flg_6_19], [date_skip_flg_6_20], [date_skip_flg_6_21], [date_skip_flg_6_22], [date_skip_flg_6_23], [date_skip_flg_6_24], [date_skip_flg_6_25], [date_skip_flg_6_26], [date_skip_flg_6_27], [date_skip_flg_6_28], [date_skip_flg_6_29], [date_skip_flg_6_30], [date_skip_flg_6_31], [date_skip_flg_7_1], [date_skip_flg_7_2], [date_skip_flg_7_3], [date_skip_flg_7_4], [date_skip_flg_7_5], [date_skip_flg_7_6], [date_skip_flg_7_7], [date_skip_flg_7_8], [date_skip_flg_7_9], [date_skip_flg_7_10], [date_skip_flg_7_11], [date_skip_flg_7_12], [date_skip_flg_7_13], [date_skip_flg_7_14], [date_skip_flg_7_15], [date_skip_flg_7_16], [date_skip_flg_7_17], [date_skip_flg_7_18], [date_skip_flg_7_19], [date_skip_flg_7_20], [date_skip_flg_7_21], [date_skip_flg_7_22], [date_skip_flg_7_23], [date_skip_flg_7_24], [date_skip_flg_7_25], [date_skip_flg_7_26], [date_skip_flg_7_27], [date_skip_flg_7_28], [date_skip_flg_7_29], [date_skip_flg_7_30], [date_skip_flg_7_31], [date_skip_flg_8_1], [date_skip_flg_8_2], [date_skip_flg_8_3], [date_skip_flg_8_4], [date_skip_flg_8_5], [date_skip_flg_8_6], [date_skip_flg_8_7], [date_skip_flg_8_8], [date_skip_flg_8_9], [date_skip_flg_8_10], [date_skip_flg_8_11], [date_skip_flg_8_12], [date_skip_flg_8_13], [date_skip_flg_8_14], [date_skip_flg_8_15], [date_skip_flg_8_16], [date_skip_flg_8_17], [date_skip_flg_8_18], [date_skip_flg_8_19], [date_skip_flg_8_20], [date_skip_flg_8_21], [date_skip_flg_8_22], [date_skip_flg_8_23], [date_skip_flg_8_24], [date_skip_flg_8_25], [date_skip_flg_8_26], [date_skip_flg_8_27], [date_skip_flg_8_28], [date_skip_flg_8_29], [date_skip_flg_8_30], [date_skip_flg_8_31], [date_skip_flg_9_1], [date_skip_flg_9_2], [date_skip_flg_9_3], [date_skip_flg_9_4], [date_skip_flg_9_5], [date_skip_flg_9_6], [date_skip_flg_9_7], [date_skip_flg_9_8], [date_skip_flg_9_9], [date_skip_flg_9_10], [date_skip_flg_9_11], [date_skip_flg_9_12], [date_skip_flg_9_13], [date_skip_flg_9_14], [date_skip_flg_9_15], [date_skip_flg_9_16], [date_skip_flg_9_17], [date_skip_flg_9_18], [date_skip_flg_9_19], [date_skip_flg_9_20], [date_skip_flg_9_21], [date_skip_flg_9_22], [date_skip_flg_9_23], [date_skip_flg_9_24], [date_skip_flg_9_25], [date_skip_flg_9_26], [date_skip_flg_9_27], [date_skip_flg_9_28], [date_skip_flg_9_29], [date_skip_flg_9_30], [date_skip_flg_9_31], [date_skip_flg_10_1], [date_skip_flg_10_2], [date_skip_flg_10_3], [date_skip_flg_10_4], [date_skip_flg_10_5], [date_skip_flg_10_6], [date_skip_flg_10_7], [date_skip_flg_10_8], [date_skip_flg_10_9], [date_skip_flg_10_10], [date_skip_flg_10_11], [date_skip_flg_10_12], [date_skip_flg_10_13], [date_skip_flg_10_14], [date_skip_flg_10_15], [date_skip_flg_10_16], [date_skip_flg_10_17], [date_skip_flg_10_18], [date_skip_flg_10_19], [date_skip_flg_10_20], [date_skip_flg_10_21], [date_skip_flg_10_22], [date_skip_flg_10_23], [date_skip_flg_10_24], [date_skip_flg_10_25], [date_skip_flg_10_26], [date_skip_flg_10_27], [date_skip_flg_10_28], [date_skip_flg_10_29], [date_skip_flg_10_30], [date_skip_flg_10_31], [date_skip_flg_11_1], [date_skip_flg_11_2], [date_skip_flg_11_3], [date_skip_flg_11_4], [date_skip_flg_11_5], [date_skip_flg_11_6], [date_skip_flg_11_7], [date_skip_flg_11_8], [date_skip_flg_11_9], [date_skip_flg_11_10], [date_skip_flg_11_11], [date_skip_flg_11_12], [date_skip_flg_11_13], [date_skip_flg_11_14], [date_skip_flg_11_15], [date_skip_flg_11_16], [date_skip_flg_11_17], [date_skip_flg_11_18], [date_skip_flg_11_19], [date_skip_flg_11_20], [date_skip_flg_11_21], [date_skip_flg_11_22], [date_skip_flg_11_23], [date_skip_flg_11_24], [date_skip_flg_11_25], [date_skip_flg_11_26], [date_skip_flg_11_27], [date_skip_flg_11_28], [date_skip_flg_11_29], [date_skip_flg_11_30], [date_skip_flg_11_31], [date_skip_flg_12_1], [date_skip_flg_12_2], [date_skip_flg_12_3], [date_skip_flg_12_4], [date_skip_flg_12_5], [date_skip_flg_12_6], [date_skip_flg_12_7], [date_skip_flg_12_8], [date_skip_flg_12_9], [date_skip_flg_12_10], [date_skip_flg_12_11], [date_skip_flg_12_12], [date_skip_flg_12_13], [date_skip_flg_12_14], [date_skip_flg_12_15], [date_skip_flg_12_16], [date_skip_flg_12_17], [date_skip_flg_12_18], [date_skip_flg_12_19], [date_skip_flg_12_20], [date_skip_flg_12_21], [date_skip_flg_12_22], [date_skip_flg_12_23], [date_skip_flg_12_24], [date_skip_flg_12_25], [date_skip_flg_12_26], [date_skip_flg_12_27], [date_skip_flg_12_28], [date_skip_flg_12_29], [date_skip_flg_12_30], [date_skip_flg_12_31]) VALUES (1, 2012, N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'N', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'Y', N'N', N'N', N'N', N'N', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'N', N'N', N'N', N'Y', N'Y', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'N', N'N', N'N', N'N', N'N', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y', N'Y')

    /****** Object: UserDefinedFunction [dbo].[udf_GetDueDate] Script Date: 10/04/2012 12:44:38 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE function [dbo].[udf_GetDueDate](

    @lastDoneInt int,

    @div smallint,

    @interval integer) returns int

    as

    begin

    declare @intDateOut int; --return variable

    declare @lastDoneDate datetime = dbo.udf_ConvertIntDate(@lastDoneInt); --variable to store yyyymmdd int as datetime

    declare @startYear int = datepart(yyyy, @lastDoneDate);

    set @intDateOut = (

    select top 1 intDate from

    (select top (@interval) Calendar.intDate from

    (selectdate_skip_div,

    cast(cast(date_skip_year as varchar(4)) --year

    + right('0' + replace(left(replace(SkipDate.MonthDay, 'date_skip_flg_', ''),2), '_', ''),2) --month

    + right('0' + replace(right(replace(SkipDate.MonthDay, 'date_skip_flg_', ''),2), '_', ''),2) --day

    as varchar(8)

    ) as date_skip_date,

    SkipDate.isSkipped

    from tbl_DateSkip

    unpivot (isSkipped for MonthDay in ([date_skip_flg_1_1]

    ,[date_skip_flg_1_2]

    ,[date_skip_flg_1_3]

    ,[date_skip_flg_1_4]

    ,[date_skip_flg_1_5]

    ,[date_skip_flg_1_6]

    ,[date_skip_flg_1_7]

    ,[date_skip_flg_1_8]

    ,[date_skip_flg_1_9]

    ,[date_skip_flg_1_10]

    ,[date_skip_flg_1_11]

    ,[date_skip_flg_1_12]

    ,[date_skip_flg_1_13]

    ,[date_skip_flg_1_14]

    ,[date_skip_flg_1_15]

    ,[date_skip_flg_1_16]

    ,[date_skip_flg_1_17]

    ,[date_skip_flg_1_18]

    ,[date_skip_flg_1_19]

    ,[date_skip_flg_1_20]

    ,[date_skip_flg_1_21]

    ,[date_skip_flg_1_22]

    ,[date_skip_flg_1_23]

    ,[date_skip_flg_1_24]

    ,[date_skip_flg_1_25]

    ,[date_skip_flg_1_26]

    ,[date_skip_flg_1_27]

    ,[date_skip_flg_1_28]

    ,[date_skip_flg_1_29]

    ,[date_skip_flg_1_30]

    ,[date_skip_flg_1_31]

    ,[date_skip_flg_2_1]

    ,[date_skip_flg_2_2]

    ,[date_skip_flg_2_3]

    ,[date_skip_flg_2_4]

    ,[date_skip_flg_2_5]

    ,[date_skip_flg_2_6]

    ,[date_skip_flg_2_7]

    ,[date_skip_flg_2_8]

    ,[date_skip_flg_2_9]

    ,[date_skip_flg_2_10]

    ,[date_skip_flg_2_11]

    ,[date_skip_flg_2_12]

    ,[date_skip_flg_2_13]

    ,[date_skip_flg_2_14]

    ,[date_skip_flg_2_15]

    ,[date_skip_flg_2_16]

    ,[date_skip_flg_2_17]

    ,[date_skip_flg_2_18]

    ,[date_skip_flg_2_19]

    ,[date_skip_flg_2_20]

    ,[date_skip_flg_2_21]

    ,[date_skip_flg_2_22]

    ,[date_skip_flg_2_23]

    ,[date_skip_flg_2_24]

    ,[date_skip_flg_2_25]

    ,[date_skip_flg_2_26]

    ,[date_skip_flg_2_27]

    ,[date_skip_flg_2_28]

    ,[date_skip_flg_2_29]

    ,[date_skip_flg_3_1]

    ,[date_skip_flg_3_2]

    ,[date_skip_flg_3_3]

    ,[date_skip_flg_3_4]

    ,[date_skip_flg_3_5]

    ,[date_skip_flg_3_6]

    ,[date_skip_flg_3_7]

    ,[date_skip_flg_3_8]

    ,[date_skip_flg_3_9]

    ,[date_skip_flg_3_10]

    ,[date_skip_flg_3_11]

    ,[date_skip_flg_3_12]

    ,[date_skip_flg_3_13]

    ,[date_skip_flg_3_14]

    ,[date_skip_flg_3_15]

    ,[date_skip_flg_3_16]

    ,[date_skip_flg_3_17]

    ,[date_skip_flg_3_18]

    ,[date_skip_flg_3_19]

    ,[date_skip_flg_3_20]

    ,[date_skip_flg_3_21]

    ,[date_skip_flg_3_22]

    ,[date_skip_flg_3_23]

    ,[date_skip_flg_3_24]

    ,[date_skip_flg_3_25]

    ,[date_skip_flg_3_26]

    ,[date_skip_flg_3_27]

    ,[date_skip_flg_3_28]

    ,[date_skip_flg_3_29]

    ,[date_skip_flg_3_30]

    ,[date_skip_flg_3_31]

    ,[date_skip_flg_4_1]

    ,[date_skip_flg_4_2]

    ,[date_skip_flg_4_3]

    ,[date_skip_flg_4_4]

    ,[date_skip_flg_4_5]

    ,[date_skip_flg_4_6]

    ,[date_skip_flg_4_7]

    ,[date_skip_flg_4_8]

    ,[date_skip_flg_4_9]

    ,[date_skip_flg_4_10]

    ,[date_skip_flg_4_11]

    ,[date_skip_flg_4_12]

    ,[date_skip_flg_4_13]

    ,[date_skip_flg_4_14]

    ,[date_skip_flg_4_15]

    ,[date_skip_flg_4_16]

    ,[date_skip_flg_4_17]

    ,[date_skip_flg_4_18]

    ,[date_skip_flg_4_19]

    ,[date_skip_flg_4_20]

    ,[date_skip_flg_4_21]

    ,[date_skip_flg_4_22]

    ,[date_skip_flg_4_23]

    ,[date_skip_flg_4_24]

    ,[date_skip_flg_4_25]

    ,[date_skip_flg_4_26]

    ,[date_skip_flg_4_27]

    ,[date_skip_flg_4_28]

    ,[date_skip_flg_4_29]

    ,[date_skip_flg_4_30]

    ,[date_skip_flg_5_1]

    ,[date_skip_flg_5_2]

    ,[date_skip_flg_5_3]

    ,[date_skip_flg_5_4]

    ,[date_skip_flg_5_5]

    ,[date_skip_flg_5_6]

    ,[date_skip_flg_5_7]

    ,[date_skip_flg_5_8]

    ,[date_skip_flg_5_9]

    ,[date_skip_flg_5_10]

    ,[date_skip_flg_5_11]

    ,[date_skip_flg_5_12]

    ,[date_skip_flg_5_13]

    ,[date_skip_flg_5_14]

    ,[date_skip_flg_5_15]

    ,[date_skip_flg_5_16]

    ,[date_skip_flg_5_17]

    ,[date_skip_flg_5_18]

    ,[date_skip_flg_5_19]

    ,[date_skip_flg_5_20]

    ,[date_skip_flg_5_21]

    ,[date_skip_flg_5_22]

    ,[date_skip_flg_5_23]

    ,[date_skip_flg_5_24]

    ,[date_skip_flg_5_25]

    ,[date_skip_flg_5_26]

    ,[date_skip_flg_5_27]

    ,[date_skip_flg_5_28]

    ,[date_skip_flg_5_29]

    ,[date_skip_flg_5_30]

    ,[date_skip_flg_5_31]

    ,[date_skip_flg_6_1]

    ,[date_skip_flg_6_2]

    ,[date_skip_flg_6_3]

    ,[date_skip_flg_6_4]

    ,[date_skip_flg_6_5]

    ,[date_skip_flg_6_6]

    ,[date_skip_flg_6_7]

    ,[date_skip_flg_6_8]

    ,[date_skip_flg_6_9]

    ,[date_skip_flg_6_10]

    ,[date_skip_flg_6_11]

    ,[date_skip_flg_6_12]

    ,[date_skip_flg_6_13]

    ,[date_skip_flg_6_14]

    ,[date_skip_flg_6_15]

    ,[date_skip_flg_6_16]

    ,[date_skip_flg_6_17]

    ,[date_skip_flg_6_18]

    ,[date_skip_flg_6_19]

    ,[date_skip_flg_6_20]

    ,[date_skip_flg_6_21]

    ,[date_skip_flg_6_22]

    ,[date_skip_flg_6_23]

    ,[date_skip_flg_6_24]

    ,[date_skip_flg_6_25]

    ,[date_skip_flg_6_26]

    ,[date_skip_flg_6_27]

    ,[date_skip_flg_6_28]

    ,[date_skip_flg_6_29]

    ,[date_skip_flg_6_30]

    ,[date_skip_flg_7_1]

    ,[date_skip_flg_7_2]

    ,[date_skip_flg_7_3]

    ,[date_skip_flg_7_4]

    ,[date_skip_flg_7_5]

    ,[date_skip_flg_7_6]

    ,[date_skip_flg_7_7]

    ,[date_skip_flg_7_8]

    ,[date_skip_flg_7_9]

    ,[date_skip_flg_7_10]

    ,[date_skip_flg_7_11]

    ,[date_skip_flg_7_12]

    ,[date_skip_flg_7_13]

    ,[date_skip_flg_7_14]

    ,[date_skip_flg_7_15]

    ,[date_skip_flg_7_16]

    ,[date_skip_flg_7_17]

    ,[date_skip_flg_7_18]

    ,[date_skip_flg_7_19]

    ,[date_skip_flg_7_20]

    ,[date_skip_flg_7_21]

    ,[date_skip_flg_7_22]

    ,[date_skip_flg_7_23]

    ,[date_skip_flg_7_24]

    ,[date_skip_flg_7_25]

    ,[date_skip_flg_7_26]

    ,[date_skip_flg_7_27]

    ,[date_skip_flg_7_28]

    ,[date_skip_flg_7_29]

    ,[date_skip_flg_7_30]

    ,[date_skip_flg_7_31]

    ,[date_skip_flg_8_1]

    ,[date_skip_flg_8_2]

    ,[date_skip_flg_8_3]

    ,[date_skip_flg_8_4]

    ,[date_skip_flg_8_5]

    ,[date_skip_flg_8_6]

    ,[date_skip_flg_8_7]

    ,[date_skip_flg_8_8]

    ,[date_skip_flg_8_9]

    ,[date_skip_flg_8_10]

    ,[date_skip_flg_8_11]

    ,[date_skip_flg_8_12]

    ,[date_skip_flg_8_13]

    ,[date_skip_flg_8_14]

    ,[date_skip_flg_8_15]

    ,[date_skip_flg_8_16]

    ,[date_skip_flg_8_17]

    ,[date_skip_flg_8_18]

    ,[date_skip_flg_8_19]

    ,[date_skip_flg_8_20]

    ,[date_skip_flg_8_21]

    ,[date_skip_flg_8_22]

    ,[date_skip_flg_8_23]

    ,[date_skip_flg_8_24]

    ,[date_skip_flg_8_25]

    ,[date_skip_flg_8_26]

    ,[date_skip_flg_8_27]

    ,[date_skip_flg_8_28]

    ,[date_skip_flg_8_29]

    ,[date_skip_flg_8_30]

    ,[date_skip_flg_8_31]

    ,[date_skip_flg_9_1]

    ,[date_skip_flg_9_2]

    ,[date_skip_flg_9_3]

    ,[date_skip_flg_9_4]

    ,[date_skip_flg_9_5]

    ,[date_skip_flg_9_6]

    ,[date_skip_flg_9_7]

    ,[date_skip_flg_9_8]

    ,[date_skip_flg_9_9]

    ,[date_skip_flg_9_10]

    ,[date_skip_flg_9_11]

    ,[date_skip_flg_9_12]

    ,[date_skip_flg_9_13]

    ,[date_skip_flg_9_14]

    ,[date_skip_flg_9_15]

    ,[date_skip_flg_9_16]

    ,[date_skip_flg_9_17]

    ,[date_skip_flg_9_18]

    ,[date_skip_flg_9_19]

    ,[date_skip_flg_9_20]

    ,[date_skip_flg_9_21]

    ,[date_skip_flg_9_22]

    ,[date_skip_flg_9_23]

    ,[date_skip_flg_9_24]

    ,[date_skip_flg_9_25]

    ,[date_skip_flg_9_26]

    ,[date_skip_flg_9_27]

    ,[date_skip_flg_9_28]

    ,[date_skip_flg_9_29]

    ,[date_skip_flg_9_30]

    ,[date_skip_flg_10_1]

    ,[date_skip_flg_10_2]

    ,[date_skip_flg_10_3]

    ,[date_skip_flg_10_4]

    ,[date_skip_flg_10_5]

    ,[date_skip_flg_10_6]

    ,[date_skip_flg_10_7]

    ,[date_skip_flg_10_8]

    ,[date_skip_flg_10_9]

    ,[date_skip_flg_10_10]

    ,[date_skip_flg_10_11]

    ,[date_skip_flg_10_12]

    ,[date_skip_flg_10_13]

    ,[date_skip_flg_10_14]

    ,[date_skip_flg_10_15]

    ,[date_skip_flg_10_16]

    ,[date_skip_flg_10_17]

    ,[date_skip_flg_10_18]

    ,[date_skip_flg_10_19]

    ,[date_skip_flg_10_20]

    ,[date_skip_flg_10_21]

    ,[date_skip_flg_10_22]

    ,[date_skip_flg_10_23]

    ,[date_skip_flg_10_24]

    ,[date_skip_flg_10_25]

    ,[date_skip_flg_10_26]

    ,[date_skip_flg_10_27]

    ,[date_skip_flg_10_28]

    ,[date_skip_flg_10_29]

    ,[date_skip_flg_10_30]

    ,[date_skip_flg_10_31]

    ,[date_skip_flg_11_1]

    ,[date_skip_flg_11_2]

    ,[date_skip_flg_11_3]

    ,[date_skip_flg_11_4]

    ,[date_skip_flg_11_5]

    ,[date_skip_flg_11_6]

    ,[date_skip_flg_11_7]

    ,[date_skip_flg_11_8]

    ,[date_skip_flg_11_9]

    ,[date_skip_flg_11_10]

    ,[date_skip_flg_11_11]

    ,[date_skip_flg_11_12]

    ,[date_skip_flg_11_13]

    ,[date_skip_flg_11_14]

    ,[date_skip_flg_11_15]

    ,[date_skip_flg_11_16]

    ,[date_skip_flg_11_17]

    ,[date_skip_flg_11_18]

    ,[date_skip_flg_11_19]

    ,[date_skip_flg_11_20]

    ,[date_skip_flg_11_21]

    ,[date_skip_flg_11_22]

    ,[date_skip_flg_11_23]

    ,[date_skip_flg_11_24]

    ,[date_skip_flg_11_25]

    ,[date_skip_flg_11_26]

    ,[date_skip_flg_11_27]

    ,[date_skip_flg_11_28]

    ,[date_skip_flg_11_29]

    ,[date_skip_flg_11_30]

    ,[date_skip_flg_12_1]

    ,[date_skip_flg_12_2]

    ,[date_skip_flg_12_3]

    ,[date_skip_flg_12_4]

    ,[date_skip_flg_12_5]

    ,[date_skip_flg_12_6]

    ,[date_skip_flg_12_7]

    ,[date_skip_flg_12_8]

    ,[date_skip_flg_12_9]

    ,[date_skip_flg_12_10]

    ,[date_skip_flg_12_11]

    ,[date_skip_flg_12_12]

    ,[date_skip_flg_12_13]

    ,[date_skip_flg_12_14]

    ,[date_skip_flg_12_15]

    ,[date_skip_flg_12_16]

    ,[date_skip_flg_12_17]

    ,[date_skip_flg_12_18]

    ,[date_skip_flg_12_19]

    ,[date_skip_flg_12_20]

    ,[date_skip_flg_12_21]

    ,[date_skip_flg_12_22]

    ,[date_skip_flg_12_23]

    ,[date_skip_flg_12_24]

    ,[date_skip_flg_12_25]

    ,[date_skip_flg_12_26]

    ,[date_skip_flg_12_27]

    ,[date_skip_flg_12_28]

    ,[date_skip_flg_12_29]

    ,[date_skip_flg_12_30]

    ,[date_skip_flg_12_31])

    ) as SkipDate --end unpivot

    where date_skip_year >= @startYear and date_skip_div = @div

    ) as date_skip --end select statment

    right outer join (select top (@interval * 2) tbl_Date.intDate from tbl_Date

    where [intDATE] >= @lastDoneInt

    order by [intDate]

    ) Calendar

    on Calendar.[intDate] = date_skip.date_skip_date

    where isnull(date_skip.isSkipped, 'N') = 'N'

    order by intDate asc) as SkipTable

    order by intDate desc);

    return @intDateOut;

    end

    GO

    /****** Object: View [dbo].[vw_SmallEventDue] Script Date: 10/04/2012 12:44:38 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE VIEW [dbo].[vw_SmallEventDue]

    AS

    SELECT event_id, division_id, event_last_done_date, event_day_interval, dbo.udf_GetDueDate(event_last_done_date, division_id, event_day_interval) AS 'Event Due'

    FROM dbo.tbl_Event

    GO

    If you want more sample data, I have a bigger table with 30k rows I can generate insert statements for. It's really big though, so I don't know if I should clog up the post with it just yet.

    FYI, these scripts were generated through SSMS for 2008 R2. If there's a problem, let me know I'll see what I can get you guys.

  • OK here is the big table

  • You don't need to change the structure, so far as I can tell. Just modify my queries to use your tables. Instead of Calendar_Users, it's your DateSkip table, and so on.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • I think that's my problem. Instead of calendar_users, I have this nasty unpivot table.

    I'll try manipulating your last query to go off the unpivot.

  • OK, I think I just had a thought. Right now I'm using this as a function, which means for every row it's going through the unpivot process for that year and beyond.

    I'm going to try moving this further up in the procedure so that it creates a temporary table (like your calendar users table) once per batch instead of once per row. I'll post my results once I get the kinks worked out.

  • Sounds like a good plan. How'd it go?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Very promising! I was able to get the query down from 1:51 for 4100 rows every time to under 15 seconds for the same data on first run (much faster for subsequent runs now as well, under 2 seconds usually).

    I took the unpivot function and turned it into a stored procedure instead, returning the results into a temporary table that I later access with a subquery in the main select statement during the procedure. Your last query got me thinking about how I was counting the days. Much more elegant than joining the tables together. My initial unpivot was selecting the days that were green, but if I flipped it to get me the white (skip) days, I was able to use the not in (list) with my date table, and it was much more effective.

    The real fun will be re-writing this with PL/SQL within the next year. At least I'll have a framework to go off of.

    GSquared, thanks for your assistance. Often times it just takes another perspective to crack the lock on the solution safe!

  • You're welcome. Glad I could help.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply