Counts on Date Range and Individual Days

Question

I'm having to re-write a project that was done using a combination of SQL queries and Query-of-Queries in ColdFusion. There were dozens of queries referencing the original SQL Query results set, but it wasn't abstracted to work for different events. So I would like to improve it by moving most of the counting into SQL. I got the first 6 counts they need working (not sure if in the most optimal way). But, in addition to those, I need to be able to do a break down not just on the overall date range, but also for each individual day in that date range for the unique counts.

So far the query is:

 SELECT Count(CASE
           WHEN type IN ( 1, 3, 4, 5, 9 ) THEN barcode
           ELSE NULL
         END)              AS total_scans,
   Count(CASE
           WHEN type IN ( 2, 8 ) THEN barcode
           ELSE NULL
         END)              AS total_creds,
   Count(barcode)          AS total_scans,
   Count(DISTINCT CASE
                    WHEN type IN ( 1, 3, 4, 5, 9 ) THEN barcode
                    ELSE NULL
                  END)     AS unique_scans,
   Count(DISTINCT CASE
                    WHEN type IN ( 2, 8 ) THEN barcode
                    ELSE NULL
                  END)     AS unique_creds,
   Count(DISTINCT barcode) AS unique_scans 
FROM   (SELECT c.id,
           a.barcode,
           d.type,
           c.location,
           Datepart(mm, a.scan_time)     AS scan_month,
           Datepart(dd, a.scan_time)     AS scan_day,
           Datepart(hour, a.scan_time)   AS scan_hour,
           Datepart(minute, a.scan_time) AS scan_min
    FROM   [scan_11pc_gate_entries] AS a
           INNER JOIN scan_units AS b
                   ON a.scanner = b.id
           INNER JOIN scan_gates AS c
                   ON b.gate = c.id
           INNER JOIN [scan_11pc_gate_allbarcodes] AS d
                   ON a.barcode = d.barcode
    WHERE  ( c.id IN (SELECT id
                      FROM   scan_gates
                      WHERE  ( event_id = 21 )) )
           AND ( a.valid IN ( 1, 8 ) )
           AND a.scan_time >= '20110808'
           AND a.scan_time <= '20110814') data  

Answer 1

Well I see plenty of other room for improvement / optimization here, but to satisfy the immediate requirement:

 SELECT d, Count(CASE WHEN type IN ( 1, 3, 4, 5, 9 ) THEN barcode
           ELSE NULL END) AS total_scans,
   Count(CASE WHEN type IN ( 2, 8 ) THEN barcode
           ELSE NULL END) AS total_creds,
   Count(barcode)         AS total_scans,
   Count(DISTINCT CASE WHEN type IN ( 1, 3, 4, 5, 9 ) THEN barcode
           ELSE NULL END) AS unique_scans,
   Count(DISTINCT CASE WHEN type IN ( 2, 8 ) THEN barcode
           ELSE NULL END) AS unique_creds,
   Count(DISTINCT barcode) AS unique_scans 
FROM   (SELECT c.id, -- is this column necessary?
           a.barcode,
           d.type,
           c.location, -- is this column necessary?
           d = DATEADD(DAY, DATEDIFF(DAY, 0, a.scan_time), 0)
    FROM   [scan_11pc_gate_entries] AS a
           INNER JOIN scan_units AS b
                   ON a.scanner = b.id
           INNER JOIN scan_gates AS c
                   ON b.gate = c.id
                  AND c.event_id = 21 -- join criteria, 
                     -- shouldn't be an extra IN clause
           INNER JOIN [scan_11pc_gate_allbarcodes] AS d
                   ON a.barcode = d.barcode
    WHERE  ( a.valid IN ( 1, 8 ) )
           AND a.scan_time >= '20110808'
           AND a.scan_time <= '20110814') data  
GROUP BY d
ORDER BY d;

Note that >= and <= is the same as BETWEEN, and unless scan_time is a DATE column (or guaranteed to always be at midnight), the approach you're using isn't safe. Better to say:

           AND a.scan_time >= '20110808'
           AND a.scan_time <  '20110815'

More info here:

• What do BETWEEN and the devil have in common?

---

EDIT

Understanding that this is based on the information I've pieced together from comments and questions, which is far from a complete picture of your environment and workload, an indexed view you may find useful would be:

CREATE VIEW dbo.myview 
WITH SCHEMABINDING
AS
  SELECT c.event_id,
         a.barcode,
         [type] = CASE WHEN d.[type] IN (2,8) THEN 'c' ELSE 's' END,
         scan_date = DATEADD(DAY, DATEDIFF(DAY, 0, a.scan_time), 0),
         total = COUNT_BIG(*)
    FROM dbo.scan_11pc_gate_entries AS a
    INNER JOIN dbo.can_units AS b
            ON a.scanner = b.id
    INNER JOIN dbo.scan_gates AS c
            ON b.gate = c.id
    INNER JOIN dbo.scan_11pc_gate_allbarcodes AS d
            ON a.barcode = d.barcode
    WHERE (a.valid IN (1,8))
      AND d.[type] IN (2,3,4,5,8,9)
    GROUP BY 
      c.event_id,
      a.barcode,
      CASE WHEN d.[type] IN (2,8) THEN 'c' ELSE 's' END,
      DATEADD(DAY, DATEDIFF(DAY, 0, a.scan_time), 0);
GO
CREATE UNIQUE CLUSTERED INDEX x 
  ON dbo.myview(event_id, barcode, [type], scan_date);

Now you can write a query that does something like this:

SELECT [date] = CONVERT(CHAR(8), scan_date, 112), 
  SUM(CASE WHEN [type] = 's' THEN total ELSE 0 END) AS total_scans,
  SUM(CASE WHEN [type] = 'c' THEN total ELSE 0 END) AS total_creds,
  COUNT(CASE WHEN [type] = 's' THEN 1 END) AS unique_scans,
  COUNT(CASE WHEN [type] = 'c' THEN 1 END) AS unique_creds
FROM dbo.myview WITH (NOEXPAND) -- in case STD Edition
WHERE event_id = 21
  AND scan_date BETWEEN '20110808' AND '20110814'
GROUP BY scan_date
UNION ALL
SELECT [date] = 'weekly', 
  SUM(CASE WHEN [type] = 's' THEN total ELSE 0 END) AS total_scans,
  SUM(CASE WHEN [type] = 'c' THEN total ELSE 0 END) AS total_creds,
  COUNT(DISTINCT CASE WHEN [type] = 's' THEN barcode END) AS unique_scans,
  COUNT(DISTINCT CASE WHEN [type] = 'c' THEN barcode END) AS unique_creds
FROM dbo.myview WITH (NOEXPAND) -- in case STD Edition
WHERE event_id = 21
  AND scan_date BETWEEN '20110808' AND '20110814'
ORDER BY [date];

This is all completely untested as your schema is a little bit cumbersome to try and create a complete repro of your system, but hopefully this gives you a general idea to work from...