创建每日报告

Question

​

​

查询：

select 
    Sum(Case when fld_event_subject='Cold Call' THEN 1 ELSE 0 END) AS ColdCall,
    Sum(Case when fld_event_subject='Referral Call' THEN 1 ELSE 0 END) As ReferalCall,
    Sum(Case when fld_event_subject='Email Sent & Fax Sent' THEN 1 ELSE 0 END) As [Email Sent & Fax Sent]
from 
    tbl_event 
where
    (fld_event_created_on >= '12/15/2015 12:00:00 AM' 
     and fld_event_created_on <= '12/16/2015 3:53:04 AM')

问题：

• 如何在{我将从小时到小时}中显示时间列
• 如何将开始时间(从小时开始)添加为下午5:30？
• 如何在下午6:30计算结束时间(在一个小时内)？
• 如何自动增加餐桌上的工时？

例如：

5:30PM to 6:30PM
6:30PM to 7:30PM
::
::
4:30AM to 5:30 AM

Answer 1

我想我有个解决办法给你。这不是最简单的，但也允许不规则的时间跨度：

-- drop table CallLog
create table CallLog
(
    CallLogId INT,
    Timestamp DATETIME2,
    Subject VARCHAR(100),
    OnlyTime AS CAST(Timestamp AS TIME)
)

-- drop table TimeInterval
create table TimeInterval
(
    TimeIntervalId INT,
    StartTime TIME,
    EndTime TIME,
    -- to handle case when interval is split between days
    CrossesMidnight BIT NOT NULL CONSTRAINT DF_TimeInterval_CrossesMidnight DEFAULT(0)
) 

insert into TimeInterval values (1, '23:30', '00:30', 1)
insert into TimeInterval values (1, '00:30', '01:30', 0)
insert into TimeInterval values (1, '01:30', '02:30', 0)
insert into TimeInterval values (1, '02:30', '20:30', 0)
insert into TimeInterval values (1, '20:30', '21:30', 0)
insert into TimeInterval values (1, '21:30', '22:30', 0)
insert into TimeInterval values (1, '22:30', '23:30', 0)
go

select * from TimeInterval

insert into CallLog values (1, '2015-01-01 10:00', 'Cold Call') 
insert into CallLog values (1, '2015-01-01 23:35', 'Cold Call') 
insert into CallLog values (1, '2015-01-02 00:01', 'Referral Call') 
insert into CallLog values (1, '2015-01-02 10:01', 'Referral Call') 
insert into CallLog values (1, '2015-01-02 21:35', 'Referral Call') 
select * from CallLog

-- I did not find an universal formatting function to also work only on TIMEs
alter function dbo.timeFormat(@time TIME)
RETURNS VARCHAR(100)
AS
BEGIN
    RETURN CAST(DATEPART(HOUR, @time) AS VARCHAR) + '.' + CAST(DATEPART(MINUTE, @time) AS VARCHAR)
END
GO

-- join condition is very complex and might need rewriting for very large data (or maybe just get rid of that midnight interval :) )
-- TODO: <= 23:59:59 skips events within the last second in day!
;with cte as (
select 
    t.StartTime, t.EndTime,
    Sum(Case when Subject='Cold Call' THEN 1 ELSE 0 END) AS ColdCall,
    Sum(Case when Subject='Referral Call' THEN 1 ELSE 0 END) As ReferalCall
from CallLog l
    join TimeInterval t on
        -- this would greatly simplify, if midnight is not inside the interval
    (t.CrossesMidnight = 0 AND (l.OnlyTime > t.StartTime AND l.OnlyTime <= t.EndTime)) 
    OR 
    (t.CrossesMidnight = 1 AND (((l.OnlyTime > t.StartTime) AND l.OnlyTime <= '23:59:59') OR (l.OnlyTime < t.EndTime)))
GROUP BY t.StartTime, t.EndTime)
select dbo.timeFormat(cte.StartTime) + ' - ' + dbo.timeFormat(cte.EndTime), cte.ColdCall, cte.ReferalCall 
from cte
go