How to select just one record out of a group of record in MS SQL?

Question

How to select just one record out of a group of record in MS SQL?

Kim-SG 20

I have table like below, I am looking a S_ID with:

Any record with status=close
AND status=received with Dt_Ts is a date field must be within 60 days when status=close.

User's image

Result here will be:

S_ID

1

3

Thank you all for any comments.

Kim.

Yitzhak Khabinsky 26,586 Reputation points

2023-03-01T00:29:11.55+00:00

While asking a question you need to provide a minimal reproducible example:

(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.

(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.

(3) Desired output based on the sample data in the #1 above.

(4) Your SQL Server version (SELECT @@version;)

Accepted answer

1 additional answer

Your answer

Yitzhak Khabinsky 26,586 Reputation points

2023-03-01T00:29:11.55+00:00

While asking a question you need to provide a minimal reproducible example:

(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.

(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.

(3) Desired output based on the sample data in the #1 above.

(4) Your SQL Server version (SELECT @@version;)

Answer 1

Hi @Kim-SG,

Please try the following solution.

-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, status VARCHAR(10), dt_ts DATE,s_id INT);
INSERT @tbl (status, dt_ts, s_id) VALUES
('sent', '2011-01-01', 1),
('received', '2011-01-02', 1),
('wait', '2011-01-15', 1),
('close', '2011-02-01', 1),
('sent', '2011-01-01', 2),
('received', '2011-01-01', 2),
('close', '2011-04-01', 2),
('sent', '2011-01-02', 3),
('received', '2011-01-15', 3),
('wait', '2011-01-15', 3),
('close', '2011-02-01', 3);
-- DDL and sample data population, end

SELECT a.s_id 
	--, DATEDIFF(DAY, a.dt_ts, b.dt_ts)
FROM @tbl AS a -- received
	INNER JOIN @tbl b -- close
		ON b.s_id = a.s_id AND DATEDIFF(DAY, a.dt_ts, b.dt_ts) <= 60
WHERE a.status = 'received'
	AND b.status = 'close';

Answer 2

Hi @Kim-SG

If I understand correctly, you can try this query.

CREATE TABLE test(ID INT, Status VARCHAR(20),dt_ts DATETIME,S_ID INT);
INSERT INTO test VALUES
(1,'sent','2011-01-01',1),
(2,'received','2011-01-02',1),
(3,'wait','2011-01-15',1),
(4,'close','2011-02-01',1),
(5,'sent','2011-01-01',2),
(6,'received','2011-01-15',2),
(7,'close','2011-04-01',2),
(8,'sent','2011-01-02',3),
(9,'received','2011-01-15',3),
(10,'wait','2011-01-15',3),
(11,'close','2011-02-01',3),
(12,'sent','2011-02-12',4),
(13,'received','2011-02-28',4),
(14,'close','2011-06-06',4);


;with CTE as(
  select S_ID,max(dt_ts) as closedate,min(dt_ts) as receivedate,min(Status) as jud
  from test where Status in ('received','close') group by S_ID)
select S_ID from CTE where datediff(day,receivedate,closedate) <= 60 and jud = 'close';

Output:

User's image

Best regards,

Percy Tang

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Kim-SG 20 Reputation points

2023-03-01T17:01:58.38+00:00

I don't know why seem to be working but it is more perfect when I change to max(status) as jud then it is working. Thank a lot.

Share via

How to select just one record out of a group of record in MS SQL?

1 additional answer

Your answer