CODEWITHSUNDEEP
sqlsql-servergroup-by
urlreader
urlreader

Reputation: 6605

SQL: How to check values within Group?

I have a table like this:

enter image description here

For each TaskConfigId, there is a TaskGuid. If the TaskGuid is the same for the same TaskConfigId, it seems they are for the same Task. Usually, the TaskStatusId will be 10 or 20. When there is something happened, TaskStatusId will be other values, i.e. 15. But for each Task, it always have 10 and 20.

So, if the task's status has 10 AND 20, it means nothing special happened.

Now, I want to find out for a TaskConfigId (for example, 100), when is the last CreatedDate, AND it has TaskStatusId 10 AND 20 ONLY, no other values. So if the TaskConfigId is 40, the highlighted case is not correct.

Here is what I have

SELECT TOP (1) CreatedDate
FROM [aptfeed].[TaskTracking]
WHERE TaskGuid IN (
        SELECT TaskGuid
        FROM [aptfeed].[TaskTracking]
        WHERE TaskConfigId = 100
        GROUP BY TaskGuid HAVING COUNT(*) = 2               
        )
    AND TaskStatusId = 10   -- start
ORDER BY CreatedDate DESC

Basically, it groups TaskGuid for the TaskConfigId, make sure it has 2 entries. Sort it and then get the last one.

My question is: what if I want to make sure the 2 entries are 10 AND 20 (if we can make sure the order is 10 first, then 20, better)?

I tried this:

SELECT TOP (1) CreatedDate
FROM [aptfeed].[TaskTracking]
WHERE TaskGuid IN (
        SELECT TaskGuid
        FROM [aptfeed].[TaskTracking]
        WHERE TaskConfigId = 100
            AND (TaskStatusId = 10 OR TaskStatusId = 20)
        GROUP BY TaskGuid HAVING COUNT(*) = 2               
        )
    AND TaskStatusId = 10   -- start
ORDER BY CreatedDate DESC

But this does not work, since it filtered all other TaskStatusId first. So, almost all COUNT(*) will be 2, i.e. TaskConfigId = 40 in the screen shot will be selected since line14 will be filtered.

Also, I feel the query I'm using is slow since it has to group all TaskGuid for the TaskConfigId. Any suggestions to improve this?

Thanks

Upvotes: 0

Views: 74

Answers (3)

Jaro
Jaro

Reputation: 23

If you're trying to get every TaskConfigId that has TaskStatusId 10 AND 20 (both records should exist) and find the last CreatedDate try this:

SELECT TaskGuid
FROM [TaskTracking]
WHERE TaskConfigId = 100
AND (TaskStatusId = 10 OR TaskStatusId = 20)
GROUP BY TaskGuid HAVING COUNT(*) = 2

You can also display the date if needed:

SELECT TaskGuid, MAX(CreatedDate) AS LastDate
FROM [TaskTracking]
WHERE TaskConfigId = 100
AND (TaskStatusId = 10 OR TaskStatusId = 20)
GROUP BY TaskGuid HAVING COUNT(*) = 2

Upvotes: 1

iSR5
iSR5

Reputation: 3498

If I got it right, you're trying to get every TaskConfigId that has TaskStatusId 10 AND 20 (both records should be existed) only, and get the last CreatedDate for each one of them.

you could do something like this : 

SELECT *
FROM (
    SELECT *
    , COUNT(TaskGuid) OVER (PARTITION BY TaskGuid) TaskCount 
    , ROW_NUMBER() OVER (PARTITION BY TaskGuid ORDER BY CreatedDate DESC) RN    
    FROM 
        [aptfeed].[TaskTracking]
    WHERE 
        TaskConfigId = 100
    AND TaskStatusId IN(10,20)
) D
WHERE 
    TaskCount = 2
AND RN = 1

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272006

You can find the TaskGuid by tweaking your original query just a tad bit:

SELECT TaskGuid
FROM [aptfeed].[TaskTracking]
WHERE TaskConfigId = 100
GROUP BY TaskGuid
HAVING COUNT(*) = 2
AND    MIN(TaskStatusId) = 10
AND    MAX(TaskStatusId) = 20

This will solve the problem of finding groups that contain 10 and 20 (not necessarily in that order) and nothing else.

But you can use window functions too:

SELECT TaskConfigId, TaskGuid, MAX(CreatedDate)
FROM (
    SELECT TaskConfigId
         , TaskGuid
         , ItemCount = COUNT(*) OVER (PARTITION BY TaskConfigId, TaskGuid)
         , FirstStatus = FIRST_VALUE(TaskStatusId) OVER (PARTITION BY TaskConfigId, TaskGuid ORDER BY CreatedDate)
         , LastStatus = LAST_VALUE(TaskStatusId) OVER (PARTITION BY TaskConfigId, TaskGuid ORDER BY CreatedDate)
         , CreatedDate
    FROM [aptfeed].[TaskTracking]
) AS cte
WHERE ItemCount = 2
AND FirstStatus = 10
AND LastStatus = 20
GROUP BY TaskConfigId, TaskGuid

Upvotes: 1

Related Questions