SQL语句帮助

Question

我正在 ASP .Net 上开发 GridView，我的 SQL Server 中有一个表格，其列名为 "surname" 和 "Date"（DateTime）以及 "duration"。该表用于休假申请。如何构建 SQL 语句以查看每天将有多少人缺席？问题在于，查询 SELECT [Date], COUNT(DISTINCT surname) GROUP BY [Date] 不会向我展示实际上 9 月 2 日将有 8 个人缺席的情况。例如，给定以下数据：

surname Date       Duration
------- ---------- ---------
Bertram 2011-09-01     3
Coulois 2011-09-01     5
LeBlanc 2011-09-01     6
Fosters 2011-09-01     3
Blanche 2011-09-01     2
Bertram 2011-09-02     6
Gillian 2011-09-02     4
Pikklar 2011-09-02     7
Thierry 2011-09-03     6
Selanne 2011-09-03     6

我希望得到以下结果：

Date  Count
----- -----
1 Sep     5
2 Sep     8    
3 Sep     10

有没有想法如何处理它并生成一个包含这些数据的网格视图？感谢您的时间。

- pikk

---

持续时间是否包括周末？ - Andrey

是的，它包括周末。 - pikk

实际日期是假期的开始日期，对吗？我看你似乎没有每天离开的日期，所以我们需要进行一些日期计算才能显示正确的日期。 - Jennifer S

确切地说，我并没有每一天的日期。是的，但我应该在哪里进行计算，是在SQL语句内还是GridView中？你有什么想法如何处理它吗？ - pikk

@Ben Robinson 嗯，我对此不够了解。你能否给我更多细节信息？ - pikk

Answer 1

你可以使用一个数字表来完成这项操作。在这里，我使用 master..spt_values。

declare @T table
(
  surname varchar(20),
  [Date] datetime,
  Duration int
)

insert into @T values
('Bertram', '2011-09-01',     3),
('Coulois', '2011-09-01',     5),
('LeBlanc', '2011-09-01',     6),
('Fosters', '2011-09-01',     3),
('Blanche', '2011-09-01',     2),
('Bertram', '2011-09-02',     6),
('Gillian', '2011-09-02',     4),
('Pikklar', '2011-09-02',     7),
('Thierry', '2011-09-03',     6),
('Selanne', '2011-09-03',     6)


select dateadd(day, N.number, [Date]) as [Date],
       count(*) as [Count]
from @T as T
  inner join master..spt_values as N
    on N.number between 0 and T.Duration
where N.type = 'P'    
group by dateadd(day, N.number, [Date])
order by dateadd(day, N.number, [Date])

结果：

Date                    Count
----------------------- -----------
2011-09-01 00:00:00.000 5
2011-09-02 00:00:00.000 8
2011-09-03 00:00:00.000 10
2011-09-04 00:00:00.000 9
2011-09-05 00:00:00.000 7
2011-09-06 00:00:00.000 7
2011-09-07 00:00:00.000 5
2011-09-08 00:00:00.000 4
2011-09-09 00:00:00.000 3

- Mikael Eriksson

Answer 2

您可以尝试像这样的方式。

WITH Dates AS
    (SELECT CAST('9/1/2011' AS DATE) AS [DATE]
    UNION SELECT '9/2/2011'
    UNION SELECT '9/3/2011'
    )

SELECT [DATE], SUM(OnVacation) AS COUNT
FROM
    (
    SELECT [DATE], 
        CASE WHEN [DATE] BETWEEN StartDate AND DATEADD(dd, Duration, StartDate) 
             THEN 1 ELSE 0 END AS OnVacation
    FROM Vacations
    CROSS JOIN Dates
    ) x
GROUP BY [DATE]
ORDER BY [DATE]

日期表是一个包含您想要查看日期列表的表格。在这个查询中，它是公共表达式（CTE）。

同时假设开始日期是持续时间的第一天。

- bobs

---

是的，但为了查看一年中的所有日期，我必须将整个年份的所有可用日期转换。 - pikk

我建议您创建一个带有日期的表格，或者创建一个更动态的CTE。 - bobs

Answer 3

以下是所有假期的细节，包括第一天和最后一天（不仅仅是起始日期）。如果在这个范围内有零预订的日期（如果有），也应该报告出来。

2011-09-01  5
2011-09-02  8
2011-09-03  10
2011-09-04  9
2011-09-05  7
2011-09-06  7
2011-09-07  5
2011-09-08  4
2011-09-09  3

该代码计算出最后预订日期，然后计算动态日期范围内每天的所有预订。

DECLARE @MaxDate date
SELECT  @MaxDate = max(dateAdd(day, duration, date)) 
FROM holiday;

WITH HolidayDates (holidayDate)
as
(
    SELECT  MIN(date) holidayDate 
    FROM    holiday 
    UNION ALL 
    SELECT  DateAdd(day, 1, holidayDate)
    FROM    holidayDates 
    WHERE   holidayDate <@MaxDate
)

SELECT      cast(hd.holidayDate as date) holidayDate
            , count(h.surname) PeopleOnHoliday
FROM        HolidayDates hd
LEFT JOIN   holiday h on hd.holidayDate between h.date AND dateAdd(day, duration, date) 
GROUP BY    hd.holidayDate
ORDER BY    hd.holidayDate

希望这能帮到您...

- Daryl Wenman-Bateson

Answer 4

我喜欢Bob的答案 SQL语句帮助。

这里是CTE，您需要创建一个日期列，给定开始和结束日期。

DECLARE @start datetime = '2011-01-01'
DECLARE @end datetime = '2011-01-31'

; WITH Dates as
(
    SELECT @start as d
    UNION ALL 
    SELECT DATEADD(DAY, 1, d)FROM dates WHERE d < @end
)
SELECT * FROM Dates

如果您将此内容插入到他的答案中，并创建一个接受“开始”和“结束”参数的存储过程，那么您应该就有了答案。

- Kirk Broadhurst