May 31, 2012 at 12:52 pm
I apologize if this is a confusing question...
What I'm trying to do is create a case statement that makes a field two names based on a specific condition.
One instance is where if it's an internet source (Google, MSN, Youtube...) then I want the source to be changed to Internet. The problem I'm getting is that I have 3 rows of data that should be grouped by the source. See below:
#CALLSSOURCE ORGSTATUS
2INTERNETADCUSTOMER
1INTERNETADCUSTOMER
1INTERNETADCUSTOMER
Here's my queryDECLARE @CALLIN INT = 3
DECLARE @ADLEADS INT = 4
DECLARE @INTLEADS INT = 2
DECLARE @CUSTOMER INT = 14
DECLARE @ADLEADFUP INT = 20
DECLARE @DEALERDEV INT = 19
SELECT COUNT(*) AS '#CALLS'
,CASE
WHEN SOURCE IN ('INTERNET', 'YOUTUBE', 'GOOGLE', 'BING', 'MSN', 'YAHOO') THEN 'INTERNET'
ELSE 'DIRECT MAIL'
END AS SOURCE
,CASE
WHEN KEY1 = 'AD' AND KEY3 = 'LEAD' THEN (@ADLEADS - COUNT(*))
WHEN SOURCE IN ('INTERNET') THEN (@INTLEADS - COUNT(*))
WHEN KEY1 = 'AD' AND KEY3 = 'CUSTOMER' THEN (@CUSTOMER - COUNT(*))
WHEN KEY1 = 'AD' AND KEY3 = 'LEAD' THEN (@ADLEADS - COUNT(*))
WHEN KEY1 != 'AD' AND KEY3 = 'LEAD' THEN (@DEALERDEV - COUNT(*))
END AS C
,KEY1 AS 'ORG'
,KEY3 AS 'STATUS'
FROM [CAL] AS AA
JOIN [CONTACT1] AS BB
ON AA.ACCOUNTNO = BB.ACCOUNTNO
WHERE USERID LIKE '%WAYNE%'
AND AA.CREATEON = '2012-5-31'
GROUP BY SOURCE, KEY1, KEY3
ORDER BY ORG ASC, STATUS
When I take out the case statement, this is my data:
#CALLSSOURCE ORGSTATUS
2GOOGLE ADCUSTOMER
1INTERNET ADCUSTOMER
1YAHOO ADCUSTOMER
And here's my query:
DECLARE @CALLIN INT = 3
DECLARE @ADLEADS INT = 4
DECLARE @INTLEADS INT = 2
DECLARE @CUSTOMER INT = 14
DECLARE @ADLEADFUP INT = 20
DECLARE @DEALERDEV INT = 19
SELECT COUNT(*) AS '#CALLS'
--,CASE
--WHEN SOURCE IN ('INTERNET', 'YOUTUBE', 'GOOGLE', 'BING', 'MSN', 'YAHOO') THEN 'INTERNET'
--ELSE 'DIRECT MAIL'
--END AS SOURCE
,SOURCE
,CASE
WHEN KEY1 = 'AD' AND KEY3 = 'LEAD' THEN (@ADLEADS - COUNT(*))
WHEN SOURCE IN ('INTERNET') THEN (@INTLEADS - COUNT(*))
WHEN KEY1 = 'AD' AND KEY3 = 'CUSTOMER' THEN (@CUSTOMER - COUNT(*))
WHEN KEY1 = 'AD' AND KEY3 = 'LEAD' THEN (@ADLEADS - COUNT(*))
WHEN KEY1 != 'AD' AND KEY3 = 'LEAD' THEN (@DEALERDEV - COUNT(*))
END AS C
,KEY1 AS 'ORG'
,KEY3 AS 'STATUS'
FROM [Goldmine].[dbo].[CAL] AS AA
JOIN [Goldmine].[dbo].[CONTACT1] AS BB
ON AA.ACCOUNTNO = BB.ACCOUNTNO
WHERE USERID LIKE '%WAYNE%'
AND AA.CREATEON = '2012-5-31'
GROUP BY SOURCE, KEY1, KEY3
ORDER BY ORG ASC, STATUS
So my question is.....is there a way to group these fields together when the CASE statement makes them a different value?
May 31, 2012 at 1:03 pm
joshd 1807 (5/31/2012)
I apologize if this is a confusing question...What I'm trying to do is create a case statement that makes a field two names based on a specific condition.
One instance is where if it's an internet source (Google, MSN, Youtube...) then I want the source to be changed to Internet. The problem I'm getting is that I have 3 rows of data that should be grouped by the source. See below:
#CALLSSOURCE ORGSTATUS
2INTERNETADCUSTOMER
1INTERNETADCUSTOMER
1INTERNETADCUSTOMER
Here's my query
DECLARE @CALLIN INT = 3
DECLARE @ADLEADS INT = 4
DECLARE @INTLEADS INT = 2
DECLARE @CUSTOMER INT = 14
DECLARE @ADLEADFUP INT = 20
DECLARE @DEALERDEV INT = 19
SELECT COUNT(*) AS '#CALLS'
,CASE
WHEN SOURCE IN ('INTERNET', 'YOUTUBE', 'GOOGLE', 'BING', 'MSN', 'YAHOO') THEN 'INTERNET'
ELSE 'DIRECT MAIL'
END AS SOURCE
,CASE
WHEN KEY1 = 'AD' AND KEY3 = 'LEAD' THEN (@ADLEADS - COUNT(*))
WHEN SOURCE IN ('INTERNET') THEN (@INTLEADS - COUNT(*))
WHEN KEY1 = 'AD' AND KEY3 = 'CUSTOMER' THEN (@CUSTOMER - COUNT(*))
WHEN KEY1 = 'AD' AND KEY3 = 'LEAD' THEN (@ADLEADS - COUNT(*))
WHEN KEY1 != 'AD' AND KEY3 = 'LEAD' THEN (@DEALERDEV - COUNT(*))
END AS C
,KEY1 AS 'ORG'
,KEY3 AS 'STATUS'
FROM [CAL] AS AA
JOIN [CONTACT1] AS BB
ON AA.ACCOUNTNO = BB.ACCOUNTNO
WHERE USERID LIKE '%WAYNE%'
AND AA.CREATEON = '2012-5-31'
GROUP BY SOURCE, KEY1, KEY3
ORDER BY ORG ASC, STATUS
When I take out the case statement, this is my data:
#CALLSSOURCE ORGSTATUS
2GOOGLE ADCUSTOMER
1INTERNET ADCUSTOMER
1YAHOO ADCUSTOMER
And here's my query:
DECLARE @CALLIN INT = 3
DECLARE @ADLEADS INT = 4
DECLARE @INTLEADS INT = 2
DECLARE @CUSTOMER INT = 14
DECLARE @ADLEADFUP INT = 20
DECLARE @DEALERDEV INT = 19
SELECT COUNT(*) AS '#CALLS'
--,CASE
--WHEN SOURCE IN ('INTERNET', 'YOUTUBE', 'GOOGLE', 'BING', 'MSN', 'YAHOO') THEN 'INTERNET'
--ELSE 'DIRECT MAIL'
--END AS SOURCE
,SOURCE
,CASE
WHEN KEY1 = 'AD' AND KEY3 = 'LEAD' THEN (@ADLEADS - COUNT(*))
WHEN SOURCE IN ('INTERNET') THEN (@INTLEADS - COUNT(*))
WHEN KEY1 = 'AD' AND KEY3 = 'CUSTOMER' THEN (@CUSTOMER - COUNT(*))
WHEN KEY1 = 'AD' AND KEY3 = 'LEAD' THEN (@ADLEADS - COUNT(*))
WHEN KEY1 != 'AD' AND KEY3 = 'LEAD' THEN (@DEALERDEV - COUNT(*))
END AS C
,KEY1 AS 'ORG'
,KEY3 AS 'STATUS'
FROM [Goldmine].[dbo].[CAL] AS AA
JOIN [Goldmine].[dbo].[CONTACT1] AS BB
ON AA.ACCOUNTNO = BB.ACCOUNTNO
WHERE USERID LIKE '%WAYNE%'
AND AA.CREATEON = '2012-5-31'
GROUP BY SOURCE, KEY1, KEY3
ORDER BY ORG ASC, STATUS
So my question is.....is there a way to group these fields together when the CASE statement makes them a different value?
Here:
DECLARE @CALLIN INT = 3
DECLARE @ADLEADS INT = 4
DECLARE @INTLEADS INT = 2
DECLARE @CUSTOMER INT = 14
DECLARE @ADLEADFUP INT = 20
DECLARE @DEALERDEV INT = 19
SELECT COUNT(*) AS '#CALLS'
,CASE
WHEN SOURCE IN ('INTERNET', 'YOUTUBE', 'GOOGLE', 'BING', 'MSN', 'YAHOO') THEN 'INTERNET'
ELSE 'DIRECT MAIL'
END AS SOURCE
,CASE
WHEN KEY1 = 'AD' AND KEY3 = 'LEAD' THEN (@ADLEADS - COUNT(*))
WHEN SOURCE IN ('INTERNET') THEN (@INTLEADS - COUNT(*))
WHEN KEY1 = 'AD' AND KEY3 = 'CUSTOMER' THEN (@CUSTOMER - COUNT(*))
WHEN KEY1 = 'AD' AND KEY3 = 'LEAD' THEN (@ADLEADS - COUNT(*))
WHEN KEY1 != 'AD' AND KEY3 = 'LEAD' THEN (@DEALERDEV - COUNT(*))
END AS C
,KEY1 AS 'ORG'
,KEY3 AS 'STATUS'
FROM [CAL] AS AA
JOIN [CONTACT1] AS BB
ON AA.ACCOUNTNO = BB.ACCOUNTNO
WHERE USERID LIKE '%WAYNE%'
AND AA.CREATEON = '2012-5-31'
GROUP BY
,CASE
WHEN SOURCE IN ('INTERNET', 'YOUTUBE', 'GOOGLE', 'BING', 'MSN', 'YAHOO') THEN 'INTERNET'
ELSE 'DIRECT MAIL'
END,
KEY1,
KEY3
ORDER BY ORG ASC, STATUS
May 31, 2012 at 1:06 pm
Nevermind...my solution was wrong 🙁
May 31, 2012 at 3:50 pm
I wasn't aware you could put the case statements into the GROUP BY clause...that's interesting! I need to be more daring in writing code 🙂
Unfortunately the query doesn't work because it's looking for SOURCE in the GROUP BY clause. I enter it in and it doesn't give me any results.
I enter the case as follows:
CASE
...
END AS SOURCE
And it throws an error stating there's an error 'Incorrect syntax near the keyword 'AS'.'
May 31, 2012 at 4:00 pm
joshd 1807 (5/31/2012)
I wasn't aware you could put the case statements into the GROUP BY clause...that's interesting! I need to be more daring in writing code 🙂Unfortunately the query doesn't work because it's looking for SOURCE in the GROUP BY clause. I enter it in and it doesn't give me any results.
I enter the case as follows:
CASE...
END AS SOURCE
And it throws an error stating there's an error 'Incorrect syntax near the keyword 'AS'.'
Which table is the column SOURCE located? Add the table alias to the column name in the case statement in the GROUP BY clause. Do not alias the CASE in the GROUP BY.
FYI, it is a good practice to use the table alias on all your columns, especially in multi-table queries.
May 31, 2012 at 4:12 pm
joshd 1807 (5/31/2012)
I wasn't aware you could put the case statements into the GROUP BY clause...that's interesting! I need to be more daring in writing code 🙂Unfortunately the query doesn't work because it's looking for SOURCE in the GROUP BY clause. I enter it in and it doesn't give me any results.
I enter the case as follows:
CASE...
END AS SOURCE
And it throws an error stating there's an error 'Incorrect syntax near the keyword 'AS'.'
Get rid of the "AS SOURCE" - you can't use a column alias in the GROUP BY clause.
So for example:
SELECT
CASE WHEN Something = 'Some value' then 1
ELSE 0 END AS MY_ALIAS
FROM
SOME_TABLE
GROUP BY
CASE WHEN Something = 'Some value' then 1
ELSE 0 END /* <-- No alias! */
June 1, 2012 at 9:10 am
If you want to display the results of the CASE expression and use it in a GROUP BY, you can use a CROSS APPLY to define and alias the CASE Statement.
SELECT cs.Source
FROM YourTable
CROSS APPLY (
SELECT CASE ... END AS Source
) AS cs
GROUP BY cs.Source
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 5, 2016 at 6:29 am
Thank you so much
February 19, 2016 at 8:42 am
For What it is worth, If I have to repeat the CASE logic in the WHERE or GROUP clauses, I prefer to wrap the original query as a CTE and then select from the CTE using the converted values. I find this cleaner to read and it means that if the CASE logic changes, only have to change it in one place. As far as I am aware the query plans that will be generated will be identical.
Even better, if you have a 1:many conversion, put an @inMemory or #temp table in the top of the script and populate the conversion data there, This helps to keep the main query clean, makes the maintenance of the script simpler as you can only have to configure the variables at the top of the script and should the relationships ever become more complicated (e.g. based on two distinct attributes) then it is simpler to modify the table than to re-write the entire CASE statement.
The other thing you need to be wary of is that the CASE statement will be triggered by the first matching rule, so the order of the WHENs can be important and have to go from most specific to least specific with the ELSE clause being a catch-all
Normally when you see this sort of CASE there is a missing hierarchical or attribute relationship in the domain and if possible you should be investing the development time in capturing the data within the domain model. What happens if the source comes in via Twitter, or a smart TV,or a microwave link?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply