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

Question

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

Josh Turley-300197

SSChasing Mays

Points: 660
More actions
October 4, 2012 at 12:39 pm

#262622

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.

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

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

joshturley@rtafleet.com SSC Rookie Points: 35 More actions · Answer 2

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

joshturley@rtafleet.com SSC Rookie Points: 35 More actions · Answer 3

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

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.

joshturley@rtafleet.com SSC Rookie Points: 35 More actions · Answer 4

joshturley@rtafleet.com

SSC Rookie

Points: 35

October 4, 2012 at 2:05 pm

#1545779

OK here is the big table

GSquared SSC Guru Points: 260824 More actions · Answer 5

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

joshturley@rtafleet.com SSC Rookie Points: 35 More actions · Answer 6

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.

Josh Turley-300197 SSChasing Mays Points: 660 More actions · Answer 7

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.

GSquared SSC Guru Points: 260824 More actions · Answer 8

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

Josh Turley-300197 SSChasing Mays Points: 660 More actions · Answer 9

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!

GSquared SSC Guru Points: 260824 More actions · Answer 10

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