TSQL Question Query help

Sh AS 41 Reputation points
2022-10-04T19:47:11.237+00:00

Hello,

I have the following table.

247474-image.png

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:

247571-image.png

-- 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!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,477 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,535 questions
{count} votes

1 answer

Sort by: Most helpful
  1. CosmogHong-MSFT 21,626 Reputation points Microsoft Vendor
    2022-10-05T02:36:41.967+00:00

    Hi @Sh AS
    Check this query:

    ;WITH CTE AS  
    (  
    SELECT DISTINCT C_Number,CAST(SETUP_DT AS DATE) SETUP_DT  
          ,CASE WHEN REOPEN_DTS ='1900-01-01T00:00:00.000' AND LAG(REOPEN_DTS)OVER(PARTITION BY C_Number ORDER BY RowRank)IS NULL  
    	        THEN SETUP_DT ELSE REOPEN_DTS END AS OPEN_DTS  
    	  ,CASE WHEN REOPEN_DTS <> '1900-01-01T00:00:00.000'   
    	        THEN (SELECT MAX(Closed_Date) FROM Table1 WHERE C_Number=T.C_Number AND RowRank >= T.RowRank)  
    			ELSE Closed_Date END AS Closed_Date  
    FROM Table1 T  
    WHERE REOPEN_DTS <> '1900-01-01T00:00:00.000' OR Closed_Date <> '1900-01-01T00:00:00.000'   
    )  
    SELECT C_Number,SETUP_DT,OPEN_DTS,CASE WHEN Closed_Date ='1900-01-01T00:00:00.000'  THEN '12/31/9000' ELSE Closed_Date END AS Closed_Date  
    FROM CTE  
    WHERE OPEN_DTS<>'1900-01-01T00:00:00.000'  
    ORDER BY C_Number,OPEN_DTS  
    

    Output:
    247613-image.png

    Best regards,
    LiHong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments