在SQL中使用CTE创建表

Question

我有一个查询，在哪里可以使用CTE创建表？因为我无法在数据库中创建任何新表。我正在计算工作日和经过的工作日数，不包括节假日和周末。但是这个创建表不起作用。如果有办法的话？由于这个创建表，我没有得到任何结果。

create table holidays(holiday date)
insert holidays values ('11-22-2018'),('11-23-2018')
;with dates as(
select dateadd(d,-day(getdate())+1,convert(date,getdate())) as startofmonth,
dateadd(d,-1,dateadd(m,1,dateadd(d,-day(getdate())+1,convert(date,getdate())))) as endofmonth,
convert(date,getdate()) as today
)
,holidaycount as (
select count(*) as holidaysinmonth,
sum(case when holiday<=today then 1 else 0 end) as holidaystodate
from dates
join holidays on holiday between startofmonth and endofmonth
)
,daycounts as(
select dates.*,
(DATEDIFF(dd, startofmonth, endofmonth) + 1)
-(DATEDIFF(wk, startofmonth, endofmonth) * 2)
-(CASE WHEN DATENAME(dw, startofmonth) = 'Sunday' THEN 1 ELSE 0 END) 
-(CASE WHEN DATENAME(dw, endofmonth) = 'Saturday' THEN 1 ELSE 0 END)
-isnull(holidaysinmonth,0) as wkdaysinmonth,
(DATEDIFF(dd, startofmonth, today) + 1)
-(DATEDIFF(wk, startofmonth, today) * 2)
-(CASE WHEN DATENAME(dw, startofmonth) = 'Sunday' THEN 1 ELSE 0 END) 
-(CASE WHEN DATENAME(dw, today) = 'Saturday' THEN 1 ELSE 0 END)
-isnull(holidaystodate,0) as wkdaystodate
from dates
cross join holidaycount
) 
select * from daycounts 

谢谢。

Answer 1

使用表变量：

declare @holidays table (holiday date)
insert @holidays values ('11-22-2018'),('11-23-2018')
;with dates as(
select dateadd(d,-day(getdate())+1,convert(date,getdate())) as startofmonth,
dateadd(d,-1,dateadd(m,1,dateadd(d,-day(getdate())+1,convert(date,getdate())))) as endofmonth,
convert(date,getdate()) as today
)
,holidaycount as (
select count(*) as holidaysinmonth,
sum(case when holiday<=today then 1 else 0 end) as holidaystodate
from dates
join @holidays on holiday between startofmonth and endofmonth
)

使用cte:

;with dates as(
select dateadd(d,-day(getdate())+1,convert(date,getdate())) as startofmonth,
dateadd(d,-1,dateadd(m,1,dateadd(d,-day(getdate())+1,convert(date,getdate())))) as endofmonth,
convert(date,getdate()) as today
), holidays as (
select holiday
from (values ('11-22-2018'),('11-23-2018') ) x (holiday)
)
,holidaycount as (
select count(*) as holidaysinmonth,
sum(case when holiday<=today then 1 else 0 end) as holidaystodate
from dates
join holidays on holiday between startofmonth and endofmonth
)

注意日期文字，假设数据库将接受dd-mm-yyyy字符串作为日期。这可能并不总是正确的，所以，请使用YYYYMMDD，SQL Server将其理解为日期，或者其次是YYYY-MM-DD。