Hello,
I have the following table.
Is it possible to be able to get the following rest set from it?
I want OPEN_DTS to be SET_DT if REOPEN_DTS is 1900-01-01 and there is no previous non 1900-01-01 REOPEN_DTS row value.
And When If REOPEN_DTS is NOT 1900-01-01, OPEN_DTS should be same as REOPEN_DTS, and Close_Date should be the next highest Close_date per C-number, but if the next Close date is 1900-01-01 then it should be 12-31-9000.
This is what the results should look like:
-- Table script and data
CREATE TABLE [dbo].Table1 ON [PRIMARY]
GO
INSERT [dbo].[Table1] ([RowRank], [C_Number], [SETUP_DT], [REOPEN_DTS], [Closed_Date])
VALUES (1, 755, CAST(N'2022-01-19T00:00:00.000' AS DateTime), CAST(N'1900-01-01T00:00:00.000' AS DateTime), CAST(N'1900-01-01T00:00:00.000' AS DateTime))
INSERT [dbo].[Table1] ([RowRank], [C_Number], [SETUP_DT], [REOPEN_DTS], [Closed_Date])
VALUES (2, 755, CAST(N'2022-01-19T00:00:00.000' AS DateTime), CAST(N'1900-01-01T00:00:00.000' AS DateTime), CAST(N'1900-01-01T00:00:00.000' AS DateTime))
INSERT [dbo].[Table1] ([RowRank], [C_Number], [SETUP_DT], [REOPEN_DTS], [Closed_Date])
VALUES (3, 755, CAST(N'2022-01-19T00:00:00.000' AS DateTime), CAST(N'1900-01-01T00:00:00.000' AS DateTime), CAST(N'1900-01-01T00:00:00.000' AS DateTime))
INSERT [dbo].[Table1] ([RowRank], [C_Number], [SETUP_DT], [REOPEN_DTS], [Closed_Date])
VALUES (4, 755, CAST(N'2022-01-19T00:00:00.000' AS DateTime), CAST(N'1900-01-01T00:00:00.000' AS DateTime), CAST(N'2022-06-09T10:15:28.000' AS DateTime))
INSERT [dbo].[Table1] ([RowRank], [C_Number], [SETUP_DT], [REOPEN_DTS], [Closed_Date])
VALUES (5, 755, CAST(N'2022-01-19T00:00:00.000' AS DateTime), CAST(N'2022-08-11T12:12:52.000' AS DateTime), CAST(N'1900-01-01T00:00:00.000' AS DateTime))
INSERT [dbo].[Table1] ([RowRank], [C_Number], [SETUP_DT], [REOPEN_DTS], [Closed_Date])
VALUES (1, 450, CAST(N'2022-04-26T00:00:00.000' AS DateTime), CAST(N'1900-01-01T00:00:00.000' AS DateTime), CAST(N'1900-01-01T00:00:00.000' AS DateTime))
INSERT [dbo].[Table1] ([RowRank], [C_Number], [SETUP_DT], [REOPEN_DTS], [Closed_Date])
VALUES (2, 450, CAST(N'2022-04-26T00:00:00.000' AS DateTime), CAST(N'1900-01-01T00:00:00.000' AS DateTime), CAST(N'1900-01-01T00:00:00.000' AS DateTime))
INSERT [dbo].[Table1] ([RowRank], [C_Number], [SETUP_DT], [REOPEN_DTS], [Closed_Date])
VALUES (3, 450, CAST(N'2022-04-26T00:00:00.000' AS DateTime), CAST(N'1900-01-01T00:00:00.000' AS DateTime), CAST(N'1900-01-01T00:00:00.000' AS DateTime))
INSERT [dbo].[Table1] ([RowRank], [C_Number], [SETUP_DT], [REOPEN_DTS], [Closed_Date])
VALUES (4, 450, CAST(N'2022-04-26T00:00:00.000' AS DateTime), CAST(N'1900-01-01T00:00:00.000' AS DateTime), CAST(N'2022-05-20T11:35:04.000' AS DateTime))
INSERT [dbo].[Table1] ([RowRank], [C_Number], [SETUP_DT], [REOPEN_DTS], [Closed_Date])
VALUES (5, 450, CAST(N'2022-04-26T00:00:00.000' AS DateTime), CAST(N'2022-06-03T17:22:48.000' AS DateTime), CAST(N'1900-01-01T00:00:00.000' AS DateTime))
INSERT [dbo].[Table1] ([RowRank], [C_Number], [SETUP_DT], [REOPEN_DTS], [Closed_Date])
VALUES (6, 450, CAST(N'2022-04-26T00:00:00.000' AS DateTime), CAST(N'2022-06-03T17:22:48.000' AS DateTime), CAST(N'1900-01-01T00:00:00.000' AS DateTime))
INSERT [dbo].[Table1] ([RowRank], [C_Number], [SETUP_DT], [REOPEN_DTS], [Closed_Date])
VALUES (7, 450, CAST(N'2022-04-26T00:00:00.000' AS DateTime), CAST(N'1900-01-01T00:00:00.000' AS DateTime), CAST(N'2022-06-17T15:17:21.000' AS DateTime))
INSERT [dbo].[Table1] ([RowRank], [C_Number], [SETUP_DT], [REOPEN_DTS], [Closed_Date])
VALUES (1, 544, CAST(N'2022-06-03T00:00:00.000' AS DateTime), CAST(N'1900-01-01T00:00:00.000' AS DateTime), CAST(N'1900-01-01T00:00:00.000' AS DateTime))
INSERT [dbo].[Table1] ([RowRank], [C_Number], [SETUP_DT], [REOPEN_DTS], [Closed_Date])
VALUES (2, 755, CAST(N'2022-06-03T00:00:00.000' AS DateTime), CAST(N'1900-01-01T00:00:00.000' AS DateTime), CAST(N'1900-01-01T00:00:00.000' AS DateTime))
INSERT [dbo].[Table1] ([RowRank], [C_Number], [SETUP_DT], [REOPEN_DTS], [Closed_Date])
VALUES (3, 755, CAST(N'2022-06-03T00:00:00.000' AS DateTime), CAST(N'1900-01-01T00:00:00.000' AS DateTime), CAST(N'1900-01-01T00:00:00.000' AS DateTime))
INSERT [dbo].[Table1] ([RowRank], [C_Number], [SETUP_DT], [REOPEN_DTS], [Closed_Date])
VALUES (4, 755, CAST(N'2022-06-03T00:00:00.000' AS DateTime), CAST(N'1900-01-01T00:00:00.000' AS DateTime), CAST(N'2022-07-20T12:07:26.000' AS DateTime))
INSERT [dbo].[Table1] ([RowRank], [C_Number], [SETUP_DT], [REOPEN_DTS], [Closed_Date])
VALUES (1, 332, CAST(N'2022-08-12T00:00:00.000' AS DateTime), CAST(N'2022-09-22T11:46:47.000' AS DateTime), CAST(N'1900-01-01T00:00:00.000' AS DateTime))
Thanks You!