CODEWITHSUNDEEP
sql-server
OurBG
OurBG

Reputation: 597

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

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.

Upvotes: 1

Views: 30

Answers (3)

Forty3
Forty3

Reputation: 2229

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

Upvotes: 0

elizabk
elizabk

Reputation: 480

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

Upvotes: 3

Vitaly Borisov
Vitaly Borisov

Reputation: 1193

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
;

Upvotes: 0

Related Questions