时间戳

首页 > 解决方案 > 尝试每天获得 1 场比赛 SQL Server

sql - 尝试每天获得 1 场比赛 SQL Server

问题描述

好的，对 SQL Server 很陌生，所以请耐心等待。

我正在尝试找到每天的开始时间，以生成过去 30 天的自动工作报告。我已经设法让一些信息发挥作用，但我正在努力解决我认为是基本的信息。

所以一些基本的背景。当我们在生产时，步骤数是 30。如果生产每天都顺利运行就足够了。我想做的是找到我们进入第 30 步的时间和生产的桶 = 1

我以为我有这个：

use Runtime

SET NOCOUNT ON

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime

SET @EndDate = GETDATE()
SET @StartDate = DATEADD(DAY, -30, @enddate)

SET NOCOUNT OFF

SELECT 
    a.TagName, a.DateTime, a.Value, 
    b.tagname, b.value, b.DateTime, a.StartDateTime
FROM
    History A, History B
WHERE
    a.tagname = ('H20W01_Prod_StepNo') 
    AND b.tagname = ('H20W01_Par_VatsProduced')
    AND a.Value = '30' 
    AND b.value = '1'
    AND a.DateTime BETWEEN @StartDate AND @EndDate
    AND b.DateTime BETWEEN @StartDate AND @EndDate

但是，这会返回此结果

结果

我似乎得到了 30 天每一天进入第 30 步的开始时间。但是每天有 30 行，最后 30 行中的每一个都有第一个大桶的日期时间。

任何帮助，将不胜感激。我在想我需要一个连接，但两者都存储在同一个表中

先感谢您

标签： sqlsql-servertsql

解决方案

最终答案将取决于了解整个问题，但从问题中阅读我会说这可行。

DECLARE @History TABLE (TagName VARCHAR(MAX), [DateTime] DATETIME, Value INT)

-- on 24th the 30 is reached at 10 am (10:00), Vats value 1 reached at 11 am (11:00)
INSERT INTO @History VALUES ('H20W01_Prod_StepNo', '20200624 09:00', 1)
INSERT INTO @History VALUES ('H20W01_Prod_StepNo', '20200624 10:00', 30)
INSERT INTO @History VALUES ('H20W01_Prod_StepNo', '20200624 11:00', 40)
INSERT INTO @History VALUES ('H20W01_Prod_StepNo', '20200624 12:00', 50)
INSERT INTO @History VALUES ('H20W01_Par_VatsProduced', '20200624 00:00', 0)
INSERT INTO @History VALUES ('H20W01_Par_VatsProduced', '20200624 10:00', 0)
INSERT INTO @History VALUES ('H20W01_Par_VatsProduced', '20200624 11:00', 1)
INSERT INTO @History VALUES ('H20W01_Par_VatsProduced', '20200624 12:00', 1)

-- on 25th the 30 is reached at 1 pm (13:00); Vats value 1 reached at 2 pm (14:00)
INSERT INTO @History VALUES ('H20W01_Prod_StepNo', '20200625 00:00', 1)
INSERT INTO @History VALUES ('H20W01_Prod_StepNo', '20200625 10:00', 4)
INSERT INTO @History VALUES ('H20W01_Prod_StepNo', '20200625 11:00', 6)
INSERT INTO @History VALUES ('H20W01_Prod_StepNo', '20200625 12:00', 28)
INSERT INTO @History VALUES ('H20W01_Prod_StepNo', '20200625 13:00', 30)
INSERT INTO @History VALUES ('H20W01_Prod_StepNo', '20200625 15:00', 30)
INSERT INTO @History VALUES ('H20W01_Par_VatsProduced', '20200625 00:00', 0)
INSERT INTO @History VALUES ('H20W01_Par_VatsProduced', '20200625 10:00', 0)
INSERT INTO @History VALUES ('H20W01_Par_VatsProduced', '20200625 11:00', 0)
INSERT INTO @History VALUES ('H20W01_Par_VatsProduced', '20200625 14:00', 1)
INSERT INTO @History VALUES ('H20W01_Par_VatsProduced', '20200625 15:00', 1)

-- create a help table of last 10 days
DECLARE @Pivot DATETIME = DATEADD(day, -10, GETDATE())
DECLARE @Days TABLE (Day DATETIME)
WHILE (@Pivot < GETDATE()) BEGIN
    INSERT INTO @Days VALUES (@Pivot)
    SET @Pivot = DATEADD(day, 1, @Pivot)
END

SELECT
    D.Day,
    MIN(HA.DateTime) AS ProductionStepReachedOn,
    MIN(HB.DateTime) AS VatsReachedOn
FROM
    @Days D
    LEFT JOIN @History HA ON 
        HA.TagName = 'H20W01_Prod_StepNo' AND
        HA.[DateTime] BETWEEN D.Day AND DATEADD(second, -1, DATEADD(day, 1, D.Day)) AND
        HA.Value = 30
    LEFT JOIN @History HB ON 
        HB.TagName = 'H20W01_Par_VatsProduced' AND
        HB.[DateTime] BETWEEN D.Day AND DATEADD(second, -1, DATEADD(day, 1, D.Day)) AND
        HB.Value = 1
GROUP BY
    D.Day

推荐阅读