March 20, 2015 at 5:49 pm
We have a sorted data that looks like the first 3 columns below, and fourth colum is what I want to create)
Shift_start meal_break shift_endShift ID
1 0 0 1
0 0 0 1
0 0 1 1
1 0 0 2
0 0 0 2
0 0 1 2
I need to find a method to assign unique Shift IDS to rows that correspond to a single shift. For instance, the first shift would begin on the first row when shift_start flag is turned on, and end on the third row when shift_end flag is turned on.
Can I do this in SQL ?...some kind of grouping ?
Thank you
March 20, 2015 at 9:43 pm
A straight forward way of doing this is to calculate the running total on the first column, here is an example
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.TBL_SAMPLE_SHIFT') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_SHIFT;
CREATE TABLE dbo.TBL_SAMPLE_SHIFT
(
ROW_ID INT NOT NULL
,COL_A INT NOT NULL
,COL_B INT NOT NULL
,COL_C INT NOT NULL
)
INSERT INTO dbo.TBL_SAMPLE_SHIFT (ROW_ID,COL_A,COL_B,COL_C)
VALUES
( 1,1,0,0)
,( 2,0,0,0)
,( 3,0,0,1)
,( 4,1,0,0)
,( 5,0,0,0)
,( 6,0,0,1)
,( 7,1,0,0)
,( 8,0,0,0)
,( 9,0,0,1)
,(10,1,0,0)
,(11,0,0,0)
,(12,0,0,1)
;
SELECT
SS.COL_A
,SS.COL_B
,SS.COL_C
,SUM(COL_A) OVER
(
ORDER BY SS.ROW_ID
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS RT_COL
FROM dbo.TBL_SAMPLE_SHIFT SS;
Results
COL_A COL_B COL_C RT_COL
----------- ----------- ----------- -----------
1 0 0 1
0 0 0 1
0 0 1 1
1 0 0 2
0 0 0 2
0 0 1 2
1 0 0 3
0 0 0 3
0 0 1 3
1 0 0 4
0 0 0 4
0 0 1 4
March 23, 2015 at 9:50 am
Perfect, thank you!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply