Crosstab Query

  • 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!

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

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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 '('.

  • 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/

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

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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);

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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 !

  • Anyone ???

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


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply