CODEWITHSUNDEEP
sql-server
RJohnson
RJohnson

Reputation: 188

SQL Query determining oldest message with additional criteria

I've got a rather complicated query that sounds easy on the surface but is proving more trouble than I expected.

I have a table named inboundMessages which acts as a sort of FIFO queue, but with a twist. I need to get the oldest non-processed message (status 0) that does not have a status of 1 or 3.

Example:

Id     messageID     Status     ReceivedDateTime
-------------------------------------------------------
112    1234          1          2018-08-07 14:52:48.657
113    1234          3          2018-08-08 14:52:48.657
114    1234          0          2018-08-07 14:52:48.657
119    1235          2          2018-08-07 16:05:10.870
120    1235          0          2018-08-08 10:37:09.980
121    1237          0          2018-08-09 05:43:08.824

Wanted Results

120    1235          0          2018-08-08 10:37:09.980

Statuses

ID       Status
0        Not picked up, not processed
1        Picked up - not processed 
2        Processed Successfully 
3        Error during processing 
4        Errored - Refiled
5        Errored - Ignored, unresolvable.

ID 114 would not be valid because other rows have the same messageID and a status of 1 or 3 meaning those will need to be resolved before it could be picked up.

ID 121 is almost valid because there are no earlier messages with the MessageID of 1237, but it's not the oldest.

ID 120 is valid because it has an earlier message of 1235, but it's status is 2, meaning it filed successfully.

I've tried using a query using a status and trying to use a "having" clause, but it doesn't seem to be right as I'm not getting any results.

My query: 

SELECT [Id]
       ,[MessageID]
      ,[Status]
      ,[ReceivedDateTime]
       ,COUNT(case when  status = 2  or status = 3  then 1 END) as  'Count'
  FROM inboundMessages

 group by [Id]
       ,[MessageID]
      ,[Status]
      ,[ReceivedDateTime]

  having Status=0 and COUNT(case when  status = 1 or status = 3 then 1 END) >=1

Upvotes: 2

Views: 72

Answers (3)

jilvanx
jilvanx

Reputation: 461

I think this can may help you.

SELECT
    Id, messageID, Status, ReceivedDateTime
FROM inboundMessages
WHERE messageID IN (
    SELECT
        messageID
    FROM inboundMessages
    WHERE Status NOT IN (1, 3)
    GROUP BY messageID
    HAVING COUNT(messageID) > 1
)
    AND Status = 0 
GROUP BY Id, messageID, Status, ReceivedDateTime

Upvotes: 0

JMabee
JMabee

Reputation: 2300

I used a two step process, first get the rows that are valid (Status=0) and then get the first one that does not have another row in the set with a 1 or 3

CREATE TABLE #tmp(Id int, messageID int, Status int,ReceivedDateTime datetime)

INSERT INTO #tmp VALUES(112,1234,1,'2018-08-07 14:52:48.657')
INSERT INTO #tmp VALUES(113,1234,3,'2018-08-08 14:52:48.657')
INSERT INTO #tmp VALUES(114,1234,0,'2018-08-07 14:52:48.657')
INSERT INTO #tmp VALUES(119,1235,2,'2018-08-07 16:05:10.870')
INSERT INTO #tmp VALUES(120,1235,0,'2018-08-08 10:37:09.980')
INSERT INTO #tmp VALUES(121,1237,0,'2018-08-09 05:43:08.824')

;WITH CTE as 
    (
    SELECT Id,messageID,ReceivedDateTime from #tmp t1 where Status = 0
    )
SELECT  TOP 1 * from CTE
 where messageID not in(select messageID from #tmp where Status in (1,3))
 ORDER BY ReceivedDateTime

Upvotes: 1

S3S
S3S

Reputation: 25132

I think this should do it.

select 
--select only 1 record
top 1 *
--from your base table
from inboundMessages
--where the messageID has a status of 0 and 2
where messageID in (select messageID from inboundMessages where Status not in (1,3)  group by messageID having count(distinct Status) > 1)
--of those messageID's that meet the where criteria, bring back only the row with Status = 0
and Status = 0
--make sure it's the oldest record (to only get one, and the correct one)
order by ReveivedDateTime desc

Upvotes: 0

Related Questions