water

Question

I hited 

select chsuerid, count(*) from issues inner join actions on actions.issueid
= issues.issueid where chsuserid ='4042'and  refrenceid like'11%' and [Status]='4' and not exists(select 1 from 
Actions A where A.chsUerID = Actions.chuerID and A.Issueid=actions.issueid and A.Status='5')
Group by chsuserid  

my question is 

Number of complaints closed by employee and reopened.... but
finally closed by another employee

status for close it is '4' and for reopened it's ='5'

how we can jit query for above question please help soon 

Answer 1

Please post sample data + desired result.

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and cleansing

Answer 2

There are two tables:

In action tables fields are:

ACTIONID

ISSUEID

OLDSTATUS

NEWSTATUS

CHSUSERTYPE

CHSUSERID

CHSUSERNAME

In issues tables fields are:

CHSREASONID COMPLAINANTID

OFFICERID

CSCCODE

DISTRIBUTORID

STATUS

PRIORITY

Answer 3

It does not help.... Please post CREATE TABLE ..+INSERT INTO data for testing.

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and cleansing