Dynamic Cross tab query using dates

  • 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

  • Apologies, I dont' have time to dig in, but this might help: http://www.sqlservercentral.com/articles/Dynamic+SQL/131473/

  • Tallboy - Friday, August 24, 2018 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

    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

  • Instead of recursion here

     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)
      )


    use a tally table function for dates:

    /* 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
    (
    WITH n1 AS (SELECT n FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))t(n))--10
          , n2 AS (SELECT n.n FROM n1 AS n CROSS JOIN n1)--100
          , n3 AS (SELECT n.n FROM n2 AS n CROSS JOIN n2)--10000
          , nums AS (SELECT 0 num UNION ALL SELECT Row_Number() OVER (ORDER BY(SELECT NULL)) num FROM n3) 
         , Calendar (BegDt, EndDt) AS (
                      SELECT
                            DateAdd(DAY, nums.num, @BegDate)
                          , DateAdd(MILLISECOND, -3, DateAdd(DAY, 1, DateAdd(week, nums.num,@BegDate)))
                      FROM nums
                      )
       SELECT
            c.BegDt
          , c.EndDt
       FROM
          Calendar c
       WHERE
          c.BegDt < @EndDate
    )
    GO
  • 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

  • You should read Jeff Moden's article about Tally Tables. They're super useful once you understand how to use them

  • 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

  • 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

  • Tallboy - Monday, August 27, 2018 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

    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 ...

  • Tallboy - Monday, August 27, 2018 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

    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