27,579
社区成员
发帖
与我相关
我的任务
分享出租房屋的空置周期写法，200分求解。
业务情况是这样, 一个出租房间的合同表 TB 中字段有: HTID 合同ID,STIME 合同开始日期,ETIME合同结束日期,FJID 房间ID
表中有很多房间的合同,一个房间可能存在多个合同,而且周期也可能重叠,如示列数据A-302房,也可能不重叠 如A-301房.
现要求出2018年1月份,这个表中所有房间的空置周期(房间没有租出去叫空置,在合同范围内的,则是在出租,不是空置)
注意,一个房间中的各个合同,他们是独立的,各自周期可能重叠.
实没想出怎么用SQL求出,求大神帮忙 !
附示列数据：
Create table tb (
HTID varchar(20),stime datetime,etime datetime,FJID varchar(20))
insert tb select '00001','2018-1-2','2018-1-8','A-301'
insert tb select '00002','2018-1-11','2018-2-25','A-301'
insert tb select '00005','2017-11-2','2018-1-9','A-302'
insert tb select '00008','2018-1-15','2018-4-8','A-302'
insert tb select '00009','2018-1-6','2018-1-13','A-302'
insert tb select '00011','2018-1-6','2018-3-20','A-303'
insert tb select '00018','2017-11-16','2018-1-28','A-304'
insert tb select '00019','2018-1-15','2018-1-26','A-306'
insert tb select '00021','2016-5-10','2019-1-11','A-307'
要求得出结果如下
A-301 的空置周期为 2018-01-01至2018-01-01,2018-01-09至2018-01-10
A-302 的空置周期为 2018-01-14至2018-01-14
A-303 的空置周期为 2018-01-01至2018-01-05
A-304 的空置周期为 2018-01-29至2018-01-31
A-306 的空置周期为 2018-01-01至2018-01-14,2018-01-27至2018-01-31
A-307 的空置周期为 空
...全文
请发表友善的回复…
发表回复
zhaoanle 2018-05-31
- 打赏
- 举报
强！
zhaoanle 2018-05-31
- 打赏
- 举报
牛
RINK_1 2018-05-14
- 打赏
- 举报
declare @startdate date,@enddate date
set @startdate='2018-01-01'
set @enddate='2018-01-31'
with cte_1
as
(select A.*,dateadd(day,B.number,stime) as in_use
from tb A
join master.dbo.spt_values B on DATEDIFF(DAY,stime,etime)>=number
where type='P'),
cte_2
as
(select *
from (select distinct FJID from tb) as A join
(select DATEADD(DAY,number,@startdate) as schedule,number as seq_1
from master.dbo.spt_values
where type='p' and number<=DATEDIFF(DAY,@startdate,@enddate)) as B on 1=1),
cte_3
as
(select A.*
from cte_2 A
left join (select FJID,
in_use
from cte_1
where in_use between @startdate and @enddate) as B on A.FJID=B.FJID and A.schedule=B.in_use
where B.in_use is null)
select FJID,MIN(schedule) as min_vacant,MAX(schedule) as max_vacant
from
(select *,ROW_NUMBER() over (partition by FJID ORDER BY schedule) as seq_2 from cte_3) as A
group by FJID,seq_1-seq_2
order by FJID,min_vacant
文盲老顾 2018-05-14
- 打赏
- 举报
-- 最终指令稍微变形一下
-- 获取 2018-1-28到2018-2-1完全空闲的房间，并获得该房间前一个占用的最后日期和后一个占用的开始日期
select *
from t6 a
cross apply(
select (select max(dt) from t4 where fjid=a.fjid and dt<'2018-1-28') as prov_time
,(select min(dt) from t4 where fjid=a.fjid and dt>'2018-2-1') as next_time
)b
where fjid not in (
select fjid from t5
unpivot(
htid for fjid in ([a-301],[a-302],[a-303],[a-304],[a-305],[a-306],[a-307])
) u
where dt between '2018-1-28' and '2018-2-1'
)文盲老顾 2018-05-14
- 打赏
- 举报
with t as ( -- 原始数据
select '00001' as HTID,'2018-1-2' as stime,'2018-1-8' as etime,'A-301' as FJID
union all select '00002','2018-1-11','2018-2-25','A-301'
union all select '00005','2017-11-2','2018-1-9','A-302'
union all select '00008','2018-1-15','2018-4-8','A-302'
union all select '00009','2018-1-6','2018-1-13','A-302'
union all select '00011','2018-1-6','2018-3-20','A-303'
union all select '00018','2017-11-16','2018-1-28','A-304'
union all select '00019','2018-1-15','2018-1-26','A-306'
union all select '00021','2016-5-10','2019-1-11','A-307'
),t1 as ( -- 转换出已占用日期
select a.*,number,convert(date,dateadd(d,number,stime)) as used_time from t a
cross apply(
select number from master..spt_values where type='p'
and number<=datediff(d,stime,etime)
) b
),t2 as ( -- 获取最小日期和最大日期，以及两日期天数差
select min(used_time) as min_time,max(used_time) as max_time,datediff(d,min(used_time),max(used_time)) as passed from t1
),t3 as ( -- 根据最小日期和天数差获取完整的日期表
select min_time,dateadd(d,number,min_time) as dt
from t2 a
cross apply(
select number from master..spt_values where type='p' and number<=a.passed
) b
),t4 as ( -- 根据日期表和已占用日期转换占用日期表
select dt,htid,fjid from t3 a
left join t1 b
on a.dt=b.used_time
),t5 as ( -- 根据日期表和占用日期表得到房间信息占用情况及每个占用时的合同号，该表已可以作为最终结果使用
select * from t4
pivot(max(htid) for fjid in ([A-301],[A-302],[A-303],[A-304],[A-305],[A-306],[A-307])) p
),t6 as ( -- 房间列表
select 'A-301' as fjid
union all
select 'A-302'
union all
select 'A-303'
union all
select 'A-304'
union all
select 'A-305'
union all
select 'A-306'
union all
select 'A-307'
)
--select * from t5
-- 获取2018-1-1到2018-2-1完全空闲的房间
select * from t6 where fjid not in (
select fjid from t5
unpivot(
htid for fjid in ([a-301],[a-302],[a-303],[a-304],[a-305],[a-306],[a-307])
) u
where dt between '2018-1-1' and '2018-2-1'
)
--order by dt,fjid,htid
shinger126 2018-05-14
- 打赏
- 举报
;WITH dl AS (SELECT DISTINCT fjid, DATEADD(DAY,number,'2018-01-01') date_list FROM master..spt_values,dbo.tb WHERE type='P' AND DATEADD(DAY,number,'2018-01-01')<='2018-01-31'),
t AS (SELECT FJID,DATEADD(DAY,a.number,stime) date_cz FROM tb,master..spt_values a WHERE a.type='P' AND DATEADD(DAY,a.number,tb.stime)<=tb.etime
AND DATEADD(DAY,a.number,tb.stime)>='2018-01-01' AND DATEADD(DAY,a.number,tb.stime)<='2018-01-31')
SELECT * FROM dl where NOT EXISTS(SELECT 1 FROM t WHERE t.FJID=dl.FJID AND t.date_cz=dl.date_list) ORDER BY dl.FJID,dl.date_list
给你写了个语句得到空置房间的日期列表，因为要开会了，没时间。剩余的很简单了，你自己弄吧
shinger126 2018-05-14
- 打赏
- 举报
;WITH dl AS (SELECT DISTINCT fjid, DATEADD(DAY,number,'2018-01-01') date_list FROM master..spt_values,dbo.tb WHERE type='P' AND DATEADD(DAY,number,'2018-01-01')<='2018-01-31'),
t AS (SELECT FJID,DATEADD(DAY,a.number,stime) date_cz FROM tb,master..spt_values a WHERE a.type='P' AND DATEADD(DAY,a.number,tb.stime)<=tb.etime
AND DATEADD(DAY,a.number,tb.stime)>='2018-01-01' AND DATEADD(DAY,a.number,tb.stime)<='2018-01-31'),
m AS (SELECT * FROM dl WHERE NOT EXISTS(SELECT 1 FROM t WHERE dl.FJID=t.FJID AND dl.date_list=t.date_cz)),
s AS (
SELECT *,DAY(date_list) a1,ROW_NUMBER() OVER(PARTITION BY fjid ORDER BY date_list) a2
FROM m --where NOT EXISTS(SELECT 1 FROM t WHERE t.FJID=dl.FJID AND t.date_cz=dl.date_list)
),e AS (
SELECT fjid,MIN(s.date_list) minrq,MAX(s.date_list) maxrq FROM s GROUP BY fjid,a1-a2 )
SELECT fjid,空置日期=STUFF((SELECT ','+CONVERT(VARCHAR(10),minrq,120)+'至'+CONVERT(VARCHAR(10),maxrq,120) FROM e WHERE e.FJID=dl.fjid ORDER BY e.minrq FOR XML PATH('') ),1,1,'') FROM dl
GROUP BY dl.FJID
fjid 空置日期
A-301 2018-01-01至2018-01-01,2018-01-09至2018-01-10
A-302 2018-01-14至2018-01-14
A-303 2018-01-01至2018-01-05
A-304 2018-01-29至2018-01-31
A-306 2018-01-01至2018-01-14,2018-01-27至2018-01-31
A-307 NULL
42205413 2018-05-14
- 打赏
- 举报
declare @sdate datetime = '2018-01-01',
@edate datetime = '2018-01-31'
;
with a as (
select row_number() over (order by object_id ) rowdate
from sys.all_objects
), a0 as (
select fjid
from #tb
group by fjid
)
, b as (
select
fjid ,
convert(nvarchar(20) ,@sdate+rowdate -1,23) dt ,
rowdate ,
row_number() over (partition by fjid order by fjid ,rowdate) rowid
from a0
inner join a on 1 =1
where not exists (
select 1
from #tb t1
where t1.FJID = a0.FJID
and t1.stime <=convert(nvarchar(20) ,@sdate+rowdate -1,23)
and t1.etime >= convert(nvarchar(20) ,@sdate+rowdate -1,23)
)
and @sdate+rowdate -1 <= @edate
group by fjid ,rowdate
) , c as
(
select
fjid ,dt ,rowid ,rowdate,
cast(dt+'至'+ dt as nvarchar(max) ) remarks
from b
where rowid =1
union all
select c.FJID ,b.dt ,b.rowid , b.rowdate,
case when c.rowdate +1 = b.rowdate
then left(remarks,len(remarks)-10) + b.dt
else remarks + ',' + b.dt + '至'+b.dt
end
from c
inner join b on b.rowid = c.rowid +1
and b.FJID = c.FJID
)
select a0.fjid ,max(remarks ) remarks
from a0
left join c on c.FJID = a0.FJID
group by a0.FJID
