0

I have a table like this:

 ID DepartmentID    ShiftID Date    EmployeeID
 3198   9   2   2018-11-12  10011300
 3234   9   1   2018-11-12  43354479
 3235   8   1   2018-11-13  43354479
 3199   9   2   2018-11-13  10011300
 3200   9   2   2018-11-14  10011300
 3236   8   1   2018-11-14  43354479
 3237   8   1   2018-11-15  43354479
 3201   9   2   2018-11-15  10011300
 3202   9   2   2018-11-16  10011300
 3238   8   1   2018-11-16  43354479
 3239   9   2   2018-11-19  43354479
 3203   9   3   2018-11-19  10011300
 4274   17  0   2018-11-19  10045079
 4275   8   1   2018-11-19  43354479
 4276   9   3   2018-11-19  43354479
 4277   8   2   2018-11-19  43354341
 3270   17  0   2018-11-19  10026106
 3273   9   2   2018-11-19  10004723
 3271   0   1   2018-11-20  10026106
 4308   9   3   2018-11-20  10045079
 4278   1   2   2018-11-20  43354341
 3204   9   2   2018-11-20  10011300

And I want to create a SP with the following output according to inputs parameters:
@StartDate Date,
@EndDate Date

 EmplyeeID   10-11-2018  11-11-2018  12-11-2018 13-11-2018 14-11-2018 etc...
 10011300         -          -           X          X          X
 43354479         -          -           X          X          X
 etc....

How can I do that?

Ilyes
  • 14,640
  • 4
  • 29
  • 55
Peter Hansen
  • 81
  • 1
  • 8
  • Why you need an `SP` instead of a `TVF`? any reason? Also what did you try? – Ilyes Dec 12 '18 at 14:01
  • 2
    This will require some painful dynamic SQL because of the different number of output columns depending on the input parameters. If you have any other (client) language available to do this in other than T-SQL, consider using that. This output looks like the kind of presentation layer stuff that doesn't belong in the database. – Jeroen Mostert Dec 12 '18 at 14:03
  • It can also be a TVF.... – Peter Hansen Dec 12 '18 at 14:03
  • I have already maked this in my presentation layer C#, but think it could be faster and cleaner to have it in the SQL server. – Peter Hansen Dec 12 '18 at 14:05
  • This would need either a dynamic pivot or a dynamic crosstab. Something like this. https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query/10404455#10404455 – Sean Lange Dec 12 '18 at 14:13
  • 2
    Faster and cleaner -- doubtful, unless your C# code is written poorly. The data in the original form is already compact (compared to having many empty columns), all you have to do in the DB layer is filter it by date. T-SQL gets awkward when the output is dynamic (for the same reason, this can't be done in a TVF -- only stored procedures can vary their result sets). It can be done, certainly, it just never improves things unless it's the only option (i.e. there is no client code except some really simple code that can only present query results as-is). – Jeroen Mostert Dec 12 '18 at 14:16

1 Answers1

2

Sample data

IF OBJECT_ID('tempdb..#t')IS NOT NULL
DROP TABLE #T
;WITH CTE(
ID, DepartmentID ,   ShiftID, [Date], EmployeeID)
AS
(
SELECT  3198,  9 , 2 ,'2018-11-12',  10011300 UNION ALL
SELECT  3234,  9 , 1 ,'2018-11-12',  43354479 UNION ALL
SELECT  3235,  8 , 1 ,'2018-11-13',  43354479 UNION ALL
SELECT  3199,  9 , 2 ,'2018-11-13',  10011300 UNION ALL
SELECT  3200,  9 , 2 ,'2018-11-14',  10011300 UNION ALL
SELECT  3236,  8 , 1 ,'2018-11-14',  43354479 UNION ALL
SELECT  3237,  8 , 1 ,'2018-11-15',  43354479 UNION ALL
SELECT  3201,  9 , 2 ,'2018-11-15',  10011300 UNION ALL
SELECT  3202,  9 , 2 ,'2018-11-16',  10011300 UNION ALL
SELECT  3238,  8 , 1 ,'2018-11-16',  43354479 UNION ALL
SELECT  3239,  9 , 2 ,'2018-11-19',  43354479 UNION ALL
SELECT  3203,  9 , 3 ,'2018-11-19',  10011300 UNION ALL
SELECT  4274,  17, 0 ,'2018-11-19',  10045079 UNION ALL
SELECT  4275,  8 , 1 ,'2018-11-19',  43354479 UNION ALL
SELECT  4276,  9 , 3 ,'2018-11-19',  43354479 UNION ALL
SELECT  4277,  8 , 2 ,'2018-11-19',  43354341 UNION ALL
SELECT  3270,  17, 0 ,'2018-11-19',  10026106 UNION ALL
SELECT  3273,  9 , 2 ,'2018-11-19',  10004723 UNION ALL
SELECT  3271,  0 , 1 ,'2018-11-20',  10026106 UNION ALL
SELECT  4308,  9 , 3 ,'2018-11-20',  10045079 UNION ALL
SELECT  4278,  1 , 2 ,'2018-11-20',  43354341 UNION ALL
SELECT  3204,  9 , 2 ,'2018-11-20',  10011300   
)
SELECT * ,'X' AS Attended INTO #T FROM CTE
SELECT * FROM #T

Try this dynamic sql

  DECLARE @Sql nvarchar(max),@Col nvarchar(max),@IsnullCol nvarchar(max)


    SELECT  @Col=STUFF((SELECT  DISTINCT ', '+QUOTENAME ([Date]) FROM #T  FOR XML PATH ('')),1,1,'')
    SELECT @IsnullCol=STUFF((SELECT  DISTINCT ', '+'ISNULL(MAX('+QUOTENAME([Date])+'),'''') AS ' +QUOTENAME([Date]) FROM #T  FOR XML PATH ('')),1,1,'')
    SET @Sql ='
    SELECT EmployeeID,'+@IsnullCol+' FROM
    (
    SELECT * FROM #T
    ) AS SRC
    PIVOT
    (
    MAX(Attended) FOR [Date] IN ('+@Col+')
    ) AS Pvt
    GROUP BY EmployeeID
    ORDER BY EmployeeID'

    Print @Sql
    EXEC (@Sql)
Sreenu131
  • 2,476
  • 1
  • 7
  • 18