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