January 4, 2016 at 10:23 am
Hi, This is similar to a question I have posted previously but I am much clearer now on what I need.
I have a simple Table called TblAppointments as follows...
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 ,
With test data as follows...
INSERT INTO [TblAppointments]
([ClientID],[StartDate],[ApptTime],[Duration],[StaffID])
VALUES
(3333,'2015-01-20', '10:00',30,202),
(3333,'2015-01-21', '10:00',30,202),
(3333,'2015-01-22', '10:00',30,202),
(3333,'2015-01-23', '10:00',30,203)
(3333,'2015-01-25', '17:30',30,201)
(3333,'2015-01-26', '17:30',30,201)
(3333,'2015-01-27', '17:30',30,205)
So I need a 'crosstab/pivot' query that will show all the dates from the start of a month to the end of a month (say 4 weeks) shown across the top and times down the Left and staffID in the middle!
Sound easy enough but its a bit more complicated as the dates across the top may be a week , 2 weeks or 4 weeks but will be passed to the stored procedure as Variables StartDate and EndDate.
I am assuming it will have to be a dynamic sql statement!
All help greatly appreciated!
January 4, 2016 at 10:37 am
What's the problem with the code that I posted in your previous thread?
http://www.sqlservercentral.com/Forums/FindPost1748928.aspx
Dynamic SQL is the only way to go if the columns will vary in number or names.
January 4, 2016 at 12:26 pm
Hi Luis, I am sure there is nothing wrong with it, but I am a SQL beginner and am finding dynamic sql quite confusing. I need something very simple so I am using three CTE's and then PIVOT them
I suppose I am looking for a more readable version I can understand and maintain....see below...
-- Add the parameters for the stored procedure here
@SDate Date = '2016-01-03',
@EDate Date = '2015-12-31',
@ClientID int = 4444
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Cols AS NVARCHAR(MAX),
@SQLString AS NVARCHAR(MAX);
SET @EDate = DATEADD(Week, 4, @SDate);
WITH ClientAppts AS
(
SELECT C.ApptDate, C.ApptTime, C.StaffID
FROM TblAppointments C
WHERE C.ClientID = @ClientID
),
AllDates AS
(
SELECT [Dates]= @SDate
UNION ALL
SELECT DATEADD(day, 1, Dates)
FROM AllDates
WHERE DATEADD(day, 1, Dates) <= @EDate
),
JoinData AS
(
SELECT A.* , CA.ApptTime, CA.StaffID
FROM AllDates A
LEFT JOIN ClientAppts CA
ON A.Dates = CA.ApptDate
)
SELECT @Cols = STUFF((SELECT distinct ',' + CAST(Dates AS VARCHAR)
FROM AllDates
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
PRINT @Cols
SET @SQLString = 'SELECT Dates, ClientID, StaffID
FROM
(
SELECT Dates, ClientID, StaffID
FROM JoinData
) X
PIVOT
(
MIN(StaffID)
FOR CAST(Dates AS VARCHAR) IN (' + @cols + ')
) PVT '
execute(@SQLString)
END
My @Cols = a list of dates seperated by commas.
2016-01-03,2016-01-04,2016-01-05,2016-01-06,2016-01-07,2016-01-08,2016-01-09,2016-01-10,2016-01-11,2016-01-12,2016-01-13,2016-01-14,2016-01-15,2016-01-16,2016-01-17,2016-01-18,2016-01-19,2016-01-20,2016-01-21,2016-01-22,2016-01-23,2016-01-24,2016-01-25,2016-01-26,2016-01-27,2016-01-28,2016-01-29,2016-01-30,2016-01-31
Then error
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '('.
January 4, 2016 at 12:51 pm
Your list of dates is invalid. They need to have single quotes around the values. I would highly recommend doing this as a dynamic cross tab instead of a dynamic pivot. It is faster and far easier to maintain. Additionally the syntax is far less obtuse than a pivot. Check out the links suggested by Luis. If you can't find them, they are in my signature.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 4, 2016 at 12:54 pm
Tallboy (1/4/2016)
Hi Luis, I am sure there is nothing wrong with it, but I am a SQL beginner and am finding dynamic sql quite confusing. I need something very simple so I am using three CTE's adn them PIVOT them...
Which I think should work but I get the error cant find object Joindata
That's why I suggested to read the 2 articles on cross tabs and pivots. The second one explains how to work with the dynamic sql.
In your case, you forgot to include the CTEs definition in the dynamic code, as well as sending the variables as parameters using sp_executesql. You're still using a recursive CTE to generate the dates. That might cause performance problems. Read more about it in here: http://www.sqlservercentral.com/articles/T-SQL/74118/
My previous example handles everything you need, the only thing you need to do is understand it. Go step by step on it and ask as many questions as you need until you can explain it to your rubber duck.
January 4, 2016 at 1:20 pm
If anyone else is interested, here's a corrected version of the sample data that actually returns something (other than errors).
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);
DECLARE
@SDate Date = '2016-01-03',
@EDate Date ,
@ClientID int = 3333;
SET @EDate = DATEADD(Week, 4, @SDate);
January 4, 2016 at 2:09 pm
Doh!!!
I see now I need to change the final CTE to a temporary table so the dynamic sql is ab leto use it.
While the crosstab may be quicker than PIVOT I cannot see it as a more readable !
Thank you for all the help. I will try out both methods and see how I get on.
SO here is my latest draft using a Calendar Table and 3 temp tables.
But still getting an error with
'2016-01-03','2016-01-04','2016-01-05','2016-01-06','2016-01-07','2016-01-08','2016-01-09','2016-01-10','2016-01-11','2016-01-12','2016-01-13','2016-01-14','2016-01-15','2016-01-16','2016-01-17','2016-01-18','2016-01-19','2016-01-20','2016-01-21','2016-01-22','2016-01-23','2016-01-24'
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ''.
-- Add the parameters for the stored procedure here
@SDate Date = '2016-01-03',
@EDate Date = '2016-12-31',
@ClientID int = 4444
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Cols AS NVARCHAR(MAX),
@SQLString AS NVARCHAR(MAX);
SET @EDate = DATEADD(Week, 4, @SDate);
CREATE TABLE #JoinData (Dates DATE, ApptTime TIME, StaffID INT);
CREATE TABLE #AllDates (Dates DATE);
CREATE TABLE #ClientAppts (ApptDate DATE, ApptTime TIME, StaffID INT);
INSERT INTO #ClientAppts (ApptDate,ApptTime,StaffID)
(
SELECT C.ApptDate, C.ApptTime, C.StaffID
FROM TblAppointments C
WHERE C.ClientID = @ClientID
);
INSERT INTO #AllDates (Dates)
( SELECT CalDate FROM TblCalendar WHERE CalDate Between @SDate and @EDate
--SELECT [Dates]= @SDate
--== UNION ALL
-- SELECT DATEADD(day, 1, Dates)
-- FROM #AllDates
-- WHERE DATEADD(day, 1, Dates) <= @EDate
);
INSERT INTO #JoinData (Dates, ApptTime, StaffID)
(
SELECT A.Dates , CA.ApptTime, CA.StaffID
FROM #AllDates A LEFT JOIN #ClientAppts CA ON A.Dates = CA.ApptDate
);
SELECT @Cols = STUFF((SELECT distinct ',' + '''' + CAST(Dates AS VARCHAR)+ ''''
FROM #AllDates
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
PRINT @Cols;
SET @SQLString = 'SELECT Dates, ApptTime, StaffID
FROM
(
SELECT Dates, ApptTime, StaffID
FROM #JoinData
) X
PIVOT
(
MIN(StaffID)
FOR Cast(Dates AS NVARCHAR) IN (' + @cols + ')
) PVT '
EXEC sp_executesql @SQLString --, N'@SDate date ', @SDate
DROP TABLE #JoinData
END
HELP !
January 4, 2016 at 4:13 pm
Anyone ???
January 5, 2016 at 12:21 am
The best way to troubleshoot dynamic SQL is to add PRINT @SQLString just before the EXEC or sp_executesql statement. That way you can look at the SQL that was submitted, and if needed copy/paste it in a separate window, do some formatting, and look at the line number in the error message (which unfortunately is not always exactly accurate, but at least it's something)
And if you don't see the problem, then you can copy/paste the invoked SQL in your next message and have our collective brains look at it. Once we spot the error in the SQL, the next step (fixing the part that generates the SQL) is usually obvious.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply