使用视图填充行 [英] Populate rows using view
本文介绍了使用视图填充行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
您是
我有一张名为"员工"的表格喜欢第一次拍我想编写一个查询(视图定义),以便它可以像snap2一样提供输出。在snap2中,除了周五和周六之外,我每天都会出现1次。如果是星期五或星期六那么0。
Hi
I have one table named "Employee" like first snap. I want to write a query (view definition) so it can provide me the output like snap2. In snap2, I am showing up 1 for day other than Friday and Saturday. If day is Friday or Saturday then 0.
Aniruddha http://aniruddhathengadi.blogspot.com/
Aniruddha http://aniruddhathengadi.blogspot.com/
推荐答案
create table #t(id int,dt1 date,dt2 date)
插入#t values(100,'20190101','20190201')
插入#t值(200,'20190401','20190430')
with cteCalendar
AS(
SELECT
ID,
dt1,
dt2,
情况,当 datepart(dw,dt1)在(7,1)中,然后0在其他1结束日期
FROM #t
其中 dt1< dt2
union all
选择
id,
dateadd(day,1,dt1)as dt1,
dt2,
大小写 datepart(dw,dateadd(day,1,dt1))in(7,1)then 0 else 1 end dayval
from cteCalendar
WHERE dt1< dt2和cteCalendar.id = id
)
SELECT id,dt1为startdate,dt1为enddate,dayval
FROM cteCalendar ORDER BY id
OPTION (MAXRECURSION 0);
create table #t (id int,dt1 date,dt2 date)
insert into #t values (100,'20190101','20190201')
insert into #t values (200,'20190401','20190430')
with cteCalendar
AS (
SELECT ID,
dt1,
dt2,
case when datepart(dw,dt1) in (7,1) then 0 else 1 end dayval
FROM #t
where dt1 < dt2
union all
select id,
dateadd(day, 1, dt1) as dt1,
dt2,
case when datepart(dw,dateadd(day, 1, dt1)) in (7,1) then 0 else 1 end dayval
from cteCalendar
WHERE dt1 < dt2 and cteCalendar.id=id
)
SELECT id, dt1 as startdate,dt1 as enddate,dayval
FROM cteCalendar ORDER BY id
OPTION (MAXRECURSION 0) ;
这篇关于使用视图填充行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文