I have the below data in an SQL Server(2017) table
POS_ID Term Code Status IsActive
TR 101 In Progress true
TR 102 In Progress true
TR 103 In Progress true
CA 151 In Progress true
CA 152 In Progress true
DA 161 In Progress true
The requirement is I want to iterate each row and compare the current row POS_ID with the previous row and if there is a different POS_ID found then I want to insert 2 rows in between in such a way that the 1st inserted row's status for that previous POS_ID will be In Progress and true and 2nd inserted row's status will be discontinued and false and term code of both these 2 inserted rows will be Term code of new POS_ID of current row Something like below example,
Expected output
POS_ID Term Code Status IsActive
TR 101 In Progress true
TR 102 In Progress true
TR 103 In Progress true
TR 151 In Progress true -- NEW ROW
TR 151 discontinue false -- NEW ROW
CA 151 In Progress true
CA 152 In Progress true
CA 161 In Progress true -- NEW ROW
CA 161 discontinue false -- NEW ROW
DA 161 In Progress true
I have tried using the Lead function to check the next value in the row for the column, but not sure how to do implement logic as mentioned above if values don't match insert 2 new rows
SELECT POS_ID AS currentvalue,
LEAD(POS_ID) OVER (ORDER BY uniqueid) AS NextValue
FROM dbo.input_Main_data
Create table and Insert script
Create table dbo.input_Main_data
( UniqueId bigint identity(1,1),
POS_ID varchar(10),
Term_code bigint,
Status varchar(50),
IsActive bit,
CONSTRAINT PK_input_Main_data_UniqueId PRIMARY KEY (UniqueId)
)
GO
INSERT INTO dbo.input_Main_data([POS_ID],[Term_code], [Status], [IsActive]) Values ('TR',101,'IN_PROGRESS',1), ('TR',102,'IN_PROGRESS',1), ('TR',103,'IN_PROGRESS',1),('CA',151,'IN_PROGRESS',1),('CA',152,'IN_PROGRESS',1),('DA',161,'IN_PROGRESS',1)
November 10, 2020 at 7:41 pm
Can you please provide sample DDL and INSERT scripts to create your sample data?
Can you also describe exactly how the dataset should be ordered?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 10, 2020 at 7:54 pm
Hello @Phil Parkin I have updated my post with Query to create a table with sample data.
And I have already posted expected output
November 10, 2020 at 8:04 pm
/*INSERT INTO dbo.input_Main_data ( ... )*/
SELECT new_rows.*
FROM dbo.input_Main_data iMd1
CROSS APPLY (
SELECT TOP (1) *
FROM dbo.input_Main_data iMd2
WHERE iMd2.Term_code > iMd1.Term_code
ORDER BY Term_code
) AS iMd2
CROSS APPLY (
SELECT iMd1.POS_ID, iMd2.Term_code, 'IN_PROGRESS' AS Status, 1 AS IsActive /*, ...*/
UNION ALL
SELECT iMd1.POS_ID, iMd2.Term_code, 'DISCONTINUE', 0 /*, ...*/
) AS new_rows
WHERE iMd2.POS_ID <> iMd1.POS_ID
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
My answer was posted on Stack Overflow. It assumes as little as possible about your data. The final result isn't in the same order as the example output because there doesn't appear to be an ORDER BY clause which re-produces it. But the INSERT statement should be good.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
November 11, 2020 at 1:42 am
>> I have the below data in a SQL Server 2017 table: <<
First of all, failure to post DDL is just plain rude as well as a mess. For those of us who now have to do your job for you. You also don't seem to know what a key is in RDBMS; it is a subset of columns that uniquely define each row. Since rows have no position or ordering, the concept of the current row and previous row are useless. What are we ordering these rows by?
There is no such thing as a generic "status" in RDBMS; it has to be the status of something. And since we don't even know the name of the table so hard to get exactly what it is. The use of bit flags is assembly language and has nothing to do with RDBMS. We are a predicate oriented language and you discover the status with predicates, not with assembly language flags.
CREATE TABLE Foobar
(pos_id CHAR(2) NOT NULL,
term_code CHAR(3) NOT NULL,
foo_status VARCHAR(12) NOT NULL
CHECK (foo_status IN ('Discontinue', 'in progress')),
PRIMARY KEY (pos_id, term_code));
>> The requirement is I want to iterate [ugh, in a set-oriented declarative language?] each row and compare the current [sic] row pos_id with the previous [sic] row and if there is a different pos_id found then I want to insert 2 rows in between in such a way that the 1st inserted row's status for that previous pos_id will be "In Progress" and true and 2nd inserted row's status will be discontinued and false and term code of both these 2 inserted rows will be Term code of new pos_id of the current row.
INSERT INTO Foobar
VALUES ('TR', '101', 'in progress'), ('TR', '102', 'in progress'),
('TR' , '103', 'in progress'), ('CA', '151', 'in progress'),
('CA', '152', 'in progress'), ('DA', '161', 'in progress');
Please post DDL and follow ANSI/ISO standards when asking for help.
November 11, 2020 at 12:31 pm
Hi there -- thank you for the points 🙂 It wasn't clear to me it's OK to use the uniqueid to order the results. Windowing functions are permitted in the ORDER BY clause as well as the SELECT list. Something like this
select pos_id, term_code, [status], isactive
from #input_Main_data md
order by first_value(uniqueid) over (partition by pos_id order by term_code),
pos_id, term_code, isactive desc;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
November 20, 2020 at 6:04 am
This was removed by the editor as SPAM
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply