如何获取特定日期的记录状态。SQL

debugcn 发表于 Dev

维克多·乌德希

我正在尝试创建一个查询以查看在特定时间段内是否打开了记录（警报）。

第一个表只有记录的当前状态。

    table: ALERT

    intID  |CurrentStateID| DateCreated
   -----------------------------------
    3      |Closed        | 10/11/2009

第二个表包含警报状态的历史记录。

    tblState

    intContextID|strToStateName|datTimeStamp
   -----------------------------------
    3       |Unassigned    |10/11/2009
    3       |Closed        |10/14/2009

这是我想要的输出：

    DESIRED OUTPUT

    DATE        |DAY        |TOTAL_OPEN
    -----------------------------------
    10/10/2009  |Friday     |0
    10/11/2009  |Saturday   |1
    10/12/2009  |Sunday     |1
    10/13/2009  |Monday     |1
    10/14/2009  |Tuesday    |0
    10/15/2009  |Wednesday  |0

我能够编写一些代码，但我认为它在 AllDays 表上的连接可能是错误的。

DECLARE @StartDate DATETIME = '2009-10-10';
DECLARE @EndDate DATETIME = '2009-10-15 23:59:59';

WITH AllDays
AS (
    SELECT @StartDate AS [Date]
        , 1 AS [level]

    UNION ALL

    SELECT DATEADD(DAY, 1, [Date])
        , [level] + 1
    FROM AllDays
    WHERE [Date] < @EndDate
    )
SELECT CAST(AllDays.[Date] AS DATE) AS 'DATE'
    , datename(dw, AllDays.[Date]) AS 'DAY'
    ,ISNULL(TOTAL_OPEN, 0) as TOTAL_OPEN
FROM AllDays
LEFT JOIN (
    SELECT DISTINCT s.datTimeStamp AS 'DATE'
        , count(A.intID) AS 'TOTAL_OPEN'
    FROM Alert A
    INNER JOIN tblState S ON A.intID = S.intContextID
    WHERE strToStateName = 'Unassigned'
    GROUP BY datTimeStamp
    ) AS TOTAL_OPEN ON TOTAL_OPEN.DATE = AllDays.[Date]

警报从 10-11 到 10-13 开放，但由于我在 datetimestamp 上加入，结果只显示 1 表示 10/11。

这是架构链接！

本压

好的.. 一种（不可否认的）方法来创建一个 udf 来计算给定日期的开放计数，如下所示：

CREATE FUNCTION [dbo].[fn_open_alert_count] (@date date)
RETURNS int
AS
BEGIN
RETURN (SELECT COUNT(intContextID) as opencount FROM tblState  WHERE strToStateName = 'Unassigned' AND datTimeStamp <= @date) - (SELECT COUNT(intContextID) as closedcount FROM tblState  WHERE strToStateName = 'Closed' AND datTimeStamp <= @date) 
END

然后你可以像这样进行查询：

DECLARE @StartDate DATETIME = '2009-10-10';
DECLARE @EndDate DATETIME = '2009-10-15 23:59:59';

WITH AllDays
AS (
    SELECT @StartDate AS [Date]
        , 1 AS [level]

    UNION ALL

    SELECT DATEADD(DAY, 1, [Date])
        , [level] + 1
    FROM AllDays
    WHERE [Date] < @EndDate
    )
SELECT CAST(AllDays.[Date] AS DATE) AS 'DATE'
    , datename(dw, AllDays.[Date]) AS 'DAY'
    ,[dbo].[fn_open_alert_count](AllDays.[Date])
FROM AllDays

如果您无权创建函数，则可以像这样内联该功能：

DECLARE @StartDate DATETIME = '2009-10-10';
DECLARE @EndDate DATETIME = '2009-10-15 23:59:59';

WITH AllDays
AS (
    SELECT @StartDate AS [Date]
        , 1 AS [level]

    UNION ALL

    SELECT DATEADD(DAY, 1, [Date])
        , [level] + 1
    FROM AllDays
    WHERE [Date] < @EndDate
    )
SELECT CAST(AllDays.[Date] AS DATE) AS 'DATE'
    , datename(dw, AllDays.[Date]) AS 'DAY'
    ,(SELECT COUNT(intContextID) as opencount FROM tblState  WHERE strToStateName = 'Unassigned' AND datTimeStamp <= AllDays.[Date]) - (SELECT COUNT(intContextID) as closedcount FROM tblState  WHERE strToStateName = 'Closed' AND datTimeStamp <= AllDays.[Date]) AS TOTAL_OPEN
FROM AllDays

这给了我以下输出：

DATE        DAY         TOTAL_OPEN
2009-10-10  Saturday    0
2009-10-11  Sunday      1
2009-10-12  Monday      1
2009-10-13  Tuesday     1
2009-10-14  Wednesday   0
2009-10-15  Thursday    0
2009-10-16  Friday      0

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

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