January 4, 2016 at 6:20 pm
OK, heres another go at the crosstab query NOT using pivot.
So basically I have a 4 column table called TblAppointment consisting of (ClientID int,ApptDate date, ApptTime Time(5), Duration tiny int, StaffID int) ApptDate, ApptTime and StaffID can be various values
With this I have 2 temporary tables which is a list of all dates say 28.
Now I want to have the All 28 dates across the top, only the times of the actual appointments down the left and StaffID in the centre...sounds simple..but I couldn't be more wrong!
While this code works it does not show the actual the different StaffID in the centre of the crosstab.
CREATE TABLE [dbo].[TblAppointments](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ClientID] [int] NOT NULL,
[ApptDate] [date] NOT NULL,
[ApptTime] [time](4) NOT NULL,
[StaffID] [int] NOT NULL,
[Duration] [int] NULL,
CONSTRAINT [PK_TblAppointments] PRIMARY KEY CLUSTERED (ID)
)
INSERT INTO [TblAppointments]
([ClientID],[ApptDate],[ApptTime],[Duration],[StaffID])
VALUES
(3333,'2016-01-20', '10:00',30,202),
(3333,'2016-01-21', '10:00',30,202),
(3333,'2016-01-22', '10:00',30,202),
(3333,'2016-01-23', '10:00',30,203),
(3333,'2016-01-25', '17:30',30,201),
(3333,'2016-01-26', '17:30',30,201),
(3333,'2016-01-27', '17:30',30,205);
My lastes code which does run looks like this
CREATE PROCEDURE [dbo].[TestCrosstab2Between2DatesV4]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Declare
@SDate Date = '2016-01-03',
@EDate Date = '2016-01-31',
@ClientID int = 5555
DECLARE @SQL AS NVARCHAR(MAX);
-- make up some sample data to work with
CREATE TABLE #Calendar (calDate DATE)
CREATE TABLE #ClientAppts (ApptDate DATE, ClientID INT, ApptTime TIME(7), StaffID Int)
INSERT INTO #ClientAppts (ApptDate, ClientID, ApptTime, StaffID)
SELECT A.ApptDate, A.ClientID, A.ApptTime, A.StaffID FROM TblAppointments A
WHERE ClientID = @ClientID;
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
)
INSERT INTO #Calendar(calDate)
SELECT TOP(DATEDIFF( dd, @SDate, @EDate) + 1) --Create all the dates needed with the use of a tally table
DATEADD( dd, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, @SDate) calDate
FROM E4
--Create the hours
SELECT @SQL = N'WITH cteTimes(ApptTime) AS(
SELECT DISTINCT CA.AppTime
FROM #ClientAppts CA
)
SELECT t.ApptTime ' +
--Generate the dynamic columns
(SELECT CHAR(10) + ' ,MAX(CASE WHEN ''' + CONVERT( char(8), calDate, 112) + ''' BETWEEN @SDate AND @EDate THEN StaffId END) AS [' + CONVERT( char(10), calDate, 121) + ']'
FROM #Calendar
FOR XML PATH(''),TYPE).value('.', 'nvarchar(max)')
+ N'
FROM cteTimes t
LEFT JOIN #ClientAppts s ON t.ApptTime = s.ApptTime --Add any parameters here to ensure the left join stays like that
AND s.ApptDate <= @EDate AND s.ApptDate >= @SDate
GROUP BY t.ApptTime
;'
--This is for debug purposes
PRINT @SQL;
--This is the actual execution
EXEC sp_executesql @SQL, N'@SDate date, @EDate date', @SDate, @EDate
DROP TABLE #ClientAppts
DROP TABLE #Calendar
END
January 4, 2016 at 7:05 pm
Spelling error got you. Check the spelling on all occurrences of "AppTime" and change them to "ApptTime", which is what is actually in the table.
If we do that on our end for testing, does it reliably show what you're talking about?
--Jeff Moden
January 4, 2016 at 9:44 pm
This will do the trick for the example table given. As usual, details are in the comments. I will say that you didn't need the Temp Tables at all. Thought I'd also give you an example of what my production could looks like.
If you want to read up on how to approach these types of things in the future, please see the following article for how to make it a whole lot easier to dev and test before wading into the dynamic SQL.
http://qa.sqlservercentral.com/articles/Crosstab/65048/
I also used my "token replacement" method to keep the dynamic SQL simple. No real payoff on that for this simple thing but can really pay off for much more complicated dynamic SQL.
CREATE PROCEDURE dbo.TestCrosstab2Between2DatesV4
/**********************************************************************************************************************
Purpose:
Appointment/Attending Staff Planning Sheet
Given a start date, end date, and a Client ID, return a horizontal calendar of contiguous days with vertical times
slots for the times when appointments occur sometime during the span of days. The attending StaffID will be positioned
at the intersection of date and time.
Usage Examples:
--===== Basic Syntax
EXEC dbo.TestCrosstab2Between2DatesV4 @pSDate, @pEDate, @pClientID [,@pDebug];
--===== Working Example
EXEC dbo.TestCrosstab2Between2DatesV4
@pSDate = '2016-01-03'
,@pEDate = '2016-01-31'
,@pClientID = 3333
,@pDebug = 1 -- If > 0, then display dynamic SQL and execute. If missing or = 0, just execute.
;
Programmer's notes:
1. If more than 1 StaffID is assigned for a given appointment, only the largest numbered StaffID will be returned.
Revision History:
Rev 01 - 04 Jan 2016 - Jeff Moden
- Initial creation and unit test
- Reverence - http://qa.sqlservercentral.com/articles/Crosstab/65048/
- Reference - http://qa.sqlservercentral.com/Forums/Topic1749547-392-1.aspx
**********************************************************************************************************************/
--===== Procedure parameters
@pSDate DATE
,@pEDate DATE
,@pClientID INT
,@pDebug TINYINT = 0
AS
--=====================================================================================================================
-- Presets and Declarations
--=====================================================================================================================
--===== Environment Presets
SET NOCOUNT ON;
SET XACT_ABORT ON;
--===== Local Variables
DECLARE @SQL NVARCHAR(MAX)
;
--=====================================================================================================================
-- Create the dynamic SQL to build the report from the start and end dates
--=====================================================================================================================
WITH
--========== Create the days from the start date to the end date
E1(N) AS (SELECT N FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))d(N)),
Calendar(DT) AS (SELECT TOP (DATEDIFF(dd,@pSDate,@pEDate)+1)
DT = DATEADD(dd,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,@pSDate)
FROM E1 a, E1 b, E1 c, E1 d, E1 e, E1 F)
SELECT @SQL = '
SELECT ApptTime = LEFT(appt.ApptTime,5)' --Format this whatever way you want.
+
( --=== Create the dynamic columns for each date in the Calendar CTE above.
SELECT CHAR(10)
+ REPLACE(REPLACE(
SPACE(8) + ',<<QDate1>> = MAX(CASE WHEN appt.ApptDate = <<QDate2>> THEN CAST(StaffID AS VARCHAR(10)) ELSE '''' END)'
,'<<QDate1>>',QUOTENAME(cal.DT))
,'<<QDate2>>',QUOTENAME(cal.DT,''''))
FROM Calendar cal
ORDER BY cal.DT
FOR XML PATH(''),TYPE).value('.', 'NVARCHAR(MAX)'
) --=== Create the rest of the static dynamic SQL
+'
FROM dbo.TblAppointments appt
WHERE appt.ApptDate >= @pSDate
AND appt.ApptDate < DATEADD(dd,1,@pEDate)
AND appt.ClientID = @pClientID
GROUP BY appt.ApptTime
ORDER BY appt.ApptTime;
'
;
--=====================================================================================================================
-- Display and execute
--=====================================================================================================================
--===== If we're in the DEBUG mode, display the dynamic SQL... ALL of it! ;-)
IF @pDebug > 0
SELECT DynamicSqL =
(
SELECT '--' + CHAR(10) + @SQL + CHAR(10)
AS [processing-instruction(BigString)]
FOR XML PATH(''), TYPE
)
;
--===== Execute the Dynamic SQL
EXEC sp_executesql @SQL
,N'@pSDate DATE, @pEDate DATE, @pClientID INT'
, @pSDate , @pEDate , @pClientID
;
GO
--Jeff Moden
January 4, 2016 at 9:52 pm
Further on Jeff's correction, the ClientID in the sample data is different from the one hard coded in the procedure, change these two things and you will get back two rows (with the supplied data), one for each [ApptTime].
😎
January 4, 2016 at 9:55 pm
Eirikur Eiriksson (1/4/2016)
Further on Jeff's correction, the ClientID in the sample data is different from the one hard coded in the procedure, change these two things and you will get back two rows (with the supplied data), one for each [ApptTime].😎
Yeah... just not the right ones in the right spots, so rewrote it to make it simpler to understand and not require Temp Tables.
--Jeff Moden
January 4, 2016 at 10:34 pm
Jeff Moden (1/4/2016)
Eirikur Eiriksson (1/4/2016)
Further on Jeff's correction, the ClientID in the sample data is different from the one hard coded in the procedure, change these two things and you will get back two rows (with the supplied data), one for each [ApptTime].😎
Yeah... just not the right ones in the right spots, so rewrote it to make it simpler to understand and not require Temp Tables.
Pretty much the way I would do it and I agree, no need for temp table here.
😎
Haven't run it yet but looking at the code the logic doesn't seem right, the the StaffID is always going to be the MAX for each row key.
January 4, 2016 at 10:40 pm
Eirikur Eiriksson (1/4/2016)
Jeff Moden (1/4/2016)
Eirikur Eiriksson (1/4/2016)
Further on Jeff's correction, the ClientID in the sample data is different from the one hard coded in the procedure, change these two things and you will get back two rows (with the supplied data), one for each [ApptTime].😎
Yeah... just not the right ones in the right spots, so rewrote it to make it simpler to understand and not require Temp Tables.
Pretty much the way I would do it and I agree, no need for temp table here.
😎
Haven't run it yet but looking at the code the logic doesn't seem right, the the StaffID is always going to be the MAX for each row key.
It's right but only like the notes in the code say. To your point, it'll only work correctly if there's only one StaffID per appointment time. Run it against the test data the op provided and see.
--Jeff Moden
January 4, 2016 at 11:09 pm
Jeff Moden (1/4/2016)
Eirikur Eiriksson (1/4/2016)
Jeff Moden (1/4/2016)
Eirikur Eiriksson (1/4/2016)
Further on Jeff's correction, the ClientID in the sample data is different from the one hard coded in the procedure, change these two things and you will get back two rows (with the supplied data), one for each [ApptTime].😎
Yeah... just not the right ones in the right spots, so rewrote it to make it simpler to understand and not require Temp Tables.
Pretty much the way I would do it and I agree, no need for temp table here.
😎
Haven't run it yet but looking at the code the logic doesn't seem right, the the StaffID is always going to be the MAX for each row key.
It's right but only like the notes in the code say. To your point, it'll only work correctly if there's only one StaffID per appointment time. Run it against the test data the op provided and see.
Sorry Jeff, my bad, I was referring to the O/P code.
😎
Edit: Typo
January 5, 2016 at 3:41 am
H Guys, thank you for all the posts. Its great support to know that there all these experts available on this forum!
Well done, correcting the spelling error of ApptTime has produced a crosstab.:-D
Although there are now 2 problems
In the test data there are 4 dates with 4 different StaffID's
IN the crosstab the whole 10:00am row shows the same appointments with the same StaffID !
I havent had a chance yet to test Jeff solution so am looking forward to it.
It's like a baptism of fire trying to understand Dynmaic crosstab sql!
January 5, 2016 at 5:30 am
Hi,
Just ran Jeff's code! Worked Brilliantly...Genius....Thank you so much! :-):-):-):-)
Also I should have added that ClientID, ApptDate, ApptTime, StaffID are a unique index, therefor no 2 staff will be on at the same date and time for the same Client.Therefor MAX(StaffID) will always hoe correct ID!, could I have used some other function instead of MAX?
I haven't a clue how it the SQL String actually works! :crazy: and if I have to change it in any way its going to be difficult!
There so many words like XML ,'<<QDate1>>' wot do they do???
What is debug mode do I need to keep it in there?
I can read Jeffs comments and its all makes sense, but will have to give it a go! When I read the 'debug' sql string it looks good too!
Is it possible to reverse the dates so they read 01-12-2016 i.e. 1st of Dec 2016.
When I link it to a .Net Datagridview the Dates may reverse anyway!
SQL Central is Brill 😀
Thanks again Guys
January 5, 2016 at 6:14 am
Tallboy (1/5/2016)
Hi,Just ran Jeff's code! Worked Brilliantly...Genius....Thank you so much!
But I haven't a clue how it works!
There so many words like XML ,'<<QDate1>>' wot do they do???
What is debug mode do I need to keep it in there!
I can read Jeffs comments and its all makes sense, but will have to give it a try! When I read the debug sql string it looks good too, but if I have to change it in any way its going to be difficult!
Is it possible to reverse the dates so they read 01-12-2016 i.e. 1st ode Dec 2016.
When I link it to a .Net Datagridview the Dates may reverse anyway!
Thanks again Guys
Thanks for the feedback. Before I get to explaining in more detail, take a look at the link I posted right after the second paragraph of my "code post". It explains how to start a dynamic crosstab and have it tested before writing any dynamic SQL.
Shifting gears to the new problem at hand, I have a couple of questions when it comes to having more than 1 StaffID per appointment.
1. Can you modify the test data you previously posted to reflect that nuance, please? This would obviously keep us from having to guess as well making it easier to understand exactly what your data looks like?
2. Can you show or explain what the content of each "cell in the middle" should look like when it contains more than one StaffID?
--Jeff Moden
January 5, 2016 at 5:19 pm
Hi Jeff,
Well I initially wanted to get the problem as simple as possible until 'we' got the code working, which you did.
Thats why my Appointments table had only 5 fields in my example. But now that it is I can extend the Appointment table to include duration and a few other fields.
I have then created a view called 'VwAppointments' in which I join TblAppointments.StaffID to StaffDetails.ID Table and add in staff details line name.
Then I concatenate the first name, surname, duration and approved status into a string called 'Staff' and instead of showing StaffId in the centre of the crosstab I show Staff! The make the Staff name and appt duration and approved status appear in the column.
I have this working so far and will post the code tomorrow for your advice please.
Each Client can have multiple appointments at the sameday and time by different staff - if that makes sense. It basicaly mimc's several Staff attending to a Client at the same day and time. But no Staff member can be out twice to same client on same time and same day!
Anyways more tomorrow its midnight here now!
January 6, 2016 at 9:58 am
Here is my View VwAppointments1 joining TblAppointments to TblStaff.
SELECT dbo.TblAppointments.ClientID, dbo.TblAppointments.ApptDate, dbo.TblAppointments.ApptTime,
dbo.TblStaff.FirstName + N' ' + dbo.TblStaff.Surname + N' ' + CAST(dbo.TblAppointments.Duration AS VARCHAR) + N' mins ' AS Staff
FROM dbo.TblAppointments LEFT OUTER JOIN
dbo.TblStaff ON dbo.TblAppointments.StaffID = dbo.TblStaff.PersonnelNo
And here is Jeff amended store proc to turn View into crosstab with string in the middle showing staff name and appt duration! Working well!
PROCEDURE [dbo].[TestCrosstab2Between2DatesV6]
-- Usage Examples:Basic Syntax
-- EXEC dbo.TestCrosstab2Between2DatesV4 @pSDate, @pEDate, @pClientID [,@pDebug];
-- Procedure parameters
@pSDate DATE = '2016-01-03'
,@pEDate DATE = '2016-01-31'
,@pClientID INT = 2528
,@pDebug TINYINT = 0 --Boolean Flag to turn off printing script!
AS
-- Presets and Declarations
-- Environment Presets
SET NOCOUNT ON;
SET XACT_ABORT ON;
-- Local SQL String Variables
DECLARE @SQL NVARCHAR(MAX);
-- Create the dynamic SQL to build the report from the start and end dates
WITH
-- Create the days from the start date to the end date
E1(N) AS (SELECT N FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))d(N)),
Calendar(calDate) AS
(SELECT TOP (DATEDIFF(dd,@pSDate,@pEDate)+1) --No of Days e.g.28
calDate = DATEADD(dd,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,@pSDate)
FROM E1 a, E1 b, E1 c, E1 d, E1 e, E1 F)
SELECT @SQL = 'SELECT ApptTime = LEFT(appt.ApptTime,5)' --Format this whatever way you want.
+
( -- Create the dynamic columns for each date in the Calendar CTE above.
SELECT CHAR(10)
+ REPLACE(REPLACE(SPACE(8) + ',<<QDate1>> = MAX(CASE WHEN appt.ApptDate = <<QDate2>>' +
' THEN Staff ELSE '''' END)'
,'<<QDate1>>',QUOTENAME(cal.calDate)),'<<QDate2>>',QUOTENAME(cal.calDate,''''))
FROM Calendar cal
ORDER BY cal.calDate
FOR XML PATH(''),TYPE).value('.', 'NVARCHAR(MAX)'
) -- Create the rest of the static dynamic SQL
+ 'FROM dbo.VwAppointments1 appt
WHERE appt.ApptDate >= @pSDate
AND appt.ApptDate < DATEADD(dd,1,@pEDate)
AND appt.ClientID = @pClientID
GROUP BY appt.ApptTime
ORDER BY appt.ApptTime;
';
-- If in the DEBUG mode, display the dynamic SQL... ALL of it! Wink
IF @pDebug > 0
SELECT DynamicSqL =
(
SELECT '--' + CHAR(10) + @SQL + CHAR(10)
AS [processing-instruction(BigString)]
FOR XML PATH(''), TYPE
)
;
--===== Now Execute the Dynamic SQL
EXEC sp_executesql @SQL
,N'@pSDate DATE, @pEDate DATE, @pClientID INT'
, @pSDate , @pEDate , @pClientID
;
January 7, 2016 at 9:51 am
Hi Folks;
Jeffs code ran brilliantly until changed my test data and added two appointment rows for the same client with same date and times data but with different StaffID's.
The result is the MAX get the highest StaffID I think! and ignores the the other row, whereas I want the crosstab to show 2 rows for each date and time but different StaffID's in the middle.
New test data is here...
2528, '10/01/2016','10:00',22011063, 30
2528, '11/01/2016','10:00',22011063, 30,
2528, '12/01/2016','10:00',22011063, 30
2528, '13/01/2016','10:00',22011063, 30
2528, '14/01/2016','10:00',22011063, 30
2528, '15/01/2016','10:00',22011063, 30
2528, '16/01/2016','10:00',22011063, 30
2528, '17/01/2016','10:00',22011063, 30
2528, '10/01/2016','15:00',22011063, 45
2528, '11/01/2016','15:00',22011063, 45
2528, '12/01/2016','15:00',22011063, 45
2528, '13/01/2016','15:00',22011063, 45
2528, '14/01/2016','15:00',22011063, 45
2528, '15/01/2016','15:00',22011063, 45
2528, '16/01/2016','15:00',22011063, 45
2528, '17/01/2016','15:00',22011063, 45
2528, '10/01/2016','10:00',22016486, 30
2528, '11/01/2016','10:00',22016486, 30
2528, '12/01/2016','10:00',22016486, 30
2528, '13/01/2016','10:00',22016486, 30
2528, '14/01/2016','10:00',22016486, 30
2528, '15/01/2016','10:00',22016486, 30
2528, '16/01/2016','10:00',22016486, 30
2528, '17/01/2016','10:00',22016486, 30
January 7, 2016 at 5:13 pm
Tallboy (1/7/2016)
Hi Folks;Jeffs code ran brilliantly until changed my test data and added two appointment rows for the same client with same date and times data but with different StaffID's.
The result is the MAX get the highest StaffID I think! and ignores the the other row, whereas I want the crosstab to show 2 rows for each date and time but different StaffID's in the middle.
New test data is here...
2528, '10/01/2016','10:00',22011063, 30
2528, '11/01/2016','10:00',22011063, 30,
2528, '12/01/2016','10:00',22011063, 30
2528, '13/01/2016','10:00',22011063, 30
2528, '14/01/2016','10:00',22011063, 30
2528, '15/01/2016','10:00',22011063, 30
2528, '16/01/2016','10:00',22011063, 30
2528, '17/01/2016','10:00',22011063, 30
2528, '10/01/2016','15:00',22011063, 45
2528, '11/01/2016','15:00',22011063, 45
2528, '12/01/2016','15:00',22011063, 45
2528, '13/01/2016','15:00',22011063, 45
2528, '14/01/2016','15:00',22011063, 45
2528, '15/01/2016','15:00',22011063, 45
2528, '16/01/2016','15:00',22011063, 45
2528, '17/01/2016','15:00',22011063, 45
2528, '10/01/2016','10:00',22016486, 30
2528, '11/01/2016','10:00',22016486, 30
2528, '12/01/2016','10:00',22016486, 30
2528, '13/01/2016','10:00',22016486, 30
2528, '14/01/2016','10:00',22016486, 30
2528, '15/01/2016','10:00',22016486, 30
2528, '16/01/2016','10:00',22016486, 30
2528, '17/01/2016','10:00',22016486, 30
Please convert the new test data to something readily consumable. Please see the first link in my signature line for how to do that.
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply