Sql语句帮助

本文关键字：帮助 语句 Sql | 更新日期: 2023-09-27 18:07:07

我在asp.net上的Gridview工作。我在SQL Server中有一个表，其中列名为"姓"answers"日期"(DateTime)和持续时间。该表用于休假请求。我如何构造一个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

任何想法如何接近它，并产生一个网格视图与这些数据?感谢您的宝贵时间

Sql语句帮助

您可以使用数字表来完成此操作。这里我使用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

下面应该给你一个所有假期的分类，从第一天预订到最后一天未预订(不仅仅是开始日期)。它还应该报告零预订范围内的日期(如果存在);

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

你可以试试这样

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]

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

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

我喜欢鲍勃回答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

如果您将此插入到他的答案中，并创建一个接受'start'和'end参数的存储过程，您应该得到您的答案。

相关文章：