Reg_date red_time qty
20201211 075959 1
20201211 080001 2
20201211, 080102, 3
20201211, 195959, 4
20201211, 200001, 5
20201212, 073001, 5
The first column is the date (date) (month) (year)
The second column is the date when the minutes
The third column is the number of
Because the company is calculate by 8 to 20 points at night during the day the day day,
20 PM and at 8 o 'clock the next day to calculate the day night
From these data, I want to take such a data,
Date shift aggregate quantity,
20201210 night shift 1
20201211 day 9 (2 + 3 + 4)
20201211 night 10 (5 + 5)
Consult with the SQL statement under how to write,
CodePudding user response:
Select case when red_time between 080000 and 200000 then ref_date else end ref_date - 1, case when red_time between 080000 and 200000 then the day else shift end
, the sum (qty) qty
From the table
Group by case when red_time between 080000 and 200000 then ref_date else end ref_date - 1
, case when red_time between 080000 and 200000 then the day else shift end
Your date format transformation should be ok