Вы также можете использовать функцию parse_url()
из проекта php.js .
Код:
parse_url('http://username:password@hostname/path?arg=value#anchor');
Результат:
{
scheme: 'http',
host: 'hostname',
user: 'username',
pass: 'password',
path: '/path',
query: 'arg=value',
fragment: 'anchor'
}
Вы можете попробовать это:
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
Объясните в этом посте: Рассчитать промежуточную сумму в 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
[ 1110] Для пользователя в предыдущей версии SQL Server вы можете проверить по этому посту: http://geekswithblogs.net/Rhames/archive/2008/10/28/calculating-running-totals-in-sql-server-2005 --- the -timal.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
На основе данных выборки мы получаем результат:
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
, но я понятия не имею, что это должно быть основано на ограниченных данных, которые мы имеем.
Вы можете попробовать это:
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