Ga naar inhoud

Scansbyhour

Report ScansByHour

  • Filename: ScansByHour
  • Title: Scans by hour
  • Sample: form140 (unit140)

Synopsis

Report per person the scans per hour.

SQL

SELECT
  A.UserName ,
  SUM(CASE WHEN datepart(hour, creationdate)=6 THEN 1 ELSE 0 END) AS X6,
  SUM(CASE WHEN datepart(hour, creationdate)=7 THEN 1 ELSE 0 END) AS X7,
  SUM(CASE WHEN datepart(hour, creationdate)=8 THEN 1 ELSE 0 END) AS X8,
  SUM(CASE WHEN datepart(hour, creationdate)=9 THEN 1 ELSE 0 END) AS X9,
  SUM(CASE WHEN datepart(hour, creationdate)=10 THEN 1 ELSE 0 END) AS X10,
  SUM(CASE WHEN datepart(hour, creationdate)=11 THEN 1 ELSE 0 END) AS X11,
  SUM(CASE WHEN datepart(hour, creationdate)=12 THEN 1 ELSE 0 END) AS X12,
  SUM(CASE WHEN datepart(hour, creationdate)=13 THEN 1 ELSE 0 END) AS X13,
  SUM(CASE WHEN datepart(hour, creationdate)=14 THEN 1 ELSE 0 END) AS X14,
  SUM(CASE WHEN datepart(hour, creationdate)=15 THEN 1 ELSE 0 END) AS X15,
  SUM(CASE WHEN datepart(hour, creationdate)=16 THEN 1 ELSE 0 END) AS X16,
  SUM(CASE WHEN datepart(hour, creationdate)=17 THEN 1 ELSE 0 END) AS X17,
  SUM(CASE WHEN datepart(hour, creationdate)=18 THEN 1 ELSE 0 END) AS X18,
  SUM(CASE WHEN datepart(hour, creationdate)=19 THEN 1 ELSE 0 END) AS X19,
  SUM(CASE WHEN datepart(hour, creationdate)<6 THEN 1 ELSE 0 END) AS XX0,
  SUM(CASE WHEN datepart(hour, creationdate)>19 THEN 1 ELSE 0 END) AS XX9
FROM
  SCANHISTORY A
WHERE
  (A.CREATIONDATE>=:StartDate)
  AND A.CREATIONDATE<=:EndDate)
GROUP BY
  A.UserName

Parameters

  • StartDate Start of period of scans.
  • EndDate End of period of scans.

Accessible in forms