27,579
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([住院号] int,[入院日期] Date,[出院日期] Date)
Insert #T
select 60089,'2018-3-2','2018-3-7' union all
select 65784,'2018-3-18','2018-3-25'
Go
--测试数据结束
SELECT
#T.住院号,
DATEADD(DAY, number, 入院日期) 费用日期
FROM
#T
JOIN
master..spt_values
ON number
BETWEEN 0 AND DATEDIFF(DAY, 入院日期, 出院日期)
WHERE
type = 'P';
with t1(住院号, 入院日期, 出院日期) as
(
select '60089', convert(datetime, '2018-3-2'), convert(datetime, '2018-3-7') union all
select '65784', convert(datetime, '2018-3-18'), convert(datetime, '2018-3-25')
)
select 住院号, t1.入院日期+number
from t1, (select number from master..spt_values where type='P') t2
where DATEDIFF(DAY, t1.入院日期, t1.出院日期)>=t2.number
;with cte(zyh,ryrq,cyrq) as
(select '60089','2018-03-02','2018-03-07' union
select '65784','2018-03-18','2018-03-25' )
select zyh,ryrq=CONVERT(varchar(100),ryrq, 120),cyrq=CONVERT(varchar(100),cyrq, 120) into #tmp from cte
select zyh,convert(char(10),DATEADD(dd,number,ryrq),111)
from master.dbo.spt_values a,#tmp b where type='p' AND number<=DATEDIFF(dd,ryrq,cyrq)
drop table #tmp
60089 2018/03/02
60089 2018/03/03
60089 2018/03/04
60089 2018/03/05
60089 2018/03/06
60089 2018/03/07
65784 2018/03/18
65784 2018/03/19
65784 2018/03/20
65784 2018/03/21
65784 2018/03/22
65784 2018/03/23
65784 2018/03/24
65784 2018/03/25