Energy

Question

I hited this query :

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 from 
 Actions A where A.chsUerID = Actions.chuerID 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 reopened is = 5 and for closed it is 4

how hit the query

Answer 1

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 from 
 Actions A where A.chsUerID = Actions.chuerID and A.Status = '5')
 Group by chsuserid 

What not exits???

Answer 2

Hi,

Please post your DDL along with some sample data + the expected output please. It may help us to provide much better help than guessing.

thanks,

Janos

---

There are 10 type of people. Those who understand binary and those who do not.

My Blog | Hire Me

Answer 3

In action tables fields are:

ACTIONID

ISSUEID

OLDSTATUS

NEWSTATUS

CHSUSERTYPE

CHSUSERID

CHSUSERNAME

In issues tables fields are:

CHSREASONID COMPLAINANTID

OFFICERID

CSCCODE

DISTRIBUTORID

STATUS

PRIORITY Come in two table is issues id.  Expected output: Number of complaints closed by employee and reopened.... but finally closed by another employee

for status column: closed '4' and reopened '5'

Answer 4

Hi,

Did you read my previous post please? again: Please post your DDL along with some sample data + the expected output.

J

---

There are 10 type of people. Those who understand binary and those who do not.

My Blog | Hire Me

Answer 5

Action Table

ACTIONID    521779

ISSUEID     247959

OLDSTATUS   2
NEWSTATUS   4
CHSUSERTYPE 10
CHSUSERID   4047
CHSUSERNAME M LAXMI BAI (531470)

In issues tables fields are:
ISSUEID       247959
RefrenceID    11000071(11 indicates year)
CHSREASONID   10
COMPLAINANTID 252223
CSCCODE       12752 
DISTRIBUTORID 14590400
STATUS        4   
PRIORITY      3 

for status column: closed '4' and reopened '5'

I repeat my question i want to see complaints closed by employee(eg: laxami bai(4042)) and reopened. but finally close by other employee

• 
  

Answer 6

Can you please stop re-opening new and new threads? In your 'vanila' thread I already provided you with the query. That query is not very simple and this problem is not too simple either. Also, does any of your tables have date field? How do you define 'finally closed'? Finally closed means that the last date status is 4. However, without date field how can we know that this row is final? Or do you have any other field that dictates the order?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog