Grouping in SQL Server plus by multiple parameters and joining results by a parameter

sql sum group by multiple columns
sql group by multiple columns
group by having sql
sql server grouping sets
group by sql
sql aggregate functions
sql count group by multiple columns
rollup, cube and grouping sets in sql server

I couldn't come up with a better title.

I have a table with this structure:

id | timestamp | barcode

I need to get the count for each product for each day and shift with one query.

Product is left(barcode, 9), date should come from the timestamp, which is datetime and shift should come from timestamp as well. Shifts are as follows:

Shift 1 : 06:00:00 to 14:29:59
Shift 2 : 14:30:00 to 22:59:59
Shift 3 : 23:00:00 to 05:59:59

So I basically need a result like:

date       | shift | item number | count
12.02.2019 | 1     | 827384950   | 32

So Far I have this:

select cast(timestamp as date) as date,
   (case when convert(time, timestamp) >= '06:00:00' and
              convert(time, timestamp) < '14:30:00'
         then 1
         when convert(time, timestamp) >= '14:30:00' and
              convert(time, timestamp) < '23:00:00'
         then 2
         else 3
    end) as shift,
   left(barcode, 9) as item_number,
   count(*)
from t
group by cast(timestamp as date),
     (case when convert(time, timestamp) >= '06:00:00' and
                convert(time, timestamp) < '14:30:00'
           then 1
           when convert(time, timestamp) >= '14:30:00' and
                convert(time, timestamp) < '23:00:00'
           then 2
           else 3
      end),
     left(barcode, 9)
order by date, shift, item_number;

It does the job, but the problem is this:

Shift 3 is actually taking part in two days, since it is from 23:00 on one day until 6 on the next day. This code splits shift 3 to two parts - one for each day.

I need to group it so Shift 3 shows for the day it started and then counts it's item count until it finishes on the next day.

I like to keep complicated logic out of GROUP BY clauses, this way when someone else comes to read the code they can clearly grasp the logic of the grouping. I instead separate out the logic into a SELECT statement of a CTE, and use the resulting columns in a simple readable query.

;WITH cte AS
(
    SELECT id, LEFT(barcode, 9) item_number,
        CONVERT(DATE, CASE WHEN CONVERT(TIME, timestamp) <'06:00:00' THEN DATEADD(DAY, -1, timestamp) ELSE timestamp END) AS date,
        CASE WHEN CONVERT(TIME, timestamp) >= '06:00:00' AND
          CONVERT(TIME, timestamp) < '14:30:00'
             THEN 1
             WHEN CONVERT(TIME, timestamp) >= '14:30:00' AND
                  CONVERT(TIME, timestamp) < '23:00:00'
             THEN 2
             ELSE 3
        END AS shift 
    FROM Table t
)

SELECT date, shift, item_number, count(*) as count
FROM cte
GROUP BY date, shift, item_number

GROUP BY (Transact-SQL), A SELECT statement clause that divides the query result into groups of rows Non-ISO-Compliant Syntax for SQL Server and Azure SQL Database GROUP BY [ ALL ] Groups the SELECT statement results according to the values in a to combine multiple GROUP BY clauses into one GROUP BY clause. GROUPING (Transact-SQL) 12/03/2019; 2 minutes to read +3; In this article. Applies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics. Indicates whether a specified column expression in a GROUP BY list is aggregated or not. GROUPING returns 1 for aggregated or 0 for not aggregated in

Please try this:

SELECT CASE WHEN a.shift = 3 AND a.[time] < '06:00' THEN DATEADD(DAY,-1,a.[date]) ELSE a.[date] END AS [date]
    ,a.shift,a.item_number,COUNT(*)
FROM (
    SELECT TRY_CONVERT(DATE,t.[timestamp]) AS [date]
        ,TRY_CONVERT(TIME,t.timestamp) AS [time]
        ,CASE 
            WHEN TRY_CONVERT(TIME,t.timestamp) >= '23:00' OR TRY_CONVERT(TIME,t.timestamp) < '06:00' THEN 3
            WHEN TRY_CONVERT(TIME,t.timestamp) >= '14:30' THEN 2
            ELSE 1
        END AS [shift]
        ,LEFT(t.barcode,9) AS [item_number]
    FROM [YourTableName] t
) a
GROUP BY CASE WHEN a.shift = 3 AND a.[time] < '06:00' THEN DATEADD(DAY,-1,a.[date]) ELSE a.[date] END
    ,a.shift,a.item_number
;

SQL: GROUP BY Clause, This SQL tutorial explains how to use the SQL GROUP BY clause with syntax and data across multiple records and group the results by one or more columns. Fortunately, SQL offers a number of clauses and operators for sorting, grouping, and summarizing. The following tips will help you discern when to sort, when to group, and when and how to summarize.

If you have some sample data to test with, that would be great!

Essentially, I am shifting the timestamp back 6 hours to avoid the confusion of the cross-day 3rd shift. It pulls the full 3rd shift to the date in which the shift starts while not losing the date of the 1st shift (since 00:00 counts for the date).

SELECT TheDay = TRY_CONVERT(DATE, DATEADD(HOUR, -6, t.[timestamp])),
       Shift = CASE 
                 WHEN TRY_CONVERT(TIME, DATEADD(HOUR, -6, t.[timestamp])) < '08:30'
                   THEN 1
                 WHEN TRY_CONVERT(TIME, DATEADD(HOUR, -6, t.[timestamp])) < '17:00'
                   THEN 2
                 ELSE 3
               END,
      Product = LEFT(t.[barcode], 9),
      ItemCount = COUNT(*)
  FROM [TableName] t
 GROUP BY TRY_CONVERT(DATE, DATEADD(HOUR, -6, t.[timestamp])), 
          CASE 
             WHEN TRY_CONVERT(TIME, DATEADD(HOUR, -6, t.[timestamp])) < '08:30'
               THEN 1
             WHEN TRY_CONVERT(TIME, DATEADD(HOUR, -6, t.[timestamp])) < '17:00'
               THEN 2
             ELSE 3
          END,
          LEFT(t.[barcode], 9)

@elizabk makes a good point:

;WITH shiftedData (TheDay, Shift, Product) AS (
    SELECT TheDay = TRY_CONVERT(DATE, DATEADD(HOUR, -6, t.[timestamp])),
           Shift = CASE 
                     WHEN TRY_CONVERT(TIME, DATEADD(HOUR, -6, t.[timestamp])) < '08:30'
                        THEN 1
                     WHEN TRY_CONVERT(TIME, DATEADD(HOUR, -6, t.[timestamp])) < '17:00'
                        THEN 2
                     ELSE 3
                   END,
          Product = LEFT(t.[barcode], 9)
      FROM [TableName] t
)
SELECT TheDay, Shift, Product, ProductCount = COUNT(*)
  FROM shiftedData
 GROUP BY TheDay, Shift, Product 

SQL Server GROUPING SETS Explained Clearly By Practical , 0 ) sales INTO sales.sales_summary FROM sales.order_items i INNER JOIN production.products p By definition, a grouping set is a group of columns by which you group. The four queries above return four result sets with four grouping sets: The GROUPING SETS defines multiple grouping sets in the same query. In my earlier article, I developed a solution How to use multiple values for IN clause using same parameter (SQL Server). Recently, I received a query, inquiring how to pass multiple values through one parameter in a stored procedure. Given below are the two different Methods : Method 1 : Using XQuery

SQL Server SUM() Function By Practical Examples, The SQL Server SUM() function is an aggregate function that calculates the sum of all ALL instructs the SUM() function to return the sum of all values including duplicates. SQL Server SUM function with GROUP BY and JOIN example INSERT � INSERT Multiple Rows � INSERT INTO SELECT � UPDATE � UPDATE JOIN� Starting with SQL Server 2017, you can now make your query results appear as a list. This means you can have your result set appear as a comma-separated list, a space-separated list, or whatever separator you choose to use. While it’s true that you could achieve this same effect prior to SQL Server 2017, it was a bit fiddly.

SQL SUM() with GROUP by, SQL SUM() with GROUP by: SUM is used with a GROUP BY clause. clause is required when using an aggregate function along with regular column data, otherwise the result will be a mismatch. SQL SUM() using multiple columns with group by Next: SUM and COUNT Using Variable and inner join. Summary: this tutorial shows you how to use the SQL UNION to combine two or more result sets from multiple queries and explains the difference between UNION and UNION ALL. Introduction to SQL UNION operator. The UNION operator combines result sets of two or more SELECT statements into a single result set. The following statement illustrates how

group by and having clauses, These summary values appear as columns in the results, one for each group. A Transact-SQL extension allows grouping by an aggregate-free expression When Adaptive Server optimizes queries, it evaluates the search conditions but displays results for groups matching the multiple conditions in the having clause: Where [MyServer] and [Test] are the actual server and database names where my Stored Procedure lies. I then add my date values back in: Pressing Invoke I get: Running SQL Server Profile I can see that it is trying to run the following script: EXEC dbo.DateParm @startdate = '01/01/2017', @enddate = '31/12/2017'

Comments
  • Good call on the CTE - makes it much cleaner!
  • This produces a result in which every day has multiple Shift 1, Shift 2 and Shift 3s. I added an order by date at the end, could it be the reason?
  • Could you please provide sample data