CODEWITHSUNDEEP
sqlsql-server
Carol
Carol

Reputation: 1930

How to exclude results not associated to a specific value

WorkQueueItem contains unique requests, some have failed with an exception and others did not fail.

The Tag table returns all tags related to a request. A request can have multiple tags or none.

I'm trying to get all the count of failed requests grouped by type of exception that do not have the "Forwarded" tag. I did get 'Duplicate Request' and 'Dummy Request' which don't have the "Forwarded" tag but 'Could no Complete' does have the 'Forwarded' tag but it also has this 'Deferred' tag as well which is returned.

I do not want to get 'Could not Complete' at all since it is associated to 'Forwarded' somewhere.

How could I get requests that don't have a Forwarded tag associated to them ?

Wanted result:

WorkQueueItem contains unique requests, some have failed with an exception and others did not fail.

The Tag table returns all tags related to a request. A request can have multiple tags or none.

I'm trying to get all the count of failed requests grouped by type of exception that do not have the "Forwarded" tag. I did get 'Duplicate Request' and 'Dummy Request' which don't have the "Forwarded" tag but 'Could no Complete' does have the 'Forwarded' tag but it also has this 'Deferred' tag as well which is returned.

I do not want to get 'Could not Complete' at all since it is associated to 'Forwarded' somewhere.

How could I get requests that don't have a Forwarded tag associated to them ?

Wanted result: (I previously added the tag column just to help understanding what is going on, I don't want it)

+---+--------------------+-------+
|   | Exception          | Count |
+---+--------------------+-------+
| 1 | Duplicate Request  | 1     |
+---+--------------------+-------+
| 2 | Dummy Request      | 1     |
+---+--------------------+-------+

Current result:

+---+--------------------+-------+----------+
|   | Exception          | Count | tag      |
+---+--------------------+-------+----------+
| 1 | Duplicate Request  | 1     | NULL     |
+---+--------------------+-------+----------+
| 2 | Dummy Request      | 1     | Deferred |
+---+--------------------+-------+----------+
| 3 | Could not Complete | 1     | Deferred |
+---+--------------------+-------+----------+

Current Query:

SELECT  exceptionreason as Exception,
        COUNT(exceptionreason) as Count,
        tag

FROM [myDB].[dbo].[WorkQueueItem]
JOIN myDB.dbo.WorkQueue
ON WorkQueue.ident = WorkQueueItem.queueident
LEFT JOIN myDB.dbo.WorkQueueItemTag
ON WorkQueueItem.ident = WorkQueueItemTag.queueitemident
LEFT JOIN myDB.dbo.Tag
ON WorkQueueItemTag.tagid = Tag.id

WHERE name='TestQueue' 
AND exception is not null
AND (NOT Tag.tag LIKE '%Forwarded%' OR Tag.tag is null)
Group by exceptionreason, tag

I hope there is enough info here. Any help is appreciated, thanks.

EDIT: Adding Datasets

WorkQueueItem

+---+--------+------------+--------------------+------------+
|   | ident  | exception  | exceptionreason    | completed  |
+---+--------+------------+--------------------+------------+
| 1 | 192947 | 2017-05-25 | Dummy Request      | NULL       |
+---+--------+------------+--------------------+------------+
| 2 | 194990 | NULL       | NULL               | 2017-05-25 |
+---+--------+------------+--------------------+------------+
| 3 | 194994 | 2017-05-25 | Duplicate Request  | NULL       |
+---+--------+------------+--------------------+------------+
| 4 | 194995 | 2017-05-25 | Could not Complete | NULL       |
+---+--------+------------+--------------------+------------+

WorkQueueItemTag

+---+----------------+-------+
|   | queueitemident | tagid |
+---+----------------+-------+
| 1 | 192947         | 14904 |
+---+----------------+-------+
| 2 | 194995         | 14905 |
+---+----------------+-------+
| 3 | 194995         | 14906 |
+---+----------------+-------+

TAG

+---+-------+-----------+
|   | id    | tag       |
+---+-------+-----------+
| 1 | 14904 | Deferred  |
+---+-------+-----------+
| 2 | 14905 | Forwarded |
+---+-------+-----------+
| 3 | 14906 | Deferred  |
+---+-------+-----------+

Upvotes: 1

Views: 58

Answers (3)

S3S
S3S

Reputation: 25112

One easy way is with a correlated sub-query and NOT IN 

declare @WorkQueueItem table (ident int, exception datetime, exceptionreasion varchar(128), completed datetime)
insert into @WorkQueueItem
values
(192947,'2017-05-25','Dummy Request',NULL),
(194990,NULL,NULL,'2017-05-25'),
(194994,'2017-05-25','Duplicate Request',NULL),
(194995,'2017-05-25','Could not Complete',NULL)

declare @WorkQueueItemTag table (queueitemindent int, tagid int)
insert into @WorkQueueItemTag
values
(192947,14904),
(194995,14905),
(194995,14906)

declare @TAG table (id int, tag varchar(64))
insert into @TAG
values
(14904,'Deferred'),
(14905,'Forwarded'),
(14906,'Deferred')


select
    i.exceptionreasion
    ,count(*)
from @WorkQueueItem i
where i.ident not in (select i.ident
                    from @WorkQueueItem i
                    left join @WorkQueueItemTag it on it.queueitemindent = i.ident
                    left join @TAG t on t.id = it.tagid
                    where t.tag = 'Forwarded' )
and i.exceptionreasion is not null
group by 
    i.exceptionreasion

Upvotes: 1

Greg
Greg

Reputation: 66

This could be close to what you want.

SELECT 
    WQI.exceptionreason, 
    COUNT(*) [Total] 
FROM 
    myDB.dbo.WorkQueueItem WQI 
WHERE 
    WQI.exception IS NOT NULL 
    AND EXISTS(SELECT 1 FROM myDB.dbo.WorkQueue WHERE ident = WQI.queueident AND name = 'TestQueue') 
    AND NOT EXISTS(
        SELECT 
            1 
        FROM 
            myDB.dbo.WorkQueueItemTag WQIT 
        WHERE 
            WQIT.queueitemident = WQI.ident 
            AND EXISTS(SELECT 1 FROM myDB.dbo.Tag WHERE id = WQIT.tagid AND tag LIKE '%Forwarded%') 
    ) 
GROUP BY 
    WQI.exceptionreason;

Upvotes: 1

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

Looks like you are looking for conditional aggregation as below:

 SELECT  exceptionreason as Exception,
        SUM(case when Tag.tag LIKE '%Forwarded%' or Tag.tag is null then 0 else 1 end) as Count,
        tag

FROM [myDB].[dbo].[WorkQueueItem]
JOIN myDB.dbo.WorkQueue
ON WorkQueue.ident = WorkQueueItem.queueident
LEFT JOIN myDB.dbo.WorkQueueItemTag
ON WorkQueueItem.ident = WorkQueueItemTag.queueitemident
LEFT JOIN myDB.dbo.Tag
ON WorkQueueItemTag.tagid = Tag.id

WHERE name='TestQueue' 
AND exception is not null
----AND (NOT Tag.tag LIKE '%Forwarded%' OR Tag.tag is null)
Group by exceptionreason, tag

Upvotes: 0

Related Questions