Sql to get latest record under some condition

Transact-SQL
1

Screenshot 2024-11-01 234333
Screenshot 2024-11-01 2343331606×432 105 KB

I want to get record with following condition
each contract no., if has estate = 'LR', then get the record with latest build date before 'LR', and if have two record with same date, then choose type = 'X' first.
If no estate = 'LR', and choose the record with latest build date, if have same build date, choose type = 'X' first.

sample input and sampe output is attached.

The table can be created by the following sql:
CREATE TABLE [dbo].[testing](
[ID] [int] IDENTITY(1,1) NOT NULL,
[contract no.] varchar(10) not null,
[estate] varchar NOT NULL,
[type] varchar NOT NULL,
[build date] [datetime] not null,
remarks varchar(20) null
PRIMARY KEY CLUSTERED
(
[ID] ASC
)
) ON [PRIMARY]
GO

and data are inserted as follow:
insert into testing("contract no.", estate, type,"build date", remarks) values ('00001','1a','B','20241101','ewr');
insert into testing("contract no.", estate, type,"build date", remarks) values ('00001','LR','C','20240101','erew');
insert into testing("contract no.", estate, type,"build date", remarks) values ('00001','1b','X','20231005','wer');
insert into testing("contract no.", estate, type,"build date", remarks) values ('00001','1c','C2','20231005','');
insert into testing("contract no.", estate, type,"build date", remarks) values ('00002','2a','B','20241005','');
insert into testing("contract no.", estate, type,"build date", remarks) values ('00002','2b','C','20240205','');
insert into testing("contract no.", estate, type,"build date", remarks) values ('00003','3a','A1','20240602','');
insert into testing("contract no.", estate, type,"build date", remarks) values ('00003','3b','X','20240602','');
insert into testing("contract no.", estate, type,"build date", remarks) values ('00004','4a','D','20240502','');
insert into testing("contract no.", estate, type,"build date", remarks) values ('00004','4b','X','20230101','');

2

Forum Etiquette: How to post data/code on a forum to get the best help – SQLServerCentral

1 Like
3

Varchars should always have a length and reserved words, spaces etc in column names are not a good idea.

WITH LRDates
AS
(
	SELECT ID, [contract no.], estate, [type], [build date], remarks
		,MAX(IIF(estate = 'LR', [build date], '1900')) OVER (PARTITION BY [contract no.]) AS LRDate
	FROM dbo.testing
)
,ContractOrder
AS
(
	SELECT ID, [contract no.], estate, [type], [build date], remarks
		,ROW_NUMBER() OVER (PARTITION BY [contract no.] ORDER BY [build date] DESC, IIF([type] = 'X', 0, 1)) AS rn
	FROM LRDates
	WHERE [build date] < IIF(LRDate = '1900', '9999', LRDate)
)
SELECT ID, [contract no.], estate, [type], [build date], remarks
FROM ContractOrder
WHERE rn = 1;
2 Likes
4 
Hi 

Hope this helps 

CREATE TABLE [dbo].[testing](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [contract no.] varchar(10) NOT NULL,
    [estate] varchar(10) NOT NULL,
    [type] varchar(10) NOT NULL,
    [build date] [datetime] NOT NULL,
    remarks varchar(20) NULL,
    PRIMARY KEY CLUSTERED ([ID] ASC)
) ON [PRIMARY]
GO

INSERT INTO testing([contract no.], estate, [type], [build date], remarks) VALUES 
('00001','1a','B','2024-11-01','ewr'),
('00001','LR','C','2024-01-01','erew'),
('00001','1b','X','2023-10-05','wer'),
('00001','1c','C2','2023-10-05',''),
('00002','2a','B','2024-10-05',''),
('00002','2b','C','2024-02-05',''),
('00003','3a','A1','2024-06-02',''),
('00003','3b','X','2024-06-02',''),
('00004','4a','D','2024-05-02',''),
('00004','4b','X','2023-01-01','');

WITH LRDates AS (
    SELECT 
        ID, 
        [contract no.], 
        estate, 
        [type], 
        [build date], 
        remarks,
        MAX(IIF(estate = 'LR', [build date], '1900-01-01')) OVER (PARTITION BY [contract no.]) AS LRDate
    FROM 
        dbo.testing
),
FilteredRecords AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY [contract no.] ORDER BY 
            CASE 
                WHEN [build date] < LRDate THEN 1 
                ELSE 2 
            END, 
            CASE 
                WHEN [type] = 'X' THEN 1 
                ELSE 2 
            END, 
            [build date] DESC) AS rn
    FROM 
        LRDates
)
SELECT 
    [contract no.], 
    estate, 
    [type], 
    [build date], 
    remarks
FROM 
    FilteredRecords
WHERE 
    rn = 1;