August 24, 2018 at 8:45 am
Hi,
I have written a stored proc query creating a number of tmp table to use in a sql string to create a crosstab for a month of dates.
To me it looks correct but I get the follow errors at the end of each SELECT INTO #TmpTable
Msg 156, Level 15, State 1, Procedure GetMonthlyDiary, Line 28
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Procedure GetMonthlyDiary, Line 35
Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Procedure GetMonthlyDiary, Line 46
Incorrect syntax near the keyword 'SELECT'.
see query below.. -- Add the parameters for the stored procedure here
@StartDate AS Date = '2018/6/01',
@TherapistID AS Integer = 80 -- Claire Veitch
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @EndDate DATE = DateAdd(mm,1,@StartDate);
DECLARE @strDays NVARCHAR(MAX);
DECLARE @query NVARCHAR(MAX);
SELECT ApptDate, ApptTime, PatientID
INTO #Diary FROM
(
SELECT PA.ApptDate, PA.ApptTime, PA.PatientID --, DATEPART(month, PA.ApptDate) AS MonthAppt
FROM TblPatientAppointments PA
WHERE PA.TherapistID = @TherapistID
AND (DATEPART(month,PA.ApptDate) = DATEPART(month,@StartDate) AND DATEPART(year,PA.ApptDate) = DATEPART(year, @StartDate))
)
SELECT myDate INTO #Calendar FROM
(
SELECT @StartDate AS myDate
UNION ALL
SELECT DATEADD(day,1,myDate) as myDate
FROM #Calendar
WHERE DATEADD(day,1,myDate) < @EndDate)
)
SELECT myDate, ISNULL(PatientID,0) AS PatientID, ApptTime
INTO #JoinData FROM
(
SELECT C.myDate, PA.PatientID, PA.ApptTime
FROM #Calendar C
LEFT JOIN #Diary PA
ON C.myDate = PA.ApptDate
)
SELECT @strDays = COALESCE(@strDays + ', ['+ convert(varchar(8), [myDate],112) + ']', '['+ convert(varchar(8), [myDate],112) + ']')
FROM #Calendar
-- PRINT @strDays
-- GROUP BY Day of week!
-- SELECT *
-- FROM Diarycte
-- FROM Calendarcte
-- FROM JoinDataCte
SET @query = N'SELECT * FROM
(
SELECT * FROM #JoinData
) x
PIVOT
(
SUM(PatientID) FOR [myDate] IN (' + @strDays + ')
) p
'
EXEC SP_EXECUTESQL @query;
END
August 24, 2018 at 1:04 pm
Apologies, I dont' have time to dig in, but this might help: http://www.sqlservercentral.com/articles/Dynamic+SQL/131473/
August 24, 2018 at 1:20 pm
Tallboy - Friday, August 24, 2018 8:45 AMHi,
I have written a stored proc query creating a number of tmp table to use in a sql string to create a crosstab for a month of dates.To me it looks correct but I get the follow errors at the end of each SELECT INTO #TmpTable
Msg 156, Level 15, State 1, Procedure GetMonthlyDiary, Line 28
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Procedure GetMonthlyDiary, Line 35
Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Procedure GetMonthlyDiary, Line 46
Incorrect syntax near the keyword 'SELECT'.
see query below..-- Add the parameters for the stored procedure here
@StartDate AS Date = '2018/6/01',
@TherapistID AS Integer = 80 -- Claire Veitch
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @EndDate DATE = DateAdd(mm,1,@StartDate);
DECLARE @strDays NVARCHAR(MAX);
DECLARE @query NVARCHAR(MAX);
SELECT ApptDate, ApptTime, PatientID
INTO #Diary FROM
(
SELECT PA.ApptDate, PA.ApptTime, PA.PatientID --, DATEPART(month, PA.ApptDate) AS MonthAppt
FROM TblPatientAppointments PA
WHERE PA.TherapistID = @TherapistID
AND (DATEPART(month,PA.ApptDate) = DATEPART(month,@StartDate) AND DATEPART(year,PA.ApptDate) = DATEPART(year, @StartDate))
)
SELECT myDate INTO #Calendar FROM
(
SELECT @StartDate AS myDate
UNION ALL
SELECT DATEADD(day,1,myDate) as myDate
FROM #Calendar
WHERE DATEADD(day,1,myDate) < @EndDate)
)
SELECT myDate, ISNULL(PatientID,0) AS PatientID, ApptTime
INTO #JoinData FROM
(
SELECT C.myDate, PA.PatientID, PA.ApptTime
FROM #Calendar C
LEFT JOIN #Diary PA
ON C.myDate = PA.ApptDate
)
SELECT @strDays = COALESCE(@strDays + ', ['+ convert(varchar(8), [myDate],112) + ']', '['+ convert(varchar(8), [myDate],112) + ']')
FROM #Calendar
-- PRINT @strDays
-- GROUP BY Day of week!
-- SELECT *
-- FROM Diarycte
-- FROM Calendarcte
-- FROM JoinDataCte
SET @query = N'SELECT * FROM
(
SELECT * FROM #JoinData
) x
PIVOT
(
SUM(PatientID) FOR [myDate] IN (' + @strDays + ')
) p
'
EXEC SP_EXECUTESQL @query;
END
Your selects need a table alias:
SELECT X.myDate INTO #Calendar FROM
(
SELECT @StartDate AS myDate
UNION ALL
SELECT DATEADD(day,1,myDate) as myDate
FROM #Calendar
WHERE DATEADD(day,1,myDate) < @EndDate)
) AS X
August 24, 2018 at 3:11 pm
/* RCIT Database Support
By: Joe Torre
On: Aug 24th 2018
For: This funtion returns weekly inerval values for the range passed in
DECLARE @d date = GetDate();
SELECT
BegDt
, EndDt
FROM dbo.WeeklyCalendar ('20150101', @d);
*/
CREATE FUNCTION [dbo].[DailyCalendar] (@BegDate datetime, @EndDate datetime) RETURNS TABLE
AS RETURN
(
August 24, 2018 at 4:23 pm
Hi Guys, thank you so much for your replies, I will start using them.
Jonathan looks like he has the solution for me!
Joe I've little experience with tally tables, I used tem once before, I have no idea how they work, they looks more complicated than the recursion, but may be quicker, I'd love to talk more about them tho.,
they seem to be from a diiferent era, before cte or am I being ageist!!!
Thanks again, I'll play around with them and get back to you...
kind regards
August 24, 2018 at 5:04 pm
You should read Jeff Moden's article about Tally Tables. They're super useful once you understand how to use them
August 27, 2018 at 3:24 am
Hi,
Jonathans answer worked for most of my query except recursive SELECT..
I'm now getting an error
Msg 2770, Level 16, State 1, Procedure GetMonthlyApptCrosstab, Line 33
The SELECT INTO statement cannot have same source and destination tables.
relating to table #Calendar
SELECT Y.myDate INTO #Calendar FROM
(
SELECT @StartDate AS myDate
UNION ALL
SELECT DATEADD(day,1,myDate) as myDate
FROM #Calendar
WHERE DATEADD(day,1,myDate) < @EndDate
)AS Y
August 27, 2018 at 5:15 am
Hi,
I replaced the recursive SELECT with a WHILE loop (which I don't like doing so if there's any other way, please let me know) and it all worked!!!...
CREATE TABLE #Calendar(myDate Date PRIMARY key);
.
.
.
.
WHILE @NewDate < @EndDate
BEGIN
SET @NewDate = DATEADD(day,1,@NewDate)
INSERT INTO #Calendar VALUES(@NewDate)
END
So now I am trying to produce a crosstab with monthly dates across the top, appointment times down the left and client names in the middle
The crosstab produces the dates across the top, but I now have to add the appointment times, even when they are null down the side.
I was thinking of doing a LEFT JOIN in the sql code such as
SELECT AT.ApptTime
FROM tblApptTime AT
LEFT JOIN (..above SQL...) Q
ON AT.ApptTime = Q.AppTime
This works but I lose the crosstab dates across the top? Any idea folks???
HI, I read the tally guide but its seems far more complicated a than a recursive or loop!, but I know there popular to use.
(I think I'd need a course on tally tables...lol..)
kind regards
Gerry
August 27, 2018 at 6:27 am
Tallboy - Monday, August 27, 2018 3:24 AMHi,
Jonathans answer worked for most of my query except recursive SELECT..I'm now getting an error
Msg 2770, Level 16, State 1, Procedure GetMonthlyApptCrosstab, Line 33
The SELECT INTO statement cannot have same source and destination tables.relating to table #Calendar
SELECT Y.myDate INTO #Calendar FROM
(
SELECT @StartDate AS myDate
UNION ALL
SELECT DATEADD(day,1,myDate) as myDate
FROM #Calendar
WHERE DATEADD(day,1,myDate) < @EndDate
)AS Y
The SELECT INTO statement creates a new table. So clearly you can't select from it and create it at the same time.
If the #Calendar table already exists you should insert into it with INSERT INTO #Calendar
...
SELECT ...
August 27, 2018 at 6:27 am
Tallboy - Monday, August 27, 2018 5:15 AMHi,
I replaced the recursive SELECT with a WHILE loop (which I don't like doing so if there's any other way, please let me know) and it all worked!!!...
CREATE TABLE #Calendar(myDate Date PRIMARY key);
.
.
.
.
WHILE @NewDate < @EndDate
BEGIN
SET @NewDate = DATEADD(day,1,@NewDate)
INSERT INTO #Calendar VALUES(@NewDate)
ENDSo now I am trying to produce a crosstab with monthly dates across the top, appointment times down the left and client names in the middle
The crosstab produces the dates across the top, but I now have to add the appointment times, even when they are null down the side.
I was thinking of doing a LEFT JOIN in the sql code such as
SELECT AT.ApptTime
FROM tblApptTime AT
LEFT JOIN (..above SQL...) Q
ON AT.ApptTime = Q.AppTimeThis works but I lose the crosstab dates across the top? Any idea folks???
HI, I read the tally guide but its seems far more complicated a than a recursive or loop!, but I know there popular to use.
(I think I'd need a course on tally tables...lol..)
kind regardsGerry
You should raise a new question for this
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply