How to make this stored procedure flexible?

Question

Post reply

How to make this stored procedure flexible?

webskater

SSCarpal Tunnel

Points: 4214
More actions
April 11, 2009 at 5:29 am

#199121

3 tables:
tblDepartments
DepartmentID
DepartmentDescription
(there is a department in here called 'global')
tblHolidays
HolidayID
HolidayDate
HolidayDescription
tblDepartmentHolidays
DepartmentHolidayID
DepartmentID (a DepartmentID of zero means it is 'global' - i.e. applies to all departments)
HolidayID
Idea is that different departments have different 'holidays' - they all share public holidays like Christmas .. but different departments are not available on specific days - attending seminars, training etc.
I want to return data that will look like this:
Holiday Description Holiday Date Global Dept1 Dept2 Dept3 Dept4
Christmas Day 25/12/2009 Yes Yes Yes Yes Yes
Seminar 2 16/06/2010 No No Yes No Yes
(Sorry, can't get that to layout in columns.) But you get the idea, each row contains data about a specific day and whether each department has that day off or not
The stored procedure I have to return the data looks like this:
DECLARE @GlobalDeptID int
SET @GlobalDeptID = (SELECT DepartmentID FROM tblDepartments WHERE DepartmentDescription = 'GlobalDepartment')
DECLARE @Dept1ID int
SET @Dept1ID = (SELECT DepartmentID FROM tblDepartments WHERE DepartmentDescription = 'Department 1')
DECLARE @Dept2ID int
SET @Dept2ID = (SELECT DepartmentID FROM tblDepartments WHERE DepartmentDescription = 'Department 2')
DECLARE @Dept3ID int
SET @Dept3ID = (SELECT DepartmentID FROM tblDepartments WHERE DepartmentDescription = 'Department 3')
DECLARE @Dept4ID int
SET @Dept4ID = (SELECT DepartmentID FROM tblDepartments WHERE DepartmentDescription = 'Department 4')
SELECT DISTINCT tbhHolidays.HolidayDescription,
CONVERT(VARCHAR(10), tbhHolidays.HolidayDate, 103) AS [Holiday Date],
CASE WHEN @GlobalDeptID IN (SELECT DepartmentID FROM tblBankHolidaysWPID WHERE tblBankHolidaysWPID.BHID = tblBankHolidays.BHID) THEN 'true' ELSE 'false' END AS 'Global',
CASE WHEN @Dept1ID IN (SELECT DepartmentID FROM tblDepartmentHolidays WHERE tblDepartmentHolidays.HolidayID = tblHolidays.HolidayID) THEN 'true' ELSE 'false' END AS 'Dept1',
CASE WHEN @Dept2ID IN (SELECT DepartmentID FROM tblDepartmentHolidays WHERE tblDepartmentHolidays.HolidayID = tblHolidays.HolidayID) THEN 'true' ELSE 'false' END AS 'Dept2',
CASE WHEN @Dept3ID IN (SELECT DepartmentID FROM tblDepartmentHolidays WHERE tblDepartmentHolidays.HolidayID = tblHolidays.HolidayID) THEN 'true' ELSE 'false' END AS 'Dept3',
CASE WHEN @Dept4ID IN (SELECT DepartmentID FROM tblDepartmentHolidays WHERE tblDepartmentHolidays.HolidayID = tblHolidays.HolidayID) THEN 'true' ELSE 'false' END AS 'Dept4'
FROM tblDepartmentHolidays
INNER JOIN tblHolidays ON tblDepartmentHolidays.HolidayID = tblHolidays.HolidayID
WHERE tblBankHolidays.HolidayDate >= (SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
ORDER BY tblHolidays.HolidayDate
Now, this all works - but it is effectively hard coded. What happens when someone adds Department 5 - or deletes Department 3.
I want to write this in such a way that the departments are retrieved from the departments table - and then 'used' - rather than manually getting the DepartmentID of each department and testing to see if this is in the tblDepartmentHolidays table - as I go through the table.
Thanks for any help.

Viewing 15 posts - 1 through 15 (of 16 total)

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

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hi

Try to handle it with a PIVOT operator.

Greets

Flo

PS: Maybe have a look to the "Best Practices" link in my signature for posting sample data and DDL. This always increases the number of answers 😉

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 2

check out the below link

http://www.databasejournal.com/features/mssql/article.php/3521101/Cross-Tab-reports-in-SQL-Server-2005.htm

This uses PIVOT based on your example.

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 3

Florian Reischl (4/11/2009)
Hi
Try to handle it with a PIVOT operator.
Greets
Flo
PS: Maybe have a look to the "Best Practices" link in my signature for posting sample data and DDL. This always increases the number of answers 😉

You may want to reconsider ever using a Pivot operator... a properly formed crosstab can be nearly twice as fast...

http://www.sqlservercentral.com/articles/T-SQL/63681/

Performance meaurements are at the bottom of the article.

--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)

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

sku370870 (4/11/2009)
Now, this all works - but it is effectively hard coded. What happens when someone adds Department 5 - or deletes Department 3.
I want to write this in such a way that the departments are retrieved from the departments table - and then 'used' - rather than manually getting the DepartmentID of each department and testing to see if this is in the tblDepartmentHolidays table - as I go through the table.
Thanks for any help.

My recommendation would be a dynamic crosstab which can easily be accomplished. See the following article.

http://www.sqlservercentral.com/articles/cross+tab/65048/

Don't forget that MAX is a valid aggregate that would probably work well here.

Also, if you want a canned solution, we need some readily consumable test data... take a look at the article in my signature line below for how to pull that off.

--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)

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 5

Jeff Moden (4/11/2009)
Florian Reischl (4/11/2009)
Hi
Try to handle it with a PIVOT operator.
Greets
Flo
PS: Maybe have a look to the "Best Practices" link in my signature for posting sample data and DDL. This always increases the number of answers 😉
You may want to reconsider ever using a Pivot operator... a properly formed crosstab can be nearly twice as fast...
http://www.sqlservercentral.com/articles/T-SQL/63681/
Performance meaurements are at the bottom of the article.

Hi Jeff

Thanks for the link. I will read it next days! (It's too late now 😉 )

Greets

Flo

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 6

You bet... thanks for the feedback, Flo.

--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)

webskater SSCarpal Tunnel Points: 4214 More actions · Answer 7

Thanks for all your replies. I am trying to write a cross tab query to return the data in the way I need it.

In the meantime, taking into account the comments made about posting code that can be run ... here goes ...

Create table #tblDepartment

(DepartmentID int,

DepartmentDescription varchar(50))

go

insert into #tblDepartment select 0,'GlobalDepartment'

insert into #tblDepartment select 1,'Department 1'

insert into #tblDepartment select 2,'Department 2'

insert into #tblDepartment select 3,'Department 3'

insert into #tblDepartment select 4,'Department 4'

Create table #tblHolidays

(HolidayID int,

HolidayDate smalldatetime,

HolidayDescription varchar(50))

insert into #tblHolidays select 1, '25/12/2009', 'Christmas Day'

insert into #tblHolidays select 2, '26/12/2009', 'Boxing Day'

insert into #tblHolidays select 3, '27/12/2009', 'Christmas Break'

insert into #tblHolidays select 4, '28/12/2009', 'Christmas Break'

Create table #tblDepartmentHolidays

(DepartmentHolidayID int,

DepartmentID int,

HolidayID int)

insert into #tblDepartmentHolidays select 1, 0, 1

insert into #tblDepartmentHolidays select 2, 0, 2

insert into #tblDepartmentHolidays select 3, 1, 3

insert into #tblDepartmentHolidays select 4, 2, 3

insert into #tblDepartmentHolidays select 5, 2, 4

--SELECT * FROM #tblDepartment

--SELECT * FROM #tblHolidays

--SELECt * FROM #tblDepartmentHolidays

DECLARE @GlobalDeptID int

SET @GlobalDeptID = (SELECT DepartmentID FROM #tblDepartment WHERE DepartmentDescription = 'GlobalDepartment')

DECLARE @Dept1ID int

SET @Dept1ID = (SELECT DepartmentID FROM #tblDepartment WHERE DepartmentDescription = 'Department 1')

DECLARE @Dept2ID int

SET @Dept2ID = (SELECT DepartmentID FROM #tblDepartment WHERE DepartmentDescription = 'Department 2')

DECLARE @Dept3ID int

SET @Dept3ID = (SELECT DepartmentID FROM #tblDepartment WHERE DepartmentDescription = 'Department 3')

DECLARE @Dept4ID int

SET @Dept4ID = (SELECT DepartmentID FROM #tblDepartment WHERE DepartmentDescription = 'Department 4')

SELECT DISTINCT #tblHolidays.HolidayDescription As [Description],

#tblHolidays.HolidayDate AS [Holiday Date],

CASE WHEN @GlobalDeptID IN (SELECT DepartmentID FROM #tblDepartmentHolidays WHERE #tblDepartmentHolidays.HolidayID = #tblHolidays.HolidayID) THEN 'true' ELSE 'false' END AS 'Global',

CASE WHEN @Dept1ID IN (SELECT DepartmentID FROM #tblDepartmentHolidays WHERE #tblDepartmentHolidays.HolidayID = #tblHolidays.HolidayID) THEN 'true' ELSE 'false' END AS 'Dept1',

CASE WHEN @Dept2ID IN (SELECT DepartmentID FROM #tblDepartmentHolidays WHERE #tblDepartmentHolidays.HolidayID = #tblHolidays.HolidayID) THEN 'true' ELSE 'false' END AS 'Dept2',

CASE WHEN @Dept3ID IN (SELECT DepartmentID FROM #tblDepartmentHolidays WHERE #tblDepartmentHolidays.HolidayID = #tblHolidays.HolidayID) THEN 'true' ELSE 'false' END AS 'Dept3',

CASE WHEN @Dept4ID IN (SELECT DepartmentID FROM #tblDepartmentHolidays WHERE #tblDepartmentHolidays.HolidayID = #tblHolidays.HolidayID) THEN 'true' ELSE 'false' END AS 'Dept4'

FROM #tblDepartmentHolidays

INNER JOIN #tblHolidays ON #tblDepartmentHolidays.HolidayID = #tblHolidays.HolidayID

WHERE #tblHolidays.HolidayDate >= (SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))

ORDER BY #tblHolidays.HolidayDate

This will (hopefully) run and show what I am trying to achieve.

Again, thanks for your input.

webskater SSCarpal Tunnel Points: 4214 More actions · Answer 8

I now have this ...

SELECT #tblHolidays.HolidayDescription As [Description], #tblHolidays.HolidayDate AS [Holiday Date],

count(case #tblDepartmentHolidays.DepartmentID when 0 then 1 else null end) as [Global],

count(case #tblDepartmentHolidays.DepartmentID when 1 then 1 else null end) as [D1],

count(case #tblDepartmentHolidays.DepartmentID when 2 then 1 else null end) as [D2],

count(case #tblDepartmentHolidays.DepartmentID when 3 then 1 else null end) as [D3],

count(case #tblDepartmentHolidays.DepartmentID when 4 then 1 else null end) as [D4]

FROM #tblDepartmentHolidays

INNER JOIN #tblHolidays ON #tblDepartmentHolidays.HolidayID = #tblHolidays.HolidayID

WHERE #tblHolidays.HolidayDate >= (SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))

AND #tblDepartmentHolidays.DepartmentID in (SELECT Distinct DepartmentID FROM #tblDepartment)

Group BY #tblHolidays.HolidayDescription, #tblHolidays.HolidayDate

ORDER BY #tblHolidays.HolidayDate

... which, applied to the code in the post above that creates the example tables, seems to give me what I want. But the departments are still effectively hard coded. Is there a way to replace those 'count' statements so that all records in the Departments table will be taken into account (rather than writing a separate 'count' statement for each departmetn and specifying (by hand) the DepartmentID for each department.

Paul White SSC Guru Points: 150467 More actions · Answer 9

Jeff Moden (4/11/2009)
You may want to reconsider ever using a Pivot operator... a properly formed crosstab can be nearly twice as fast...
http://www.sqlservercentral.com/articles/T-SQL/63681/
Performance meaurements are at the bottom of the article.

Hey Jeff & Flo,

Nested PIVOTs can equal or out-perform the examples you give in the article. I posted a fuller reply with example code as a reply to your article. Thanks for an interesting challenge! Kept me busy this evening 🙂

/Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 10

Paul White (4/12/2009)
Jeff Moden (4/11/2009)
You may want to reconsider ever using a Pivot operator... a properly formed crosstab can be nearly twice as fast...
http://www.sqlservercentral.com/articles/T-SQL/63681/
Performance meaurements are at the bottom of the article.
Hey Jeff & Flo,
Nested PIVOTs can equal or out-perform the examples you give in the article. I posted a fuller reply with example code as a reply to your article. Thanks for an interesting challenge! Kept me busy this evening 🙂
/Paul

Thanks, Paul. I'll check it out. I'm always open for improvements to the code.

--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)

Paul White SSC Guru Points: 150467 More actions · Answer 11

Jeff Moden (4/12/2009)
Thanks, Paul. I'll check it out. I'm always open for improvements to the code.

:w00t: OMG no. Please no-one look upon this as an 'imnprovement'!!! :w00t:

It's a technical curiosity, that's all. It's an ugly hack at best.

Please can the SQL Server team extend PIVOT a bit and make it faster? Hmm? Please?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 12

Heh... ok, Paul. From the way you were talking, I thought you were on to an improvement.

--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)

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 13

sku370870 (4/12/2009)
I now have this ...
SELECT #tblHolidays.HolidayDescription As [Description], #tblHolidays.HolidayDate AS [Holiday Date],
count(case #tblDepartmentHolidays.DepartmentID when 0 then 1 else null end) as [Global],
count(case #tblDepartmentHolidays.DepartmentID when 1 then 1 else null end) as [D1],
count(case #tblDepartmentHolidays.DepartmentID when 2 then 1 else null end) as [D2],
count(case #tblDepartmentHolidays.DepartmentID when 3 then 1 else null end) as [D3],
count(case #tblDepartmentHolidays.DepartmentID when 4 then 1 else null end) as [D4]
FROM #tblDepartmentHolidays
INNER JOIN #tblHolidays ON #tblDepartmentHolidays.HolidayID = #tblHolidays.HolidayID
WHERE #tblHolidays.HolidayDate >= (SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
AND #tblDepartmentHolidays.DepartmentID in (SELECT Distinct DepartmentID FROM #tblDepartment)
Group BY #tblHolidays.HolidayDescription, #tblHolidays.HolidayDate
ORDER BY #tblHolidays.HolidayDate
... which, applied to the code in the post above that creates the example tables, seems to give me what I want. But the departments are still effectively hard coded. Is there a way to replace those 'count' statements so that all records in the Departments table will be taken into account (rather than writing a separate 'count' statement for each departmetn and specifying (by hand) the DepartmentID for each department.

You've done the hard part. Now, see the following article for how to overcome the hard coding...

http://www.sqlservercentral.com/articles/cross+tab/65048/

I'd just give you the code, but then you wouldn't learn how to do it on your own.

--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)

WayneS SSC Guru Points: 95460 More actions · Answer 14

I like this thread, though from a different perspective. I like how the replies to the poster encouraged the poster to provide run-able code to duplicate the issue, and also how the replies have encouraged and fostered learning (instead of just here's some code to do what you want) for the poster. It probably took more work from the repliers to research the articles necessary to reference in their posts (well, Jeff's probably got them all memorized :-P), but in the long run, as Jeff mentioned, now the poster learns how to do that type of problem.

Well done gang.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2