Complicated Stored Procedure 1

[Halliarse]

I'm elatively new to creating complicated stored procedures and need some advice please, although I'm sure that many of you won't think this complicaqted at all.

I have a Crystal report that displays a rolling 12 months woth of KPI data and I need to claculate the number of available working days per month, from the initial reporting period for 12 months. I will pass the report start date to the stored procedure and want to return 12 output variables with the correct monthly values.

I have to validate against 2 files, one which holds public holidays, the other the company working days.

The stored procedure needs to loop between 2 dates, calculate the 'Day' number for that date and see if that number record exists within the OpeningHours table. It it does, then add 1 to the correct output variable. Similarly, the actual date being looped through needs to be checked from the PublicHoliday table and if that exists. subtract 1 from the correct output variable.

My initial thought is to have the @StartMonth passed to the stored procedure then calculate the 12 periods worth of working days to pass back into the @month1, @Month2....output variables.

Can anyone please suggest where I start with this because I'm lacking a little imagination?

Many thanks

Steve

 

[gmmastros]

Can you show some sample data and expected results?



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

 

[Halliarse]

Ok, the data is attached with an explanation beneath

The initial date passed to the stored procedure will be StartMonth, 01/06/2014
The WorkingDays of Mon, Tue, Thu, Fri, Sat are held on the OpeningHours table where the day number, i.e. 2 for Monday is held in the 'Day' column
Bank Holidays are held on the PublicHolidays table with the actual date being herld in the BankHoliday column.
The column Avail Days in the spreadsheet is the expected return values for each of the 12 returned output values.

The process should take the StartMonth, loop through the days in that month and if the day relating to each date matches any found in the OpeningHours table then add 1 to the AvailDays. The secondary loop should match the actual date to anything in the PublicHolidays and reduce the AvailDays.

I would also like to include functionality for leap years.

Hope that makes sense!

Cheers

Steve

 

[gmmastros]

Can you post a couple rows from OpeningHours and a couple rows from the PublicHoliday table?

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

 

[Halliarse]

Public Holidays

PublicHolidayID BankHoliday
24 2015-04-03 00:00:00.000
25 2015-04-06 00:00:00.000
27 2015-05-25 00:00:00.000
26 2015-05-04 00:00:00.000
28 2015-08-31 00:00:00.000
29 2015-12-25 00:00:00.000
30 2015-12-28 00:00:00.000

OpeningHours

SalonOpeningID SalonID Day StartTime EndTime
1 1 2 08:30:00.0000000 16:30:00.0000000
2 1 3 08:30:00.0000000 16:30:00.0000000
3 1 5 08:30:00.0000000 19:00:00.0000000
4 1 6 08:30:00.0000000 20:00:00.0000000
5 1 7 08:00:00.0000000 13:00:00.0000000

 

[gmmastros]

The PublicHolidays table is easy to understand. I'm still a little confused about the OpeningHours table. How many rows are in OpeningHours? Since there are multiple Mondays in each month, do all the Monday's have a DayId = 2?



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

 

[Halliarse]

The table just lists which days within the week are working days, so yes the number 2 just determines that Monday is an available working day

 

[gmmastros]

Try this:

; With Calendar As
(
Select  DateAdd(Day, Number, '20140601') As WorkDay,
        DatePart(Weekday, DateAdd(Day, Number, '20140601')) As DayId
From    Master..spt_values 
Where   Type = 'P'
        And number < 365
)
Select  Top 12
        Year(Calendar.WorkDay) As WorkYear, 
        Month(Calendar.Workday) As WorkMonth,
        Count(*) As WorkingDays,
        Count(BankHoliday) As BankHolidays,
        Count(*) - Count(BankHoliday) As AvailableDays
From    Calendar
        Inner Join OpeningHours
          On Calendar.DayId = OpeningHours.DayId
        Left Join PublicHolidays
          On Calendar.Workday = PublicHolidays.BankHoliday
Group By Year(Calendar.WorkDay), Month(Calendar.Workday)
Order By WorkYear, WorkMonth

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

 

[Halliarse]

Hi George

I'm probably being a bit slow here and struggling to understand what your code will return!

Please can you explain what the code is doing?

Many thanks

Steve

 

[gmmastros]

I bet you were expecting a couple of loops. Since this can be done in a set based manner, it will perform much better than using loops.

First, let me explain my thought process. To accomplish this query, I realized it would be helpful to have a unique list of dates to run the query on. Then, I could left join to the holidays to determine which day is a holiday. I could also join to the OpeningHours table to only return the weekdays we care about. Once I have the base set of data (list of dates), I could count the rows while grouping by month.

Next, let me explain Master..spt_values. "spt_values" is a table that exists in every Master database of every sql server instance. If you look at the number column while filtering for type = 'P', you will get a sequence of numbers starting at 0 and continuing through a couple thousand.

Try running this query:

Select  DateAdd(Day, Number, '20140601') As WorkDay,
        DatePart(Weekday, DateAdd(Day, Number, '20140601')) As DayId
From    Master..spt_values 
Where   Type = 'P'
        And number < 365

The query above should return 365 rows (1 year's worth of data). This part "[!]DateAdd(Day, Number, '20140601')[/!]" will convert the number to a date. 20140601 represents the first day of the start month. In this case, it's year 2014, month 06, day 01 (June 1, 2014). Also notice that there is a DayId column that represents the day of the week. This will come in handy when joining to the OpeningHours table.

Next, notice that we inner join to the OpeningHours table. By performing an inner join, we are essentially filtering out the rows from the list of dates to only include rows where the DayId matches something in the OpeningHours table.

Next, we left join to the holiday table on the date. If a date exists in the holiday table, we can count them to determine the number of holidays for the month.

Count(*) will return a count of rows in the table grouped by month.
Count(BankHoliday) will return a count of rows where there is a holiday date match. This works because if a date is NOT a holiday, the BankHoliday column will hold a null value and Count ignores null values.

As you can see....

Select Count('anything')
Select Count(Convert(int, NULL))

Does this help in your understanding? If you need further clarification, please let me know.

BTW, did you run the code? Does it work?


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

 

[Halliarse]

It does indeed help and yes it works perfectly, it's very clever!

I now need to work out how to pass the date of the first month as a parameter from the Crystal Report as well as retrieving the 12 entries of AvailableDays from the stored procedure back to the Crystal Report

 

[gmmastros]

I think I made a slight mistake.

Where you see 365 in the code, you should replace it with 366. This will then account for leap years. For years that are not a leap year, there would be a 13th row with just one day if it happens to be a working day. The TOP 12 in the query would then filter out that month. For years with a leap year, it will work properly.

Without changing the number, during leap years, the last month could be off by one day (if that last day is a work day). This would likely be a very difficult bug to find if left untreated.

As for the Crystal Reports part.... I don't know anything about it.

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

 

[philhege]

George, could you genericize the number of days based on date math?

...And number < 365

becomes

And number < datediff(d,@inputdate,dateadd(yy,1,@inputdate))

That should handle regular and leap years.

To get started on the SP that Crystal calls, you would set up the date as an input variable:

CREATE PROCEDURE MySP
(@inputdate date)


declare @yeardays int
 select @yeardays = datediff(d,@inputdate,dateadd(yy,1,@inputdate))

...
Where   Type = 'P'
        And number < @yeardays

and so on.


-----------
With business clients like mine, you'd be better off herding cats.