SQL Query - gather data based on date range - poss

Question

please forgive my inexperience, I hope this isn't too dumb of a question, I'm stuck and have no where else to turn. I'll keep it to the point:

I'm trying to gather payroll data with the results like so:

The issue I have is the variable number of columns. I will be given a date range and are required to return an attendance record for each day in the given range, or a null value if no data is present. I'm using WebAPI as middle tier so I have the ability to perform further data manipulation to achieve this result.

My tables are as follows:

I can't be the first person who needs this done, any articles/posts or anything that would help me accomplish this? Even pseudo code would help; anything!

Thanks a million in advnace!

This is what I've been able to come up with but I'm not even sure if its doable:

-- convert date range into days of month 
-- to ensure null values are included in data??
DECLARE @intFlag INT = 0;
DECLARE @numberOfDays INT = DATEDIFF(DAY, @startDate, @endDate);
DECLARE @TMP TABLE (DaysOfMonth date)

WHILE (@intFlag <= @numberOfDays)
BEGIN
    INSERT INTO @TMP VALUES (DATEADD(DAY, @intFlag, @startDate));
    SET @intFlag = @intFlag + 1
END

-- select days in given data range so c# app can build header row
-- would it help if I pivot this data?
SELECT
    DaysOfMonth
FROM
    @TMP
ORDER BY
    DaysOfMonth

-- get a count for number of people
DECLARE @count INT = 0;
DECLARE @TMPPPL TABLE (Id int identity(1,0), PId Int)

INSERT INTO 
    @TMPPPL
SELECT 
    p.PersonId
FROM 
    dbo.People p
JOIN 
    dbo.UserTypes ut on p.UserType_UserTypeId = ut.UserTypeId and (ut.Code = 'caregiver' or ut.Code = 'director')

DECLARE @numberOfPeople INT = (SELECT COUNT(1) FROM @TMPPPL)

-- create and execute sproc to return row of data for each person
WHILE (@count <= @numberOfPeople)
BEGIN

    -- STUCK HERE, This obviously won't work but what else can I do?
    EXEC GetPersonAttendanceHours @personId, @startDate, @endDate;

    SET @count = @count + 1
END

Answer 1

This is a variation on a theme that I've done in order to display schedules or attendance. I expect something similar should work with your report. Here is the beginning of your stored procedure:

DECLARE @iDay INT = 0;
DECLARE @countDays INT = DATEDIFF(DAY, @startDate, @endDate);
DECLARE @tempDates TABLE ([tempDate] DATE);
DECLARE @filterDates NVARCHAR;
WHILE (@iDay <= @countDays)
BEGIN
  INSERT INTO @tempDates VALUES (DATEADD(DAY, @iDay, @startDate));
  SET @iDay = @iDay + 1;
END;
SELECT @filterDates = STUFF(
  (SELECT N''',''' + CONVERT(NVARCHAR, [tempDate], 103) FROM @tempDates FOR XML PATH('')),
  1,
  2,
  ''  
);

You were on the right track with your suggestion. The next query gets your data before you PIVOT it.

SELECT [People].[Person_PersonID], [tempDates].[tempDate], [Attendances].[SignIn], [Attendances].[SignOut],
  MIN([Attendances].[SignOut], DATEADD(DAY, 1, [tempDates].[tempDate]))
  - MAX([Attendances].[SignIn], [tempDates].[tempDate]) * 24 AS [numHours]
FROM [People]
CROSS JOIN @tempDates [tempDates]
LEFT JOIN [Attendances]
  ON (
    ([Attendances].[SignIn] < DATEADD(DAY, 1, [tempDates].[tempDate]))
    AND ([Attendances].[SignOut] > [tempDates].[tempDate])
  );

Once we're satisfied with the results of the previous query, we substitute it with a query using PIVOT, which should look something like this.

SELECT *
FROM (
  SELECT [People].[PersonID], [tempDates].[tempDate], [Attendances].[SignIn], [Attendances].[SignOut],
    MIN([Attendances].[SignOut], DATEADD(DAY, 1, [tempDates].[tempDate]))
    - MAX([Attendances].[SignIn], [tempDates].[tempDate]) * 24 AS [numHours]
  FROM [People]
  CROSS JOIN @tempDates [tempDates]
  LEFT JOIN [Attendances]
    ON (
      ([Attendances].[SignIn] < DATEADD(DAY, 1, [tempDates].[tempDate]))
      AND ([Attendances].[SignOut] > [tempDates].[tempDate])
    )
) AS [DatedAttendance]
PIVOT (
  SUM([numHours]) FOR ([tempDate] IN (@filterDates))
) AS [PivotAttendance]
ORDER BY [PersonID]

Answer 2

This was interesting. I think this will do what you're looking for. First test data:

CREATE TABLE people (PersonID int, Name varchar(30))

INSERT INTO people (PersonID, Name)
SELECT 1, 'Kelly'
UNION ALL SELECT 2, 'Dave'
UNION ALL SELECT 3, 'Mike'

CREATE TABLE attendances (PersonID int, SignIn datetime, SignOut datetime)

INSERT INTO attendances (PersonID, SignIn, SignOut)
SELECT 1, '1-Feb-2015 08:00', '1-Feb-2015 09:00'
UNION ALL SELECT 1, '1-Feb-2015 12:00', '1-Feb-2015 12:30'
UNION ALL SELECT 2, '2-Feb-2015 08:00', '2-Feb-2015 08:15'
UNION ALL SELECT 1, '3-Feb-2015 08:00', '3-Feb-2015 09:00'
UNION ALL SELECT 1, '4-Feb-2015 08:00', '4-Feb-2015 08:30'
UNION ALL SELECT 2, '4-Feb-2015 08:00', '4-Feb-2015 10:00'
UNION ALL SELECT 2, '6-Feb-2015 12:00', '6-Feb-2015 15:00'
UNION ALL SELECT 3, '6-Feb-2015 15:00', '6-Feb-2015 17:00'
UNION ALL SELECT 3, '8-Feb-2015 10:00', '8-Feb-2015 12:00'

Then a dynamic query:

DECLARE @startDate DATETIME='1-Feb-2015'
DECLARE @endDate DATETIME='9-Feb-2015'
DECLARE @numberOfDays INT = DATEDIFF(DAY, @startDate, @endDate)

declare @dayColumns TABLE (delta int, colName varchar(12))

-- Produce 1 row for each day in the report. Note that this is limited by the 
-- number of objects in sysobjects (which is about 2000 so it's a high limit)
-- Each row contains a delta date offset, @startDate+delta gives each date to report 
-- which is converted to a valid SQL column name in the format colYYYYMMDD
INSERT INTO @dayColumns (delta, colName)
SELECT delta, 'col'+CONVERT(varchar(12),DATEADD(day,delta,@startDate),112) as colName from (
  select (ROW_NUMBER() OVER (ORDER BY sysobjects.id))-1 as delta FROM sysobjects 
) daysAhead
WHERE delta<=@numberOfDays

-- Create a comma seperated list of columns to report
DECLARE @cols AS NVARCHAR(MAX)= ''
SELECT @cols=CASE WHEN @cols='' THEN @cols ELSE @cols+',' END + colName FROM @dayColumns ORDER BY delta
DECLARE @totalHours AS NVARCHAR(MAX)= ''
SELECT @totalHours=CASE WHEN @totalHours='' THEN '' ELSE @totalHours+' + ' END + 'ISNULL(' + colName +',0)' FROM @dayColumns ORDER BY delta

-- Produce a SQL statement which outputs a variable number of pivoted columns
DECLARE @query AS NVARCHAR(MAX)
SELECT @query=
'declare @days TABLE (reportDay date, colName varchar(12))

INSERT INTO @days (reportDay, colName)
SELECT DATEADD(day,Delta,'''+CONVERT(varchar(22),@startDate,121)+'''), ''col''+CONVERT(varchar(12),DATEADD(day,delta,'''+CONVERT(varchar(22),@startDate,121)+'''),112) as colName from (
  select (ROW_NUMBER() OVER (ORDER BY sysobjects.id))-1 as Delta FROM sysobjects 
) daysAhead
WHERE Delta<='+CAST(@numberOfDays as varchar(10))+'

SELECT p.Name, pivotedAttendance.*,'+@totalHours+' as totalHours FROM (
  SELECT * FROM (
    select p.PersonID, d.colName, CAST(DATEDIFF(MINUTE, a.SignIn, a.SignOut)/60.0 as decimal(5,1)) as hrsAttendance 
    from @days d
    CROSS JOIN people p 
    LEFT OUTER JOIN attendances a ON a.PersonID=p.PersonID AND CAST(a.SignOut as DATE)=d.reportDay
  ) as s
  PIVOT (
    SUM(hrsAttendance) FOR colName in ('+@cols+')
  ) as pa
) as pivotedAttendance
INNER JOIN people p on p.PersonID=pivotedAttendance.PersonID'

-- Run the query
EXEC (@query)

Which produces data in a similar format to your example, with all of the days in the report range and a row for each person. From the above I see:

For presentation purposes you should be able to convert the column name to a display-able date (just parse the YYYYMMDD out of the column name). The date can't be used as the column name directly as it produces an invalid column name.

SQL Fiddle example here.