Work Pattern Calendar Tables

Question

Work Pattern Calendar Tables

itwhiz

Mr or Mrs. 500

Points: 531
More actions
October 5, 2014 at 3:47 pm

#293921

Hi,
I need to prove that there is a speed issue with one of our stored procs/functions but to do this I need to create a large static work pattern calendar table.
Currently I have
1) A table that contains the start date of numerous work pattern.
2) A table that contains the details of the work patterns i.e on days, on night and off days to the work pattern.
Different work patterns are of different lengths, some may be 8 days, some 34 days, 14 days etc. and they repeat over bank holidays and weekends.
What I need to create is a big work pattern calendar table stating at 01/01/1980 to 31/12/2099 that contains a record for each work pattern for each day.
To create the table using TSQL, I'm currently looking at using multiple loops. But can any one suggest any better ways of doing it or even point me in the direction of similar examples of code?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

I don't know what's involved in your detailed work patterns, so I don't know if you can replace it. My guess would be that you could, but I can't say for sure.

To replace the dates, that's pretty simple once you're familiar with a Tally table. Jeff's article at http://www.sqlservercentral.com/articles/T-SQL/62867/ describes them in some detail and they're well worth the time to read it. The article will really change the way you look at loops. Here's an example of returning a table of dates:

SELECT DATEADD(day, t.N, '01/01/1980')

FROM dbo.Tally t

WHERE t.N < DATEDIFF(day, '01/01/1980', '12/31/2099');

From this, you can perform a set-based insert against your table without a loop. From there, if you have a table with a "master set" of work patterns, you may be able to insert into your work patterns table by using a cross apply of it with your dates table.

I know this isn't complete, but without the DDL and details of what you're after, I don't know where to go from here. I hope it helps.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

itwhiz (10/5/2014)
Hi,
I need to prove that there is a speed issue with one of our stored procs/functions but to do this I need to create a large static work pattern calendar table.
Currently I have
1) A table that contains the start date of numerous work pattern.
2) A table that contains the details of the work patterns i.e on days, on night and off days to the work pattern.
Different work patterns are of different lengths, some may be 8 days, some 34 days, 14 days etc. and they repeat over bank holidays and weekends.
What I need to create is a big work pattern calendar table stating at 01/01/1980 to 31/12/2099 that contains a record for each work pattern for each day.
To create the table using TSQL, I'm currently looking at using multiple loops. But can any one suggest any better ways of doing it or even point me in the direction of similar examples of code?
Thanks

As Ed suggested, generating shedloads of data sequential and constrained randomized data is pretty easy. Can you post what you'd like to see for CREATE TABLE statements for the tables and a bit of data so we can se what you mean by "different work patterns of different lengths" so we have a better understanding of the data you want to generate? Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

itwhiz (10/5/2014)
Hi,
I need to prove that there is a speed issue with one of our stored procs/functions ...

Proving that there is a performance issue need not take as much time and effort as you think. Why not post up the actual execution plan so folks have an opportunity to investigate and offer suggestions?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

@itwhiz,

Are you all set or do you still need help?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

itwhiz Mr or Mrs. 500 Points: 531 More actions · Answer 5

Hi everyone,

Thanks for the replies.

I was working on creating the table using a row for each day/work day pattern, but the slight error has been proven by the testers within the system, so it's been handed back to the software house to rectify, plus I've been moved on to something else.

But thanks again for the offer of help it was much appreciated.