SQL-结果从工作日（时间）和周（无时间）开始

debugcn 发表于 Dev

小丑本人

我正在尝试解决问题。我需要从一个工作周中获取结果（例如，指定工作时间为上午7:00至8:00 pm），并且需要包括周末的结果（没有指定时间）。但我不知道如何在工作日解决问题并包括结果

select  
c.PatientId
,g.PatientId
,c.StartDate
,g.TransferOrdinalNumber

from ClinicalEvent C
join PatientIncomming G on C.PatientId=G.PatientId

where

(DATEPART(dw, StartDate) in (1,2,3,4,5))
and (
(CAST(StartDate as Time) between '20:00' and '23:59')
or 
(CAST(StartDate as Time) between '00:01' and '07:00')
)


and StartDate between '2020.02.01' and '2020.2.28'
and EventTypeId = '5365f53c-583b-4b53-bf50-2ec7c002e53c'
and g.TransferOrdinalNumber=1
group by C.PatientId, G.PatientId, c.StartDate, g.TransferOrdinalNumber
order by C.PatientId, G.PatientId

新人

您可以使用UNION ALL语句在非常基本的方法中会有所帮助。

select  
c.PatientId
,g.PatientId
,c.StartDate
,g.TransferOrdinalNumber

from ClinicalEvent C
join PatientIncomming G on C.PatientId=G.PatientId

where

(DATEPART(dw, StartDate) in (1,2,3,4,5))
and (
(CAST(StartDate as Time) between '20:00' and '23:59')
or 
(CAST(StartDate as Time) between '00:01' and '07:00')
)


and StartDate between '2020.02.01' and '2020.2.28'
and EventTypeId = '5365f53c-583b-4b53-bf50-2ec7c002e53c'
and g.TransferOrdinalNumber=1
group by C.PatientId, G.PatientId, c.StartDate, g.TransferOrdinalNumber
UNION ALL

select  
c.PatientId
,g.PatientId
,c.StartDate
,g.TransferOrdinalNumber

from ClinicalEvent C
join PatientIncomming G on C.PatientId=G.PatientId

where

(DATEPART(dw, StartDate) in (6,7))
--and (
--(CAST(StartDate as Time) between '20:00' and '23:59')
--or 
--(CAST(StartDate as Time) between '00:01' and '07:00')
--)


and StartDate between '2020.02.01' and '2020.2.28'
and EventTypeId = '5365f53c-583b-4b53-bf50-2ec7c002e53c'
and g.TransferOrdinalNumber=1
group by C.PatientId, G.PatientId, c.StartDate, g.TransferOrdinalNumber
    order by C.PatientId, G.PatientId

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。