Summing DateDiff and output cascading for each row in variables(SQL-Server) (PHP)

Question

it will be very difficult to explain this in words so I will attempt small example to explain you what i want:

for example i have this SQL-Server table

+-------+-------+------+
| TestID| Start | End  | 
+-------+-------+------+
|     1 | DateA | DateB|  
|     2 | DateA | DateB| 
|     3 | DateA | DateB|   
|     4 | DateA |      |     
+-------+-------+------+

What i want is this table:

$Rest = 1000

+-------+-------+------+----------+-----------+----------+
| TestID| Start | End  | Testtime | Totaltime | Resttime |
+-------+-------+------+----------+-----------|----------|
|     1 | DateA | DateB|   214    |   214     |   786    |
|     2 | DateA | DateB|   100    |   314     |   686    |
|     3 | DateA | DateB|   200    |   514     |   486    |
|     4 | DateA |      |          |           |          |
+-------+-------+------+----------+-----------+----------+

i have the problem to understand what i have to combine

here is the code to get the first SQL-Server table

SELECT  TestID, Start, End, DATEDIFF(hour, Start, End) AS Testtime
                         FROM Testresults
                         WHERE TesttableID = 1

Each Row has the TesttableID = 1 thx for help.

Edit : SQL Server Version: 9.0.5057

Edit : I get results but not the right one, they are switched in Totaltime and Resttime

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.TestIDAND t2.TesttableID = 1  ) AS Resttime  FROM Testresults t1 WHERE t1.TesttableID = 1

What i get is These Results, they switched..:

+-------+-------+------+----------+-----------+----------+
| TestID| Start | End  | Testtime | Totaltime | Resttime |
+-------+-------+------+----------+-----------|----------|
|     1 | DateA | DateB|   214    |   514     |   486    |
|     2 | DateA | DateB|   100    |   300     |   700    |
|     3 | DateA | DateB|   200    |   200     |   800    |
|     4 | DateA |      |          |           |          |
+-------+-------+------+----------+-----------+----------+

All information at this post : https://stackoverflow.com/questions/860966/calculate-a-running-total-in-sql-server — Sanpas, Jan 17 '19 at 13:10
The real problem here is your using a 14 year old version of SQL Server that had been unsupported for 4 years. It's time to upgrade. — Thom A, Jan 17 '19 at 13:12

Michał Turczyn · Answer 1 · 2019-01-17T13:16:08.520

2

You could try this:

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

For SQL Server 2005 you could use JOIN to accomplish this:

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

edited Jan 17 '19 at 13:16

answered Jan 17 '19 at 10:47

Michał Turczyn

32,028
14
47
69

The Version is: 9.0.5057 – Daniel Jan 17 '19 at 11:35
than i guess i have to handle it somehow in php with a function – Daniel Jan 17 '19 at 11:38
testtime is just a temporary Name and i dont have a table2 i want Totaltime and Resttime as temporary Name. I tryed this: SELECT TestID, start, end, DATEDIFF(hour,start,end) AS Testtime, (SELECT SUM(Testtime) FROM Testresults WHERE TestID <=aa.TestID) AS Totaltime FROM Testresult as aa ORDER BY Testtime, Totaltime But i ge the error: incorrect columnname Testtime – Daniel Jan 17 '19 at 12:01
The first table is changed – Daniel Jan 17 '19 at 12:26
still incorrect columnname Testtime becasue this column doesnt exist only temporary – Daniel Jan 17 '19 at 12:34
rly dunno SELECT t1.TestID, t1.start, t1.end, DATEDIFF(hour,start,end) AS Testtime, t1.Testtime, SUM(t2.Testtime), 1000-SUM(t2.Testime) FROM Testresults t1 JOIN Testresults on t1.TestID >= t2.TestID GROUP BY t1.TestID, t1.start, t1.end, t1.Testtime incorrect columnname Testtime and multible columnname – Daniel Jan 17 '19 at 12:40
same Problem in your code the Alias Column Syntax missing, unknown Testtime Totaltime and Resttime – Daniel Jan 17 '19 at 12:53
ty now i get reslts but not the one i expected. I edited my first post with the actual code. Can u maybe check it. thx – Daniel Jan 17 '19 at 13:43

score 1 · Answer 2 · answered Jan 17 '19 at 10:47

Based on the sample data we have this gets you the result:

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;

Note that you likely need to implement some kind of PARTITION BY in the OVER clause, but I have no idea what that would need to be based on the limtied data we have.

`OVER` was introduced in SQL Server 2005 (iirc), @Daniel. Are you *really* using something prior to that? `ROWS BETWEEN` was introduced with SQL Server 2012, however, 2008 only has a couple of months of extended support left, and 2005 is way out of support, so you really should have upgraded by now. Why haven't you? — Thom A, Jan 17 '19 at 13:00

score 1 · Answer 3 · edited Jan 18 '19 at 10:09

You Can try this :

 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

Explain at this post : Calculate a Running Total in SQL Server

RESULT :

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

To user on previous SQL Server Version you can check at this post: http://geekswithblogs.net/Rhames/archive/2008/10/28/calculating-running-totals-in-sql-server-2005---the-optimal.aspx

REVISION For previous SQL version :

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

RESULT :

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

Sorry but i didn't know your all configuration Can you try this :

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

@Daniel you can check at this link : I know there are various ways of doing this in SQL Server 2000 / 2005 / 2008. http://geekswithblogs.net/Rhames/archive/2008/10/28/calculating-running-totals-in-sql-server-2005---the-optimal.aspx — Sanpas, Jan 17 '19 at 13:09
ty i did, i posted an update in my first post. Problem is only now the results are switched, can u check it? — Daniel, Jan 17 '19 at 15:03
@Daniel her updated my example and using your query :) . let my know if it's a good way and your excepted output . — Sanpas, Jan 17 '19 at 15:20
I get error: Incorrect Syntax near 'IS' That IIF statement only exists in MDX - the query language for SQL Server Analysis Services - the datawarehousing side of SQL Server. Plain T-SQL does not have an IIF Statement source: https://stackoverflow.com/questions/4374907/iif-statement-in-sql-server-2005 — Daniel, Jan 17 '19 at 15:53
@Daniel i have update , sorry but i didn't your complet install ... let me know if it's query work. Note. have just change the t2.TestID >= t1.TestID by t2.TestID <= t1.TestID — Sanpas, Jan 17 '19 at 16:12
no thats just summing from every TestID the Testimes to a high number as Totaltime. My Table is ofc only a example it has much more entrys. I tryed already like in the post mentioned to replace te IIF with Case When, Then, Else, End but i get an error. The solution was i have to preselect the rows with TesttableID = 1 and than summing them. look post 1. But one code Change i need is that if Testend is null dont Output totaltime and resttime, becasue this test is open and is still going. — Daniel, Jan 18 '19 at 09:19

Summing DateDiff and output cascading for each row in variables(SQL-Server) (PHP)

3 Answers3