January 29, 2014 at 1:59 am
Hi,
I have a query where I have used CASE statement to derive a column, how can I call this column in WHERE clause?
How do I assign as Alias to this column?
SELECT DISTINCT
R.[systemname] AS 'Market'
,SD.[Group] AS [Group]
,RT.[type] AS [Type]
,DATENAME (mm, SD.[StartDate]) As [Month]
,SUM(CASE SD.EstimateTime
WHEN '-' THEN 0
WHEN '0 (web)' THEN 0
WHEN '15 mins' THEN 15
WHEN '30 mins' THEN 30
WHEN '45 mins' THEN 45
WHEN '1 hour' THEN 60
WHEN '1 hr 15' THEN 75
WHEN '1hr 30' THEN 90
ELSE '0'
END )
AS 'Estimate'
,SUM(CASE SD.Actual
WHEN '-' THEN 0
WHEN '0 (web)' THEN 0
WHEN '15 mins' THEN 15
WHEN '30 mins' THEN 30
WHEN '45 mins' THEN 45
WHEN '1 hour' THEN 60
WHEN '1 hr 15' THEN 75
WHEN '1hr 30' THEN 90
WHEN '1 hr 45' THEN 105
ELSE '0'
END) AS 'Actual'
,'MGroup' = CASE
WHEN RRS.[system_name] = ('Cash') THEN ('Finance')
WHEN RRS.[system_name] =('Bank') THEN ('Finance')
WHEN RRS.[system_name] =('Bankers') THEN 'Finance'
END
,COUNT(*) AS Count
FROM [DBName].[dbo].[Tablename] AS RR
WHERE
SD.StartDate BETWEEN ('20140101') AND ('20140120') AND
MGroup in (@MGroup)
GROUP BY
[Group]
,[Market]
,[Type]
Thanks very much
January 29, 2014 at 2:03 am
You need to either use a subquery or CTE or to put the entire CASE in the where clause. The WHERE is evaluated before the aliases in the select are applied, hence you can't reference an alias in the where clause.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 29, 2014 at 2:47 am
I used Sub query and it worked.
Thanks
January 29, 2014 at 9:13 am
I think you might benefit from reading this book:
http://www.amazon.com/Microsoft-Server-2012-T-SQL-Fundamentals/dp/0735658145
Expands a bit more on Gail's response above.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply