Get the records from SQL table which matches on the same date

Question

How to find the all the records which are having multiple status for the same account Id (doesn't matter what status it's belong) on the same Date, doesn't matter about the time, but Date is important

Expected Result should as be mentioned in below table, I should pick the data which are having multiple status on the same day for the same account Id

AccountId	Status	lastupdatedTimestamp
12345	DISB	2023-09-07 06:30:30.000
12345	SET	2023-09-07 06:34:30.000
32341	REB	2023-09-07 14:30:30.000
32341	D	2023-09-07 15:31:30.000
52355	SET	2023-09-09 14:30:30.000
52355	D	2023-09-09 15:31:30.000

Sample table data :

CREATE TABLE PaymentRecord
(
    accountid BIGINT,
    Status varchar(10),
    lastupdatedTimestamp DATETIME
)

INSERT INTO PaymentRecord (accountid, Status, lastupdatedTimestamp) 
VALUES (12345, 'DISB', '2023-09-07 16:30:30.000')
INSERT INTO PaymentRecord (accountid, Status, lastupdatedTimestamp) 
VALUES (12345, 'SET', '2023-09-07 16:34:30.000')
INSERT INTO PaymentRecord (accountid, Status, lastupdatedTimestamp) 
VALUES (12346, 'D', '2023-09-07 11:30:30.000')
INSERT INTO PaymentRecord (accountid, Status, lastupdatedTimestamp) 
VALUES (22341, 'CLR', '2023-09-08 13:30:30.000')
INSERT INTO PaymentRecord (accountid, Status, lastupdatedTimestamp) 
VALUES (32341, 'REB', '2023-09-08 14:30:30.000')
INSERT INTO PaymentRecord (accountid, Status, lastupdatedTimestamp) 
VALUES (32341, 'D', '2023-09-08 15:31:30.000')
INSERT INTO PaymentRecord (accountid, Status, lastupdatedTimestamp) 
VALUES (42325, 'CLR', '2023-09-09 11:30:30.000')
INSERT INTO PaymentRecord (accountid, Status, lastupdatedTimestamp) 
VALUES (52355, 'SET', '2023-09-09 14:30:30.000')
INSERT INTO PaymentRecord (accountid, Status, lastupdatedTimestamp) 
VALUES (52355, 'D', '2023-09-09 15:31:30.000')
INSERT INTO PaymentRecord (accountid, Status, lastupdatedTimestamp) 
VALUES (52355, 'SK', '2023-09-10 16:31:30.000')

I have tried the following , but that did not work based on the expected result.

SELECT DISTICNT
    AccountId,
    Status,
    CONVERT (date, LastUpdatedTimestamp)
FROM
    PaymentRecord
WHERE
    LastUpdatedTimestamp BETWEEN '2023-09-07' AND '2023-09-11'
ORDER BY
    AccountId,
    CONVERT (date, LastUpdatedTimestamp),
    Status DESC

Answer 1

It took me a while to understand your question.

But with your WHERE I can not reproduce your wanted result.

The concept is easy, make a sub-select where you get the accountid and date of the criteria more than one status at a date and join it to the main query.

select p1.AccountId
  ,convert(date,p1.LastUpdatedTimestamp),Status 
from PaymentRecord p1
  JOIN
  
  (SELECT AccountId,convert(date,LastUpdatedTimestamp) as LastUpdatedTimestamp
  FROM  PaymentRecord
 where LastUpdatedTimestamp between '2023-09-08' and '2023-09-11'
  GROUP BY AccountId,convert(date,LastUpdatedTimestamp)
HAVING COUNt(DISTINCT Status) > 1) p2 
  ON p1.AccountId = p2.AccountId AND convert(date,p1.LastUpdatedTimestamp) = p2.LastUpdatedTimestamp
  Order by p1.AccountId, p1.LastUpdatedTimestamp,Status desc

AccountId	(No column name)	Status
32341	2023-09-08	REB
32341	2023-09-08	D
52355	2023-09-09	SET
52355	2023-09-09	D

fiddle

Answer 2

You can use EXISTS:

SELECT *
FROM PaymentRecord pr1
WHERE EXISTS (
    SELECT *
    FROM PaymentRecord pr2
    WHERE pr1.AccountId = pr2.AccountId
    AND pr1.Status <> pr2.Status
    AND CAST(pr1.lastupdatedTimestamp AS DATE) = CAST(pr2.lastupdatedTimestamp AS DATE)
)
AND pr1.lastupdatedTimestamp BETWEEN '2023-09-07' AND '2023-09-11'