CODEWITHSUNDEEP
sqlsql-server
bieman
bieman

Reputation: 1

Aggregate rows where primary key has multiple entries -SQL server

My primary key is a case_id in a ticketing system. My DB records a change to any attribute of the case_id's as a new entry. So any case_id may have multiple entries. The changed attribute gets recorded in attribute column and there's from_string and to_string columns which show changed values.

The issue is when extracting open cases which have recorded an 'open' event but no 'close' event in description column. Open and closing event get recorded by changes in different attributes.

I've tried

select case_id, create_date, attribute, from_string, to_string
from mydb
where   (
attribute    = 'status'
AND to_string       = 'opened' 
) --- opening event
AND NOT attribute = 'Root Cause' --- closing event
group by case_id
;

Upvotes: 0

Views: 116

Answers (2)

Venkataraman R
Venkataraman R

Reputation: 12969

you can achieve this using Common Table Expression.

;WITH CTE_CaseData AS
(
select case_id, create_date, attribute, from_string, to_string, 
CASE WHEN attribute    = 'status'
AND to_string       = 'opened' THEN 'Opened'
WHEN attribute = 'Root Cause' THEN 'Closed'
ELSE NULL END AS Status
from mydb
)
SELECT case_id, create_date, attribute, from_string, to_string
from CTE_CaseData as oc
WHERE NOT EXISTS 
(
SELECT * FROM CTE_CaseData WHERE case_id = oc.caseid AND status = 'Closed'
) AND status = 'Opened'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270663

The issue is when extracting open cases which have recorded an 'open' event but no 'close' event in description column.

I would expect code that looks like this:

select d.*
from mydb d
where d.attribute = 'status' and
      d.to_string = 'opened' and
      not exists (select 1
                  from mydb d2
                  where d2.case_id = d.case_id and
                        d2.attribute = 'status' and
                        d2.to_string = 'Root Cause'
                 );

Upvotes: 1

Related Questions