这是如何的数据看起来像。 这是一个很长的表
我需要计算的就业人数的一天
如何写SQL服务器的逻辑得到这样的结果? 我。创建一个日期表和随后的加入,但这引起了一个错误,因为该表是太大。 我需要一个递归的逻辑?
为未来的问题,不要发布图像的数据。 相反,使用服务,如 dbfiddle. 我无论如何增加一个素描为一个答案,与一个更好的准备的问题,你可以得到完整的答案。 无论如何,这就是:
-- extrema is the least and the greatest date in staff table
with extrema(mn, mx) as (
select least(min(hired),min(retired)) as mn
, greatest(max(hired),max(retired)) as mx
from staff
), calendar (dt) as (
-- we construct a calendar with every date between extreme values
select mn from extrema
union all
select dateadd(day, 1, d)
from calendar
where dt < (select mx from extrema)
)
-- finally we can count the number of employed people for each such date
select dt, count(1)
from calendar c
join staff s
on c.dt between s.hired and s.retired
group by dt;
如果你发现自己做这样的计算通常,这是一个很好的想法,以创建一个日历表。 你可以添加其他属性如,如果它是一个天在一周的中间等。
有约束:
CHECK(hired <= retired)
第一部分可以简化:
with extrema(mn, mx) as (
select min(hired) as mn
, max(retired) as mx
from staff
),
假设目前的员工有一个空的退休日期
Declare @Date1 date = '2015-01-01'
Declare @Date2 date = getdate()
Select A.Date
,HeadCount = count(B.name)
From ( Select Top (DateDiff(DAY,@Date1,@Date2)+1)
Date=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),@Date1)
From master..spt_values n1,master..spt_values n2
) A
Left Join YourTable B on A.Date >= B.Hired and A.Date <= coalesce(B.Retired,getdate())
Group BY A.Date
你需要一个日历表。 你开始的日历,并留下加入其他的一切,使用 BETWEEN
逻辑。
你可以使用一个真正表。 或者你可以产生它在飞行中,像这样:
WITH
L0 AS ( SELECT c = 1
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),
(1),(1),(1),(1),(1),(1),(1),(1)) AS D(c) ),
L1 AS ( SELECT c = 1 FROM L0 A, L0 B, L0 C, L0 D ),
Nums AS ( SELECT rownum = ROW_NUMBER() OVER(ORDER BY (SELECT 1))
FROM L1 ),
Dates AS (
SELECT TOP (DATEDIFF(day, '20141231', GETDATE()))
Date = DATEADD(day, rownum, '20141231')
FROM Nums
)
SELECT
d.Date,
NumEmployed = COUNT(*)
FROM Dates d
JOIN YourTable t ON d.Date BETWEEN t.Hired AND t.Retired
GROUP BY
d.Date;
如果你的日期有时间组成部分,那么你需要使用 >= AND <
逻辑
尝试限制范围内的日期表。 在这个例子我有一个表格的日期名为TallyStickDT.
SELECT dt, COUNT(name)
FROM (
SELECT dt
FROM tallystickdt
WHERE dt >= (SELECT MIN(hired) FROM #employees)
AND dt <= GETDATE()
) A
LEFT OUTER JOIN #employees E ON A.dt >= E.Hired AND A.dt <= e.retired
GROUP BY dt
ORDER BY dt