How to slit value from one row to multiple rows

Question

How to slit value from one row to multiple rows

phambao

SSC Enthusiast

Points: 172
More actions
January 22, 2022 at 12:38 am

#3975935

Hi all,
I would like to know how I can slit value from one row to multiple rows based on conditions.
I would like to track downtime of one machine during a week and performance of each operator in terms of troubleshooting skills.
Thing is we have 4 shifts A,B,C,D in order and working time start 7am --> 7pm and vice versa
it is quite tricky that when operation from shift A logged the tool status down at 17 22/1/2022 and then status of tool is logged as Up at
7 24/1/2022, so all duration down time is accounted for operator shift A around 3 days. It is not right since operator A only works 12h/day.
Do you know any function in SQL which can help user to slit value of one row to multiple rows based on condition
Sincerely,

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

Without some actual test data, all I can say is STRING_SPLIT() and CASE.

If you want a coded example, please read the article at the first link in my signature line below for one way to help you help people help you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ed B Hall of Fame Points: 3617 More actions · Answer 2

I don't think you need a function to split the table. I think you need to work with your data to create two tables, shifts and events, containing start and end times, then you need to calculate the amount of time that the spans overlap between tables.

The first table needs to contain the start time and the end time of the system being down (the event). It appears that you log the system down event time and the system back up event time in the same column and you need to convert that to a span. I have made assumptions about the simplicity of your data, but the LEAD function can return the next row for the same 'System' and assuming that next row is when the system was logged as back online, that becomes the end time. In reality I doubt it it that simple, but the principle is to convert the two individual events into a datetime span.

DECLARE @Event TABLE(EventID INT, SystemID INT, SystemStatus CHAR(1), LogTime DATETIME)
INSERT @Event VALUES(1, 1, 'D', '2022-01-22 09:00:00'),
(2, 1, 'U', '2022-01-24 21:00:00')

DECLARE @EventDetail TABLE (SystemID INT, SystemStatus CHAR(1), EventStart DATETIME, EventEnd DATETIME)
INSERT @EventDetail  (SystemID, SystemStatus, EventStart, EventEnd)
SELECTa.SystemID, a.SystemStatus, a.LogTime, a.ResolveTime
FROM (
SELECTSystemID, SystemStatus, LogTime,
ISNULL(LEAD(LogTime, 1) OVER (PARTITION BY SystemID ORDER BY LogTime), GETDATE()) AS ResolveTime
FROM@Event
     ) AS a
WHEREa.SystemStatus = 'D'

SELECT *
FROM @EventDetail

SystemID  SystemStatusEventStart        EventEnd
1  D        2022-01-22 09:00:00.0002022-01-24 21:00:00.000

I don't understand your description of having four 12 hour shifts per day, so I am going to assume there are two 12 hour shifts per day. If you create a table containing shift information for every day during the reporting period, you can compare it to the logged event table and calculate the amount of time each shift overlapped with the event. This is the time the shift operator spent on the event.

DECLARE @Shift TABLE (ShiftID INT,  ShiftCode CHAR(1), ShiftStartTime DATETIME, ShiftEndTime DATETIME)
INSERT @Shift VALUES 
(1, 'A', '2022-01-21 07:00:00', '2022-01-21 19:00:00'), 
(2, 'B', '2022-01-21 19:00:00', '2022-01-22 07:00:00'),
(3, 'A', '2022-01-22 07:00:00', '2022-01-22 19:00:00'),
(4, 'B', '2022-01-22 19:00:00', '2022-01-23 07:00:00'),
(5, 'A', '2022-01-23 07:00:00', '2022-01-23 19:00:00'),
(6, 'B', '2022-01-23 19:00:00', '2022-01-24 07:00:00'),
(7, 'A', '2022-01-24 07:00:00', '2022-01-24 19:00:00'),
(8, 'B', '2022-01-24 19:00:00', '2022-01-25 07:00:00')



SELECT  ShiftID, ShiftCode, 
CONVERT(DATE, ShiftStartTime) AS ShiftStartDate,
DATEDIFF(minute,
(IIF(ShiftStartTime > EventStart, ShiftStartTime, EventStart)),
(IIF(ShiftEndTime < EventEnd, ShiftEndTime, EventEnd))) AS MinutesWorked
FROM (
SELECT *
FROM @Shift AS a
CROSS JOIN @EventDetail AS b
WHERE (a.ShiftStartTime >= b.EventStart AND a.ShiftStartTime < EventEnd)
OR (a.ShiftEndTime >= b.EventStart AND a.ShiftEndTime < EventEnd)
    ) AS x

ShiftIDShiftCode  ShiftStartDate     MinutesWorked
3A   2022-01-22       600
4B   2022-01-22       720
5A   2022-01-23       720
6B   2022-01-23       720
7A   2022-01-24       720
8B   2022-01-24       120

Shifts that overlap with the event will have a shift start time or a shift end time that is between the event start time and the event end time. We cannot use BETWEEN because the shift end time = next shift start time, so we use >= and <.

Time worked on the event during the shift is the difference between the effective start time and the effective end time. This is the time the shift started working on the event and the time the shift stopped working on the event. Effective start time is the greater of shift start time and event start time. Effective end time is the lesser of shift end time and event end time. If you combine the sample code segments, it should work to show a simple of example of what I think you need to do.