时间戳

首页 > 解决方案 > 生成月报表状态的 SQL 查询

sql - 生成月报表状态的 SQL 查询

问题描述

我有一张这样的桌子：

TICKET ID | OPEN DATE  | CLOSE DATE
----------+------------+------------
1         | 2018-12-30 | 2019-01-01 
2         | 2019-01-30 | 2019-02-01 
3         | 2019-01-20 | 2019-01-22

我必须生成一个视图，为当年的每个月为每个工单 ID 创建几个具有状态（打开、关闭、积压）的条目。如果 OPEN DATE 在当月，则状态 OPEN，如果关闭日期在当月，则状态 CLOSED，如果打开日期在上个月且关闭日期不在上个月，则状态为 BACKLOG。

因此，在下面的示例中，输出表将是：

TICKET ID | MONTH  | STATUS
----------+--------+---------
1         | JAN-19 | BACKLOG
1         | JAN-19 | CLOSED
2         | JAN-19 | OPEN
3         | JAN-19 | OPEN
3         | JAN-19 | CLOSED
2         | FEB-19 | CLOSED

有没有办法在 SQL Server 的纯 SQL 中做到这一点？

标签： sqlsql-server

解决方案

这是做你想做的吗？您可以通过调整日期表来调整它运行的时间段。

declare @tickets table (ticketId int, openDate datetime, closeDate datetime)

insert into @tickets (ticketId, openDate, closeDate) values (1, '30 Dec 2018', '1 Jan 2019')
insert into @tickets (ticketId, openDate, closeDate) values (2, '30 Jan 2019', '1 Feb 2019')
insert into @tickets (ticketId, openDate, closeDate) values (3, '20 Jan 2019', '22 Jan 2019')

; with dates as (
    select cast('1 Jan 2019' as datetime) startMon, cast('31 Jan 2019' as dateTime) endMon
    union all
    select dateAdd(mm, 1, startMon), DATEADD(dd, -1, dateAdd(mm, 2, startMon)) from dates where startMon < '01 Dec 2019'
)
select ticketId, startMon, 'BACKLOG' [Status] from @tickets t inner join dates d on t.openDate between dateAdd(m, -1, startMon) and startMon and closeDate < endMon and closeDate >= startMon
union
select ticketId, startMon, 'OPEN' from @tickets t inner join dates d on t.openDate between startMon and endMon
union
select ticketId, startMon, 'CLOSED' from @tickets t inner join dates d on t.closeDate between startMon and endMon
order by startMon, ticketId

推荐阅读