1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46
|
CREATE PROCEDURE [dbo].[ReportingGTS2]
@Date1 varchar(10) = '',
@Date2 varchar(10) = ''
AS
BEGIN
DECLARE @Heure1 int
DECLARE @Heure2 int
WHILE (@Date1 <= @Date2)
BEGIN
SET @Heure1 = 0
SET @Heure2 = 1
WHILE (@Heure2 <= 23)
BEGIN
CREATE TABLE #TempTable
(
"Date_1" Date,
"HourMin" int,
"HourMax" int,
"TotalProcess" int,
"TotalDuration" int
)
INSERT INTO #TempTable
([Date_1], [HourMin], [HourMax], [TotalProcess],[TotalDuration])
VALUES
(
[@Date1], [@Heure1], [@Heure2],
[SELECT SUM(NB) FROM dbo.MaTable
WHERE CONVERT(varchar(10), Start_Date, 103) = @Date1
AND DATEPART(HH,Start_Date) >= @Heure1
AND DATEPART(HH,Start_Date) <= @Heure2],
[SELECT SUM(Exec_Duration) FROM dbo.MaTable
WHERE CONVERT(varchar(10), Start_Date, 103) = @Date1
AND DATEPART(HH,Start_Date) >= @Heure1
AND DATEPART(HH,Start_Date) <= @Heure2]
)
SET @Heure1 = @Heure1 + 1
SET @Heure2 = @Heure1 + 1
END
SET @Date1 = @Date1 + 1
END
SELECT * FROM #TempTable
END
GO |
Partager