CODEWITHSUNDEEP
grouping
user2628624
user2628624

Reputation: 19

grouping query by 30 min intervals

I am trying to get a query to group customer records by 30 min intervals while totaling the number of customers in the 30 min interval and totaling amount of money spent by each customer.

I'm new at this, and I am trying to take it head on.

I am using Microsoft SQL 2005.

Here is an example of what my main table looks like:

Cust_ID (Prime key), Date_time (Time of order), Price, and Sales_Tax

The intervals should be between 0-29 and 30-59 of each hour for a specific date.

I have figured out how to simply select the records by date range. but I am having trouble with the grouping function.

Upvotes: 1

Views: 421

Answers (1)

Curt
Curt

Reputation: 5722

    SELECT SUM(price)            AS price_sum,
       SUM(tax)                  AS tax_sum,
       COUNT(DISTINCT cust_id)   AS cust_count,
       CONVERT(varchar(13), time_of_order, 120) 
        + CASE 
             WHEN DATEPART(minute, time_of_order) < 30 THEN 'A'
             ELSE 'B'
          END           AS time_chunk_hash
  FROM Sales
       GROUP BY CONVERT(varchar(13), time_of_order, 120) 
                    + CASE 
                         WHEN DATEPART(minute, time_of_order) < 30 THEN 'A'
                         ELSE 'B'
                      END
       ORDER BY CONVERT(varchar(13), time_of_order, 120)  
                  +  CASE 
                        WHEN DATEPART(minute, time_of_order) < 30 THEN 'A'
                        ELSE 'B'
                     END

A working example is at: http://sqlfiddle.com/#!3/794ae/12

The computed time_chunk_hash is used to order and group the output, and will look like:

 2013-07-28 15A
 2013-07-29 15B

which would indicate, for example, the first half hour after 3pm on July 28, and then the second half.

Here's a more readable way of rendering the same query

SELECT SUM(price)                  AS price_sum,
       SUM(tax)                    AS tax_sum,
       COUNT(DISTINCT cust_id)     AS cust_cnt,
       time_chunk_hash
   FROM 
   (
      SELECT price,
             tax,
             cust_id,
             CONVERT(varchar(13), time_of_order, 120)
              + CASE 
                   WHEN DATEPART(minute, time_of_order) < 30 THEN 'A'
                   ELSE 'B'
               END           AS time_chunk_hash
        FROM Sales
   ) HASHED
     GROUP BY time_chunk_hash
     ORDER BY time_chunk_hash

A working example is at: http://sqlfiddle.com/#!3/794ae/11

Upvotes: 1

Related Questions