Menjumlahkan DateDiff dan keluaran berjenjang untuk setiap baris dalam variabel (SQL-Server) (PHP)

akan sangat sulit untuk menjelaskan hal ini dengan kata-kata jadi saya akan mencoba contoh kecil untuk menjelaskan kepada Anda apa yang saya inginkan:

misalnya saya punya tabel SQL-Server ini

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

Yang saya inginkan adalah tabel ini:

$Istirahat = 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 |      |          |           |          |
+-------+-------+------+----------+-----------+----------+

saya memiliki masalah untuk memahami apa yang harus saya gabungkan

berikut adalah kode untuk mendapatkan tabel SQL-Server pertama

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

Setiap Baris mendapat TesttableID = 1 terima kasih atas bantuannya.

Sunting: Versi SQL Server: 9.0.5057

Sunting : Saya mendapatkan hasil tetapi tidak tepat, hasilnya dialihkan dalam Totaltime dan 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

Apa yang saya dapatkan adalah Hasil Ini, mereka beralih ..:

+-------+-------+------+----------+-----------+----------+
| 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 |      |          |           |          |
+-------+-------+------+----------+-----------+----------+

php sql sql-server datediff sql-server-2005
person Daniel    schedule 17.01.2019    source sumber
comment
Semua informasi di posting ini: stackoverflow .com/questions/860966/   -  person Sanpas    schedule 17.01.2019
comment
Masalah sebenarnya di sini adalah Anda menggunakan SQL Server versi 14 tahun yang tidak didukung selama 4 tahun. Saatnya untuk meningkatkan.   -  person Larnu    schedule 17.01.2019

Jawaban (3)


arrow_upward
2
arrow_downward

Anda dapat mencoba ini:

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

Untuk SQL Server 2005 Anda bisa menggunakan JOIN untuk mencapai hal ini:

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
person Michał Turczyn    schedule 17.01.2019
comment
Versinya adalah: 9.0.5057 - person Daniel; 17.01.2019
comment
daripada saya kira saya harus menanganinya entah bagaimana di php dengan suatu fungsi - person Daniel; 17.01.2019
comment
testtime hanyalah Nama sementara dan saya tidak punya tabel2 saya ingin Totaltime dan Resttime sebagai Nama sementara. Saya mencoba ini: SELECT TestID, start, end, DATEDIFF(hour,start,end) AS Testtime, (SELECT SUM(Testtime) FROM Testresults WHERE TestID ‹=aa.TestID) AS Totaltime FROM Testresult sebagai aa ORDER BY Testtime, Totaltime But saya mendapatkan kesalahan: nama kolom yang salah Waktu Tes - person Daniel; 17.01.2019
comment
Tabel pertama diubah - person Daniel; 17.01.2019
comment
masih salah nama kolom Testtime karena kolom ini tidak ada hanya sementara - person Daniel; 17.01.2019
comment
entahlah PILIH t1.TestID, t1.start, t1.end, DATEDIFF(hour,start,end) AS Testtime, t1.Testtime, SUM(t2.Testtime), 1000-SUM(t2.Testime) DARI Hasil Tes t1 GABUNG Hasil Tes pada t1.TestID ›= t2.TestID GROUP BY t1.TestID, t1.start, t1.end, t1.Testtime nama kolom yang salah Testtime dan nama kolom multible - person Daniel; 17.01.2019
comment
Masalah yang sama dalam kode Anda Sintaks Kolom Alias ​​hilang, Total Waktu Tes dan Waktu Istirahat tidak diketahui - person Daniel; 17.01.2019
comment
Sekarang saya mendapatkan hasil tetapi tidak seperti yang saya harapkan. Saya mengedit posting pertama saya dengan kode sebenarnya. Bisakah kamu memeriksanya. Terima kasih - person Daniel; 17.01.2019

arrow_upward
1
arrow_downward

Berdasarkan contoh data yang kami miliki, berikut ini hasil yang Anda dapatkan:

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;

Perhatikan bahwa Anda mungkin perlu menerapkan semacam PARTITION BY di klausa OVER, tapi saya tidak tahu apa yang perlu dilakukan berdasarkan data terbatas yang kami miliki.

person Larnu    schedule 17.01.2019
comment
Versi SQL saya tidak didukung Over() - person Daniel; 17.01.2019
comment
OVER diperkenalkan di SQL Server 2005 (iirc), @Daniel. Apakah Anda benar-benar menggunakan sesuatu sebelumnya? ROWS BETWEEN diperkenalkan dengan SQL Server 2012, namun, tahun 2008 hanya memiliki beberapa bulan perpanjangan dukungan yang tersisa, dan tahun 2005 sudah tidak lagi mendukung, jadi Anda seharusnya sudah memutakhirkannya sekarang. Kenapa belum? - person Larnu; 17.01.2019
comment
Versi: 9.0.5057 - person Daniel; 17.01.2019

arrow_upward
1
arrow_downward

Anda dapat mencoba ini:

 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

Jelaskan di postingan ini: Hitung Total Berjalan di SQL Server

HASIL :

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

Untuk pengguna pada Versi SQL Server sebelumnya Anda dapat memeriksa di posting ini: http://geekswithblogs.net/Rhames/archive/2008/10/28/calculator-running-totals-in-sql-server-2005---the-optimal.aspx

REVISI Untuk versi SQL sebelumnya :

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

HASIL :

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

Maaf tapi saya tidak tahu semua konfigurasi Anda. Bisakah Anda mencoba ini:

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
person Sanpas    schedule 17.01.2019
comment
Versi SQL saya tidak didukung Over() - person Daniel; 17.01.2019
comment
@Daniel Anda dapat memeriksa di tautan ini: Saya tahu ada berbagai cara untuk melakukan ini di SQL Server 2000/2005/2008. geekswithblogs.net/Rhames/archive/2008/10/28/ - person Sanpas; 17.01.2019
comment
Ya, sudah, saya memposting pembaruan di posting pertama saya. Soalnya baru sekarang hasilnya ditukar, bisa dicek? - person Daniel; 17.01.2019
comment
@Daniel dia memperbarui contoh saya dan menggunakan permintaan Anda :). beri tahu saya jika ini cara yang baik dan hasil yang Anda kecualikan. - person Sanpas; 17.01.2019
comment
Saya mendapatkan kesalahan: Sintaks Salah di dekat 'IS' Pernyataan IIF itu hanya ada di MDX - bahasa kueri untuk Layanan Analisis SQL Server - sisi pergudangan data dari SQL Server. T-SQL biasa tidak memiliki sumber Pernyataan IIF: stackoverflow.com/questions/4374907/ - person Daniel; 17.01.2019
comment
@Daniel saya punya pembaruan, maaf tetapi saya belum menyelesaikan instalasi Anda ... beri tahu saya jika kuerinya berfungsi. Catatan. baru saja mengubah t2.TestID ›= t1.TestID menjadi t2.TestID ‹= t1.TestID - person Sanpas; 17.01.2019
comment
tidak, itu hanya menjumlahkan dari setiap TestID, Testtimes ke angka yang tinggi sebagai Totaltime. Tabel Saya hanya sekedar contoh karena memiliki lebih banyak entri. Saya sudah mencoba seperti di postingan yang disebutkan untuk mengganti te IIF dengan Case When, Then, Else, End tetapi saya mendapatkan kesalahan. Solusinya adalah saya harus memilih terlebih dahulu baris dengan TesttableID = 1 dan kemudian menjumlahkannya. lihat posting 1. Tetapi satu perubahan kode yang saya perlukan adalah jika Testend adalah null jangan Keluarkan total waktu dan waktu istirahat, karena tes ini terbuka dan masih berjalan. - person Daniel; 18.01.2019