SQL查询-根据日期范围收集数据-可能的可变列数 [英] SQL Query - gather data based on date range - possible variable number of columns

问题描述

请原谅我的经验不足，我希望这不是一个愚蠢的问题，我陷入困境，无处可寻.我将重点放在这里:

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:

我遇到的问题是可变的列数.我将获得一个日期范围，并且需要返回给定范围内每一天的出勤记录，如果没有数据，则返回一个空值.我将WebAPI用作中间层，因此可以执行进一步的数据操作以达到此结果.

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.

我的表格如下:

我不能成为第一个需要完成此操作的人，任何文章/帖子或任何可以帮助我完成此操作的内容吗?即使是伪代码也会有所帮助.任何东西！

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!

感谢上百万！

这是我能想到的，但是我什至不确定它是否可行:

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

推荐答案

这很有趣.我认为这将满足您的需求.首次测试数据:

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'

然后进行动态查询:

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:

出于演示目的，您应该能够将列名转换为可显示的日期(只需从列名中解析YYYYMMDD).日期不能直接用作列名，因为它会产生无效的列名.

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示例此处.

SQL Fiddle example here.

这篇关于SQL查询-根据日期范围收集数据-可能的可变列数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！