CODEWITHSUNDEEP
sql-servert-sqlgaps-and-islands
iPath ツ
iPath ツ

Reputation: 2488

Adding a new row before other records that have specific value

I have the following table:

Id  | DateTime            | State
1   | 2018-07-16 10:00:00 | 0
2   | 2018-07-16 10:15:34 | 1
3   | 2018-07-16 10:21:12 | 0
4   | 2018-07-16 10:32:45 | 1
5   | 2018-07-16 10:44:05 | 0

I need a query (t-sql) that inserts a new row before each row that has State == 0. The new row should have DateTime = TheRowOfInterest(datetime) - 1 second and State = 1.

TheRowOfInterest is every row that has State == 0.

Important constraint: the new row should be added only if the previous record had State == 1.

The resulting table looks like this:

DateTime            | State
2018-07-16 10:00:00 | 0
2018-07-16 10:15:34 | 1
2018-07-16 10:21:11 | 1      <<<
2018-07-16 10:21:12 | 0
2018-07-16 10:32:45 | 1
2018-07-16 10:44:04 | 1      <<<
2018-07-16 10:44:05 | 0

I know I need to use window-ing functions but haven't got any far so any suggestions are welcome.

More info:

Here are the Create statements and sample data

CREATE TABLE [dbo].[SwitchSeries](
    [Id] [int] NOT NULL,
    [DateTime] [datetime2](7) NOT NULL,
    [State] [tinyint] NOT NULL
)

Filled with sample data:

INSERT INTO SwitchSeries VALUES
    (1, '2018-07-16 10:00:00', 0),
    (2, '2018-07-16 10:15:34', 1),
    (3, '2018-07-16 10:21:12', 0),
    (4, '2018-07-16 10:32:45', 1),
    (5, '2018-07-16 10:44:05', 0)

Upvotes: 1

Views: 52

Answers (2)

Noxthron
Noxthron

Reputation: 492

if you know that identity values are sequential, then you can simply join the table with itself like this:

select DateAdd(ss, -1, s2.DateTime), 1
from SwitchSeries s1 inner join SwitchSeries s2 on s1.Id = s2.Id - 1
where s1.State = 1 and s2.State = 0;

you can also check the fiddle here

SQL Fiddle

Upvotes: 1

Thom A
Thom A

Reputation: 95659

This was quite a simple solution. You haven't posted what you've tried yet, which is really important when posting a question; it means the volunteers know you've put some effort in and you aren't expecting you to do their work for them.

Anyway, as I said, this was achieved with the use of a CTE, LAG, and UNION ALL:

WITH CTE AS(
    SELECT *,
           LAG([State]) OVER (ORDER BY [datetime] ASC) AS PreviousState
    FROM dbo.SwitchSeries)
SELECT [DateTime], [State]
FROM CTE
UNION ALL
SELECT DATEADD(SECOND,-1,[DateTime]), 1
FROM CTE
WHERE [State] = 0
  AND PreviousState = 1
ORDER BY [DateTime];

I, however, totally agree with WhatsThePoint's comment; don't use keywords as column names. Both state and datetime are keywords in SQL Server.

Upvotes: 1

Related Questions