w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Counting the number of transaction hourly for the past 8 hours
DECLARE @LastHour AS datetime;
SET @LastHour = DATEADD(hour, DATEDIFF(hour, 0, GETDATE()), 0);

WITH HourOffsets AS (
            SELECT -1 AS Offset
  UNION ALL SELECT -2
  UNION ALL SELECT -3
  UNION ALL SELECT -4
  UNION ALL SELECT -5
  UNION ALL SELECT -6
  UNION ALL SELECT -7
  UNION ALL SELECT -8
)
,Periods AS (
    SELECT DATEADD(hour, Offset, @LastHour) AS PeriodStartTime
          ,DATEADD(hour, Offset + 1, @LastHour) AS PeriodEndTime
    FROM HourOffsets
)
,Transactions AS (
    SELECT Periods.PeriodStartTime
          ,COUNT(*) AS TransactionCount
    FROM Periods
         LEFT JOIN TTID
             ON TTID.DateTimeStart >= Periods.PeriodStartTime
                AND TTID.DateTimeStart < Periods.PeriodEndTime
         LEFT JOIN Trxn
             ON TTID.TTID = (rtrim(Trxn.TTID)+ltrim(Trxn.STV))
             AND Trxn.Status='ok'
    WHERE TTID.Status IS NULL
          OR TTID.Status = 'A'
    GROUP BY Periods.PeriodStartTime
)
SELECT CONVERT(varchar(10), PeriodStartTime, 103)
      ,CONVERT(varchar(2), DATEPART(hour, Periods.PeriodStartTime)) + '-'
       CONVERT(varchar(2), DATEPART(hour, Periods.PeriodEndTime))
      ,TransactionCount
FROM Transactions




© Copyright 2018 w3hello.com Publishing Limited. All rights reserved.