Checking content - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Checking content

Author

Topic

Steve2106
Posting Yak Master

183 Posts

Posted - 2011-08-03 : 06:52:42

Hi There,

I have an Sql Table that has a callId and a CallCode so I could have:

CallId: 6 CallCode: (1) Assigned
CallId: 6 CallCode: (2) Accepted
CallId: 6 CallCode: (3) Completed
CallId: 6 CallCode: (4) CompleteAccepted

I need to query the table and get a list of all the CallId that have a CallCode of (1) Assigned but not a CallCode of (2) Accepted

Also the same for,
I need to query the table and get a list of all the CallId that have a CallCode of (3) Completed but not a CallCode of (4) CompleteAccepted

I am trying to give a list of records that are waiting to be accepted

Thanks for your help.

Best Regards,

Steve

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-03 : 07:26:08

you mean this?


SELECT CalliD
FROM Table
GROUP BY CallID
HAVING (SUM(CASE WHEN CallCode=1 THEN 1 ELSE 0 END) >0
AND SUM(CASE WHEN CallCode=2 THEN 1 ELSE 0 END) =0)
OR (SUM(CASE WHEN CallCode=3 THEN 1 ELSE 0 END) >0
AND SUM(CASE WHEN CallCode=4 THEN 1 ELSE 0 END) =0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Steve2106
Posting Yak Master

183 Posts

Posted - 2011-08-04 : 14:55:41

Does anyone know how I can do this
Hi,
Thanks for taking the time to help.

Your code works great but I need to be able to show what they relate to. on the left is what I have returned from your code on the right is what I need to be shown:
CallId Callid Action
2 2 Needs Accepting
3 3 Needs Accepting
10 10 Needs Close-Out
15 15 Needs Accepting
16 16 Needs Close-Out
22 22 Needs Close-Out
25 25 Needs Close-Out
26 26 Needs Accepting
28 28 Needs Accepting
29 29 Needs Close-Out
30 30 Needs Close-Out
31 31 Needs Close-Out

Your code is exactly what I need but I must show what it relates to.
Thanks again for your help, I appreciate it.

Best Regards,

Steve

Steve2106
Posting Yak Master

183 Posts

Posted - 2011-08-10 : 06:43:47

Hi There,

I need your help further on this.
The code that visakh16 supplied below works great but I need it to be more user friendly. This is visakh16 select query.

SELECT CalliD
FROM Table
GROUP BY CallID
HAVING (SUM(CASE WHEN CallCode=1 THEN 1 ELSE 0 END) >0
AND SUM(CASE WHEN CallCode=2 THEN 1 ELSE 0 END) =0)
OR (SUM(CASE WHEN CallCode=3 THEN 1 ELSE 0 END) >0
AND SUM(CASE WHEN CallCode=4 THEN 1 ELSE 0 END) =0)

I need to add something that when the sum above is 1, add some text that says 'Needs Accepting'

IE the select query above returns something like:
CallID
2
3
7
What I need to return is something like:
CallID
2 Needs Accepting
3 Needs Accepting
7 Needs Accepting

How can I do that.

Thanks again for your help. I do appreciate it.

Best Regards,

Steve

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-10 : 07:19:44

you mean this?


SELECT CalliD,
CASE WHEN SUM(CASE WHEN CallCode=1 THEN 1 ELSE 0 END) >1 OR
SUM(CASE WHEN CallCode=3 THEN 1 ELSE 0 END) >1
THEN 'Needs Accepting'
ELSE 'Some default value'
END
FROM Table
GROUP BY CallID
HAVING (SUM(CASE WHEN CallCode=1 THEN 1 ELSE 0 END) >0
AND SUM(CASE WHEN CallCode=2 THEN 1 ELSE 0 END) =0)
OR (SUM(CASE WHEN CallCode=3 THEN 1 ELSE 0 END) >0
AND SUM(CASE WHEN CallCode=4 THEN 1 ELSE 0 END) =0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Steve2106
Posting Yak Master

183 Posts

Posted - 2011-08-10 : 08:39:17

Hi There,
Thanks for your reply, I appreciate you taking time to help.

I am sorry to ask but I need it to work on both the criteria. I.E.
If callcode = 1 then 'Needs Accepting'
If Callcode = 3 then 'Needs CallBack'

Also do I need to keep the code at the end:
HAVING (SUM(CASE WHEN CallCode=1 THEN 1 ELSE 0 END) >0
AND SUM(CASE WHEN CallCode=2 THEN 1 ELSE 0 END) =0)
OR (SUM(CASE WHEN CallCode=3 THEN 1 ELSE 0 END) >0
AND SUM(CASE WHEN CallCode=4 THEN 1 ELSE 0 END) =0)

Thanks again,

Steve

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-10 : 09:16:30

[code]
SELECT CalliD,
CASE WHEN SUM(CASE WHEN CallCode=1 THEN 1 ELSE 0 END) >1 THEN 'Needs Accepting'
WHEN SUM(CASE WHEN CallCode=3 THEN 1 ELSE 0 END) >1 THEN 'Needs CallBack'
END
FROM Table
GROUP BY CallID
HAVING (SUM(CASE WHEN CallCode=1 THEN 1 ELSE 0 END) >0
AND SUM(CASE WHEN CallCode=2 THEN 1 ELSE 0 END) =0)
OR (SUM(CASE WHEN CallCode=3 THEN 1 ELSE 0 END) >0
AND SUM(CASE WHEN CallCode=4 THEN 1 ELSE 0 END) =0)
[/code]

you need to retain the code at end if you want to filter based on callcode value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Steve2106
Posting Yak Master

183 Posts

Posted - 2011-08-10 : 10:48:46

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-08-10 : 14:27:39

quote:
I need to query the table and get a list of all the CallId that have a CallCode of (1) Assigned but not a CallCode of (2) Accepted

select asgn.callId
  from table asgn
  where asgn.callCode = 1
    and not exists(select *
                     from table acc
                     where acc.callId = asgn.callId
                       and acc.callCode = 2
                   )

quote:
I need to query the table and get a list of all the CallId that have a CallCode of (3) Completed but not a CallCode of (4) CompleteAccepted

Use the same logic as for the first query.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Steve2106
Posting Yak Master

183 Posts

Posted - 2011-08-10 : 18:46:50

Hi There,
Thanks for the reply.

I'll give that code a try but how do I add another column showing a prompt where not exists CallCode2.
I would like to show something like:

CallId | Message
3 | Need to Accept
7 | Need to Accept
9 | Need to Call */ This is if it's a Callcode of 3

Thanks for your help.

Best Regards,

Steve

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-11 : 11:26:53

quote:
Originally posted by Steve2106

Hi,

Thanks again for your reply.

I tried the code but it returns:
CallCode | NoColumnName
2 | NULL
3 | NULL
10| NULL
15| NULL

Any ideas why.

Best Regards,

Steve

how come SUM return NULL. i think you're using some modified query rather than my suggestion.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Steve2106
Posting Yak Master

183 Posts

Posted - 2011-08-12 : 04:22:08

Hi visakh16,
Thanks for the reply and your continued help.

Your right I have changed it a bit to work with a different table.
This is the code I am using. It returns the correct ActionId but not the text. As mentioned the column name is (no column name) and it contains NULL.

SELECT ActionID,
CASE WHEN SUM(CASE WHEN EmailType = '(1) Assign/Re-Assign' THEN 1 ELSE 0 END) >1 THEN 'Needs Accepting'
WHEN SUM(CASE WHEN EmailType = '(5) Complete Action' THEN 1 ELSE 0 END) >1 THEN 'Needs CallBack'
END
FROM tbemailhistory
GROUP BY ActionId
HAVING (SUM(CASE WHEN EmailType = '(1) Assign/Re-Assign' THEN 1 ELSE 0 END) >0
AND SUM(CASE WHEN EmailType = '(2) Accept' THEN 1 ELSE 0 END) =0)
OR (SUM(CASE WHEN EmailType = '(5) Complete Action' THEN 1 ELSE 0 END) >0
AND SUM(CASE WHEN EmailType = '(6) Accept Close-Out' THEN 1 ELSE 0 END) =0)

I do not think I have changed your code much.

Best Regards,

Steve

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources