时间戳

首页 > 解决方案 > SQL - 添加空白列并根据计算的 datediff 列中的值进行填充

sql - SQL - 添加空白列并根据计算的 datediff 列中的值进行填充

问题描述

我对 SQL 非常陌生，而且是自学的....所以请清楚。我正在尝试在我的数据中添加一个空白列，该列将显示天数列中的数字是否为：

15-21 天 15-21 天 ,22-28 天 22-28 天 ,>28 天 违反

我可以使用' ' AS Status. 我只需要知道如何填充它

declare @HG Varchar (100)

set @HG = 'Clinical Support - Health Group'



SELECT distinct OURREF as [DATIX ref], DATE_OPENED as [Date opened (dd/mm/yyyy)], DATE_OF_INCIDENT as [Incident date], ORGANISATION AS [Health Group], DIRECTORATE as [Division], SPECIALTY as [Specialty], ACTUAL_LOCATION As [Exact location], SEVERITY as [Severity], DATEDIFF(Day, DATE_OPENED, CURRENT_TIMESTAMP) as Days, INCIDENT_TYPE as [Type], CATEGORY as [Category], SUBCATEGORY as [sub Category], APPROVED_CODE as [Current approval status], inc_mgr AS [Handler]
FROM [CDI_PATEX_INCIDENTS] AS a
JOIN dbo.DATIX_incidents_main b on a.OURREF = b.inc_ourref

where OURREF not like 'SUI%' and APPROVED_CODE in ('INREV') and ORGANISATION in (@HG) 
and DATEDIFF(dd,DATE_OPENED,GETDATE()) between 15 and 21

UNION

SELECT distinct OURREF as [DATIX ref], DATE_OPENED as [Date opened (dd/mm/yyyy)], DATE_OF_INCIDENT as [Incident date], ORGANISATION AS [Health Group], DIRECTORATE as [Division], SPECIALTY as [Specialty], ACTUAL_LOCATION As [Exact location], SEVERITY as [Severity], DATEDIFF(Day, DATE_OPENED, CURRENT_TIMESTAMP) as Days, INCIDENT_TYPE as [Type], CATEGORY as [Category], SUBCATEGORY as [sub Category], APPROVED_CODE as [Current approval status], inc_mgr AS [Handler]
FROM [CDI_PATEX_INCIDENTS] AS a
JOIN dbo.DATIX_incidents_main b on a.OURREF = b.inc_ourref

where OURREF not like 'SUI%' and APPROVED_CODE in ('INREV') and ORGANISATION in (@HG) 
and DATEDIFF(dd,date_reported,GETDATE()) between 21 and 28

UNION


SELECT distinct OURREF as [DATIX ref], DATE_OPENED as [Date opened (dd/mm/yyyy)], DATE_OF_INCIDENT as [Incident date], ORGANISATION AS [Health Group], DIRECTORATE as [Division], SPECIALTY as [Specialty], ACTUAL_LOCATION As [Exact location], SEVERITY as [Severity], DATEDIFF(Day, DATE_OPENED, CURRENT_TIMESTAMP) as Days, INCIDENT_TYPE as [Type], CATEGORY as [Category], SUBCATEGORY as [sub Category], APPROVED_CODE as [Current approval status], inc_mgr AS [Handler]
FROM [CDI_PATEX_INCIDENTS] AS a
JOIN dbo.DATIX_incidents_main b on a.OURREF = b.inc_ourref

where OURREF not like 'SUI%' and APPROVED_CODE in ('INREV') and ORGANISATION in (@HG) 
and DATEDIFF(dd,DATE_OPENED,GETDATE()) > 28


Group by OURREF, SPECIALTY, ORGANISATION, SEVERITY, DIRECTORATE, DATE_OPENED, DATE_OF_INCIDENT, ACTUAL_LOCATION, INCIDENT_TYPE, CATEGORY, SUBCATEGORY, DATE_REPORTED, APPROVED_CODE, inc_mgr
Order by Days desc

标签： sqlsql-servertsql

解决方案

你为什么不一次性完成它们，没有联合，并使用这种技术？

select 
....,

case when DATEDIFF(dd, DATE_OPENED, GETDATE()) BETWEEN 21 AND 28 then 'x'
when DATEDIFF(dd, DATE_OPENED, GETDATE()) > 28 then 'y'
else 'z' end as Status

from tablex

推荐阅读