การรวม DateDiff และเอาต์พุตเรียงซ้อนสำหรับแต่ละแถวในตัวแปร (SQL-Server) (PHP)

คุณสามารถลองสิ่งนี้:

SELECT TestId,
       Start,
       End,
       Testtime,
       SUM(Testtime) OVER (ORDER BY TestId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
       1000 - SUM(Testtime) OVER (ORDER BY TestId ROWS BETWEEN AND UNBOUNDED PRECEDING AND CURRENT ROW)
FROM MyTable

สำหรับ SQL Server 2005 คุณสามารถใช้ JOIN เพื่อทำสิ่งนี้ให้สำเร็จ:

select t1.testid,
       t1.start, 
       t1.end, 
       DATEDIFF(hour, t1.Start, t1.End), 
       sum(DATEDIFF(hour, t2.Start, t2.End)), 
       1000 - sum(DATEDIFF(hour, t2.Start, t2.End))
from MyTable t1
join MyTable t2 on t1.testid >= t2.testid
group by t1.testid, t1.start, t1.end, t1.Start, t1.End

จากข้อมูล ตัวอย่าง ที่เราได้รับ ผลลัพธ์ที่ได้คือ:

DECLARE @Rest int = 1000;

WITH VTE AS (
    SELECT *
    FROM (VALUES(1,'DateA','DateB',214),
                (2,'DateA','DateB',100),
                (3,'DateA','DateB',200),
                (4,'DateA',NULL,NULL)) V(TestID,[Start],[End],Testtime))
SELECT VTE.TestID,
       VTE.Start,
       VTE.[End],
       VTE.Testtime,
       CASE WHEN [End] IS NOT NULL THEN SUM(VTE.Testtime) OVER (ORDER BY VTE.TestID ASC
                                                                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) END AS TotalTime,
       CASE WHEN [End] IS NOT NULL THEN @Rest - SUM(VTE.Testtime) OVER (ORDER BY VTE.TestID ASC
                                                                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) END AS RestTime
FROM VTE;

โปรดทราบว่าคุณอาจต้องใช้ PARTITION BY บางประเภทในส่วนคำสั่ง OVER แต่ฉันไม่รู้ว่าจะต้องใช้อะไรบ้างโดยพิจารณาจากข้อมูลที่จำกัดที่เรามี

คุณสามารถลองสิ่งนี้:

 CREATE TABLE #MyTable  
    (PrimaryKey   int PRIMARY KEY,  
       DateValueBegin      DATETIME,
       DateValueEnd      DATETIME,
       NbValue      int
      );  
    GO  

    INSERT INTO #MyTable 
    SELECT 1, DATEADD(HOUR,-2,GETDATE()), GETDATE(), 214
    UNION
    SELECT 2, DATEADD(HOUR,-2,GETDATE()), DATEADD(HOUR,-1,GETDATE()), 100
    UNION 
    SELECT 3, DATEADD(HOUR,-2,GETDATE()), GETDATE(), 200
    UNION
    SELECT 4, DATEADD(HOUR,-1,GETDATE()), NULL, 210
    UNION
    SELECT 5, DATEADD(HOUR,-1,GETDATE()), NULL, 0;

    SELECT *, SUM(NbValue) OVER(ORDER BY PrimaryKey 
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as 'Totaltime',
         1000 - SUM(NbValue) OVER(ORDER BY PrimaryKey 
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) As 'RestTime'    FROM #MyTable

    DROP TABLE  #MyTable

อธิบายได้ที่โพสต์นี้: คำนวณ Running Total ใน SQL Server

ผลลัพธ์ :

PrimaryKey  DateValueBegin  DateValueEnd    NbValue Totaltime   RestTime
1   2019-01-17 09:48:05.123 2019-01-17 11:48:05.123 214 214 786
2   2019-01-17 09:48:05.123 2019-01-17 10:48:05.123 100 314 686
3   2019-01-17 09:48:05.123 2019-01-17 11:48:05.123 200 514 486
4   2019-01-17 10:48:05.123 NULL    210 724 276
5   2019-01-17 10:48:05.123 NULL    0   724 276

สำหรับผู้ใช้ SQL Server เวอร์ชันก่อนหน้าคุณสามารถตรวจสอบได้ที่โพสต์นี้: http://geekswithblogs.net/Rhames/archive/2008/10/28/calculating-running-totals-in-sql-server-2005---the-optimal.aspx

การแก้ไขสำหรับ SQL เวอร์ชันก่อนหน้า:

CREATE TABLE #MyTable  
    (PrimaryKey   int PRIMARY KEY,  
       DateValueBegin      DATETIME,
       DateValueEnd      DATETIME
      );  
    GO  

    INSERT INTO #MyTable 
    SELECT 1, DATEADD(HOUR,-214,GETDATE()), GETDATE()
    UNION
    SELECT 2, DATEADD(HOUR,-100,GETDATE()), DATEADD(HOUR,-1,GETDATE())
    UNION 
    SELECT 3, DATEADD(HOUR,-200,GETDATE()), GETDATE()
    UNION
    SELECT 4, DATEADD(HOUR,-1,GETDATE()), NULL
    UNION
    SELECT 5, DATEADD(HOUR,-1,GETDATE()), NULL;

    SELECT * FROM #MyTable

    SELECT PrimaryKey,
       DateValueBegin,
       DateValueEnd,
       DATEDIFF(hour,DateValueBegin,IIF(DateValueEnd IS NOT NULL ,DateValueEnd, DateValueBegin)) as Testtime,
       (SELECT SUM(DATEDIFF(hour,DateValueBegin,IIF(DateValueEnd IS NOT NULL ,DateValueEnd, DateValueBegin)))
        FROM #MyTable t2
       WHERE t2.PrimaryKey <= t1.PrimaryKey  ) AS Totaltime,
       DATEDIFF(hour,DateValueBegin,IIF(DateValueEnd IS NOT NULL ,DateValueEnd, DateValueBegin)) as Testtime,
       (SELECT 1000-SUM(DATEDIFF(hour,DateValueBegin,IIF(DateValueEnd IS NOT NULL ,DateValueEnd, DateValueBegin)))
        FROM #MyTable t3
       WHERE t3.PrimaryKey <= t1.PrimaryKey  ) AS Resttime
    FROM #MyTable t1



       DROP TABLE  #MyTable

ผลลัพธ์ :

PrimaryKey  DateValueBegin  DateValueEnd    Testtime    Totaltime   Testtime    Resttime
1   2019-01-08 18:17:35.430 2019-01-17 16:17:35.430   214   214 214 786
2   2019-01-13 12:17:35.430 2019-01-17 15:17:35.430   99    313 99  687
3   2019-01-09 08:17:35.430 2019-01-17 16:17:35.430   200   513 200 487
4   2019-01-17 15:17:35.430  NULL                     0 513 0   487
5   2019-01-17 15:17:35.430  NULL                     0 513 0   487

ขออภัย แต่ฉันไม่ทราบการกำหนดค่าทั้งหมดของคุณ คุณลองสิ่งนี้ได้ไหม:

SELECT t1.TestID,
           t1.start,
           t1.end, 
           t1.TesttableID,
           DATEDIFF(hour,t1.start,t1.end) as Testtime,
           (SELECT SUM(DATEDIFF(hour,t2.start,t2.end))
            FROM Testresults t2
           WHERE t2.TestID <= t1.TestsID AND t2.TesttableID = 1  ) AS Totaltime,
           (SELECT 1000-SUM(DATEDIFF(hour,t2.start,t2.end))
           FROM Testresults t2
           WHERE t2.TestID <= t1.TestID AND t2.TesttableID = 1  ) AS Resttime  FROM Testresults t1 WHERE t1.TesttableID = 1