sql-server - 如何获取特定日期的记录状态。SQL
问题描述
我正在尝试创建一个查询以查看记录(警报)是否在特定时间段内打开。
第一个表只有记录的当前状态。
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
推荐阅读
- c# - WPF 在列表框中启用突出显示、复制和粘贴
- c# - C# Twilio 检索合成媒体
- c++ - C ++:是否可以模板类型名?
- rxjs - 给 combineLatest 的 observables 的顺序是否相关?
- sql - SQL "$" 符号用法
- php - 自动将新条目添加到表 codeigniter
- wordpress - 当我配置我的子域时 ERR_TOO_MANY_REDIRECTS
- amazon-web-services - AWS S3 Sync --force-glacier-transfer
- google-api - 为私有应用环境发布 Google OAuth 开发者验证表
- swift - 你在哪里声明与 Swift 中的 addGestureRecognizer() 相关的 UITapGestureRecognizer()?