December 30, 2015 at 10:39 am
Here is code tsql code using recursion within a cte that producing a syntax error marked *** below!
DECLARE
@dt1 Date = '2015-12-01',
@dt2 Date = '2015-12-30',
@SDate Date = '2015-12-01',
@EDate Date = '2015-12-30',
@ClientID int = 2528;
WITH ClientAppts AS
(
SELECT A.ApptDate, A.ApptTime, --A.Approved +' '+
S.FirstName + ', ' + S.Surname +' '+CAST(A.Duration AS Varchar(10)) +'mins' AS Details
FROM TblAppointments A
LEFT JOIN TblStaff S
ON A.StaffID = S.PersonnelNo
WHERE A.ClientID = @ClientID
AND A.ApptDate BETWEEN @SDate and @EDate
),
DailyDates(dates) AS
(
SELECT [Dates]= @dt1
UNION ALL
SELECT dateadd(dd,1,[dates])
FROM DailyDates
WHERE dateadd(dd,1,[dates]) <= @dt2)
SELECT CONVERT(date,[dates]) AS Dates
FROM DailyDates
OPTION (maxrecursion 0)
), *** Error Here***
SELECT A.Dates , C.ApptDate, C.ApptTime, C.Details
FROM DailyDates A
LEFT JOIN ClientAppts C
ON A.dates = C.ApptDate
END
I have highlighted the line with the error but cant for th elife of me see what wrong all the examples I see on line match this format!
Any ideas?
December 30, 2015 at 10:55 am
That END statement will cause an error if there is not a BEGIN statement somewhere before it within the same transaction. I'll just assume, however, that you have a BEGIN statement...
I think the problem with your query is that the OPTION (maxrecursion 0) should be on the outside of your recursive CTE (right after your final LEFT JOIN statement.
-- Itzik Ben-Gan 2001
December 30, 2015 at 12:43 pm
The following code should be removed for it to work.
SELECT CONVERT(date,[dates]) AS Dates
FROM DailyDates
OPTION (maxrecursion 0)
),
However, there's a major problem. You have a rCTE that counts which can become a huge problem. Change the code into this:
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
),
DailyDates(Dates) AS(
SELECT TOP (DATEDIFF( dd, @dt1, @dt2) + 1)
DATEADD( dd, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, @dt1)
FROM E4
),
ClientAppts( ApptDate, ApptTime, Details) AS
(
SELECT A.ApptDate,
A.ApptTime, --A.Approved +' '+
S.FirstName + ', ' + S.Surname +' '+CAST(A.Duration AS Varchar(10)) +'mins' AS Details
FROM TblAppointments A
LEFT JOIN TblStaff S ON A.StaffID = S.PersonnelNo
WHERE A.ClientID = @ClientID
AND A.ApptDate BETWEEN @SDate and @EDate
)
SELECT A.Dates , C.ApptDate, C.ApptTime, C.Details
FROM DailyDates A
LEFT JOIN ClientAppts C
ON A.dates = C.ApptDate;
I changed the order of your CTEs just to keep the data close to the last SELECT.
December 30, 2015 at 6:19 pm
Luis Cazares (12/30/2015)
The following code should be removed for it to work.
SELECT CONVERT(date,[dates]) AS Dates
FROM DailyDates
OPTION (maxrecursion 0)
),
However, there's a major problem. You have a rCTE that counts which can become a huge problem. Change the code into this:
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
),
DailyDates(Dates) AS(
SELECT TOP (DATEDIFF( dd, @dt1, @dt2) + 1)
DATEADD( dd, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, @dt1)
FROM E4
),
ClientAppts( ApptDate, ApptTime, Details) AS
(
SELECT A.ApptDate,
A.ApptTime, --A.Approved +' '+
S.FirstName + ', ' + S.Surname +' '+CAST(A.Duration AS Varchar(10)) +'mins' AS Details
FROM TblAppointments A
LEFT JOIN TblStaff S ON A.StaffID = S.PersonnelNo
WHERE A.ClientID = @ClientID
AND A.ApptDate BETWEEN @SDate and @EDate
)
SELECT A.Dates , C.ApptDate, C.ApptTime, C.Details
FROM DailyDates A
LEFT JOIN ClientAppts C
ON A.dates = C.ApptDate;
I changed the order of your CTEs just to keep the data close to the last SELECT.
I wanted to come back and mention this earlier today but we were having network issues.
In addition to what Luis recommended I would add that this is where a properly indexed calendar table (date dimension table in the BI world) would come in handy.
-- Itzik Ben-Gan 2001
December 30, 2015 at 7:34 pm
Hi Luis,
Thanks for your prompt reply.
I know your code works as I have tested it out before. I know it creates a cte of dates but would you mind explaining how it works as I cant seem to grasp it at all. why do we need nested cte's?
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
),
DailyDates(Dates) AS(
SELECT TOP (DATEDIFF( dd, @dt1, @dt2) + 1)
DATEADD( dd, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, @dt1)
FROM E4
Also someone suggested creating a simple single column table of dates which on the face of it has the advantage of being easier to handle and maintain, I would only need 6 months of dates at any one time?
kind regards
December 30, 2015 at 8:17 pm
Tallboy (12/30/2015)
Hi Luis,Thanks for your prompt reply.
I know your code works as I have tested it out before. I know it creates a cte of dates but would you mind explaining how it works as I cant seem to grasp it at all. why do we need nested cte's?
This is a common creation of a cteTally which won't need to read from disk any information, based on the original model of Itzik Ben-Gan.
It creates sets of rows that are exponentially growing on each CTE which then generates the numbers or dates in this case.
Here's the commented version:
WITH
E(n) AS(
--We create 10 rows
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
--With a cross join we create 10 x 10 = 100 rows
SELECT a.n FROM E a, E b
),
E4(n) AS(
--With a cross join we create 100 x 100 = 10,000 rows
SELECT a.n FROM E2 a, E2 b
),
DailyDates(Dates) AS(
SELECT TOP (DATEDIFF( dd, @dt1, @dt2) + 1) --We limit the rows to what we need
DATEADD( dd, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, @dt1) --We add n-1 days to the initial date
FROM E4
)
SELECT A.Dates
FROM DailyDates A ;
You don't actually need to create all of them and can simplify the code.
WITH
DailyDates(Dates) AS(
SELECT TOP (DATEDIFF( dd, @dt1, @dt2) + 1) --We limit the rows to what we need
DATEADD( dd, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, @dt1) --We add n-1 days to the initial date
FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n),
(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E2(n),
(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E3(n) -- 10 x 10 x 10 = 1000 rows
)
SELECT A.Dates
FROM DailyDates A ;
Hope this will clarify how the code works.
December 30, 2015 at 8:59 pm
Good catch, Luis.
Tallboy, if you aren't familiar with tally tables and how they can be used, see the article at http://www.sqlservercentral.com/articles/T-SQL/62867/ for explanation and examples.
December 31, 2015 at 4:41 am
Hi Guys, I have the above code running well now but have run into another problem with a pivot table
,AllData AS
(
SELECT A.Dates , C.ApptTime, C.Details
FROM DailyDates A
LEFT JOIN ClientAppts C
ON A.dates = C.ApptDate
)
SELECT *
FROM AllData
PIVOT
(
MAX(Details)
FOR Dates IN (SELECT Dates FROM DailyDates)
)
AS p
SQL won't let me use a SELECT clause inside the PIVOT !
Help!
December 31, 2015 at 8:47 am
HERE is my latest attempt to solve the problem...
while it produce a correct list of dates on my @cols string. I need the dates to be wrapped with singel quotes...i.e. '2015-12-20',
AllData AS
(
SELECT A.Dates , C.ApptTime, C.Details --C.ApptDate,
FROM DailyDates A
LEFT JOIN ClientAppts C
ON A.dates = C.ApptDate
)
SELECT @cols = ISNULL(@cols +',', '') + CAST(Dates AS VArchar) FROM DailyDates
SELECT @SQLString = N'SELECT A.Dates , A.ApptTime, A.Details
FROM AllData A
PIVOT
(
MAX(A.Details)
FOR A.Dates IN ('+ @cols +')
)
AS p;'
EXEC sp_executesql @SQLString
--SELECT @cols
END
Any advise appreciated!
December 31, 2015 at 9:14 am
You can't create a dynamic pivot like that. You need to use dynamic SQL.
In my case, I prefer to use cross tabs instead of pivots. These is because they allow more flexibility and can give better performance when more than one pivot is needed in a single query.
For further explanation on this, check the following articles:
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/Crosstab/65048/
Here's an example of what you need according to my crystal ball:
CREATE TABLE TblAppointments(
ApptDate date,
ApptTime time,
Duration int,
StaffID int,
ClientID int
);
INSERT INTO TblAppointments
VALUES
( '20151205', '13:00', 30, 1, 2528),
( '20151206', '11:00', 60, 1, 2528),
( '20151207', '15:00', 15, 1, 2528),
( '20151208', '18:00', 10, 1, 2528),
( '20151209', '12:00', 90, 1, 2528),
( '20151210', '11:00', 120, 1, 2528);
CREATE TABLE TblStaff(
PersonnelNo int,
FirstName varchar(50),
Surname varchar(50)
);
INSERT INTO TblStaff VALUES(1, 'Luis', 'Cazares');
DECLARE
@dt1 Date = '2015-12-01',
@dt2 Date = '2015-12-30',
@SDate Date = '2015-12-01',
@EDate Date = '2015-12-30',
@ClientID int = 2528;
DECLARE @SQL nvarchar(max),
@Header nvarchar(max),
@Columns nvarchar(max),
@Footer nvarchar(max);
--Header
SELECT @Header = 'WITH ClientAppts AS
(
SELECT A.ApptDate, A.ApptTime,
S.FirstName + '', '' + S.Surname + '' '' + CAST(A.Duration AS Varchar(10)) + '' mins'' AS Details
FROM TblAppointments A
LEFT JOIN TblStaff S ON A.StaffID = S.PersonnelNo
WHERE A.ClientID = @ClientID
AND A.ApptDate BETWEEN @SDate and @EDate
),
Times(Hours) AS(
SELECT TOP (24) --We limit the rows to what we need
CAST( DATEADD( HH, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, 0) AS time)
FROM (VALUES(0),(0),(0),(0),(0))E(n),
(VALUES(0),(0),(0),(0),(0))E2(n) -- 5 x 5 = 25 rows
)
SELECT CAST( A.Hours AS CHAR(5)) AS ApptTime';
WITH DailyDates(Dates) AS(
SELECT TOP (DATEDIFF( dd, @dt1, @dt2) + 1) --We limit the rows to what we need
DATEADD( dd, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, @dt1) --We add n-1 days to the initial date
FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n),
(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E2(n),
(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E3(n) -- 10 x 10 x 10 = 1000 rows
)
SELECT @Columns = (SELECT CHAR(13) + CHAR(9) + ',MAX( CASE WHEN C.ApptDate = ' + QUOTENAME(CONVERT( char(8), Dates, 112), '''')
+ ' THEN C.Details END) AS ' + QUOTENAME(CONVERT( char(10), Dates, 120))
FROM DailyDates
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)');
SELECT @Footer = 'FROM Times A
LEFT JOIN ClientAppts C ON A.Hours = C.ApptTime
GROUP BY A.Hours;';
SET @SQL = @Header + @Columns + @Footer;
PRINT @SQL;
EXEC sp_executesql @SQL,
N'@SDate Date, @EDate Date, @ClientID int',
@SDate,
@EDate,
@ClientID;
GO
DROP TABLE TblAppointments;
DROP TABLE TblStaff;
Note that I had to create the sample data to be able to test. You should provide your sample data this way for future posts.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply