April 11, 2009 at 5:29 am
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.
April 11, 2009 at 7:32 am
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 😉
April 11, 2009 at 10:10 am
check out the below link
This uses PIVOT based on your example.
April 11, 2009 at 2:32 pm
Florian Reischl (4/11/2009)
HiTry 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
Change is inevitable... Change for the better is not.
April 11, 2009 at 2:35 pm
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
Change is inevitable... Change for the better is not.
April 11, 2009 at 2:43 pm
Jeff Moden (4/11/2009)
Florian Reischl (4/11/2009)
HiTry 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
April 11, 2009 at 3:04 pm
You bet... thanks for the feedback, Flo.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 12, 2009 at 4:42 am
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.
April 12, 2009 at 5:57 am
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.
April 12, 2009 at 6:54 am
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
April 12, 2009 at 7:39 am
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
Change is inevitable... Change for the better is not.
April 12, 2009 at 7:59 am
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
April 12, 2009 at 7:35 pm
Heh... ok, Paul. From the way you were talking, I thought you were on to an improvement.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 12, 2009 at 7:38 pm
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
Change is inevitable... Change for the better is not.
April 13, 2009 at 7:16 am
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
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply