November 14, 2008 at 11:48 am
Hi Guys,
I need to know how to do division, subtraction and multiplation within an CASE WHEN statement... below is my TSQL.
CASE WHEN ColumnName IS NULL THEN 0 ELSE ((ColumnName1-ColumnName2)/ColumnName3)*100 END AS 'Alias'
Please help
--
:hehe:
November 14, 2008 at 11:55 am
November 14, 2008 at 11:56 am
hey my question is
the logic between ELSE and END
how do I write that out in TSQL ? or have I written it right?
--
:hehe:
November 14, 2008 at 12:00 pm
Yerp, that's correct as it is. Just add a SELECT to the beginning, and a FROM, and replace the column names with your actual field names, and you're in business. (You also don't need the single quotes around Alias)
IE.
SELECT CASE WHEN ColumnName IS NULL THEN 0 ELSE ((ColumnName1-ColumnName2)/ColumnName3)*100 END AS 'Alias'
FROM SomeTable
November 14, 2008 at 12:03 pm
Thanks!
--
:hehe:
November 14, 2008 at 12:06 pm
It comes back with an error... Do you want to see my full query?
--
:hehe:
November 14, 2008 at 12:08 pm
SELECT CalDay5.Yr AS C0,
CalDay5.CalDt AS C1,
Loc.Alias,
COUNT(Loc.Loc_PK) AS LocsReported,
CalDay5.PerOfYrNbr AS C3,
CalDay5.WkOfYrNbr AS C4,
REPLACE(S.name,'ZAXBY/','') AS POS,
--LR.BusDt,
LR.AdjNetSales,
LR.CompCurrSales,
LR.CompHistSales,
SELECT CASE WHEN LR.CompCurrSales IS NULL THEN 0 ELSE (('LR.CompCurrSales'-'LR.CompHistSales'/'LR.CompHistSales')*(100)) END AS 'Comp Sales Percentage' FROM Fct_LocRollup,
FROM Fct_LocRollup LR, Loc Loc, Sys S,
( /*Derived Table-->*/
SELECT CalDay.CalDt AS CalDt,
CalDay.Yr AS Yr,
CalDay.PerOfYrNbr AS PerOfYrNbr,
CalDay.WkOfYrNbr AS WkOfYrNbr,
CalDay.DayOfWkNbr AS DayOfWkNbr
FROM dbo.CalDay CalDay
WHERE CalDay.CalID = '9da74ff3-74a5-46cb-a4ef-ee5f1409b0d8'
)
CalDay5 /*<--Derived Table*/
WHERE LR.Cal_PK = 21
AND LR.Loc_PK = Loc.Loc_PK
AND Loc.SysID = S.SysID
AND Loc.Alias NOT in ('00000001', '0000002', '0000003', '0000004', '00501', '9999', 'POSI_00001', 'POSI_00002', 'SQUIRREL_00002', 'SQUIRREL_00003', 'SQUIRREL_00004')
--AND LR.BusDt >= '1/1/2006'
AND LR.XTD = 2
AND UPPER(DATENAME(DW,LR.BusDt)) = 'SUNDAY'
AND CalDay5.Yr between 2006 and 2008
AND CalDay5.DayOfWkNbr between 7 and 7
AND CalDay5.CalDt = LR.BusDt
GROUP BY --LR.BusDt,
LR.AdjNetSales,
LR.CompCurrSales,
LR.CompHistSales,
CalDay5.Yr,
CalDay5.CalDt,
Loc.Alias,
CalDay5.PerOfYrNbr,
CalDay5.WkOfYrNbr,
S.name
ORDER BY C1, /*LR.BusDt,*/ Loc.Alias
--
:hehe:
November 14, 2008 at 12:15 pm
Whats the difference between Column names and Field Names? I thought both were same
--
:hehe:
November 14, 2008 at 12:28 pm
What is the error??
November 14, 2008 at 12:36 pm
Remove single quotes from (('LR.CompCurrSales'-'LR.CompHistSales'/'LR.CompHistSales')*
You are working with the field values, not with the literals.
November 14, 2008 at 12:39 pm
You've been attacked by Single quotes! FIND COVER! (Sorry, it's Friday, and I need a beer)(And the Select and from's I told you to add were from when I thought you were running this by itself) Additionally, you're using old syntax, which I've converted into JOIN's. Give this a shot.
[font="Courier New"]SELECT CalDay5.Yr AS C0,
CalDay5.CalDt AS C1,
Loc.Alias,
COUNT(Loc.Loc_PK) AS LocsReported,
CalDay5.PerOfYrNbr AS C3,
CalDay5.WkOfYrNbr AS C4,
REPLACE(S.name,'ZAXBY/','') AS POS,
--LR.BusDt,
LR.AdjNetSales,
LR.CompCurrSales,
LR.CompHistSales,
CASE WHEN LR.CompCurrSales IS NULL
THEN 0
ELSE (((LR.CompCurrSales-LR.CompHistSales)/LR.CompHistSales)*(100))
END AS [Comp Sales Percentage]
FROM Fct_LocRollup LR
INNER JOIN Loc Loc ON LR.Loc_PK = Loc.Loc_PK
INNER JOIN [Sys] S ON Loc.SysID = S.SysID
INNER JOIN ( SELECT CalDay.CalDt AS CalDt, --Derived Table
CalDay.Yr AS Yr, CalDay.PerOfYrNbr AS PerOfYrNbr,
CalDay.WkOfYrNbr AS WkOfYrNbr, CalDay.DayOfWkNbr AS DayOfWkNbr
FROM dbo.CalDay CalDay
WHERE CalDay.CalID = '9da74ff3-74a5-46cb-a4ef-ee5f1409b0d8'
) CalDay5 ON CalDay5.CalDt = LR.BusDt
WHERE LR.Cal_PK = 21
AND Loc.Alias NOT IN ('00000001', '0000002', '0000003', '0000004', '00501', '9999', 'POSI_00001', 'POSI_00002', 'SQUIRREL_00002', 'SQUIRREL_00003', 'SQUIRREL_00004')
--AND LR.BusDt >= '1/1/2006'
AND LR.XTD = 2
AND UPPER(DATENAME(DW,LR.BusDt)) = 'SUNDAY'
AND CalDay5.Yr BETWEEN 2006 AND 2008
AND CalDay5.DayOfWkNbr BETWEEN 7 AND 7
GROUP BY --LR.BusDt,
LR.AdjNetSales, LR.CompCurrSales, LR.CompHistSales, CalDay5.Yr,
CalDay5.CalDt,Loc.Alias,CalDay5.PerOfYrNbr,CalDay5.WkOfYrNbr,
S.[name]
ORDER BY C1, /*LR.BusDt,*/ Loc.Alias
[/font]
November 14, 2008 at 12:41 pm
Hey Glen... Thanks for the information... it not only correct my problem but also I learned soemthing new..........
Sorry.. i'm kinda new to SQL development..
THANK YOU!
--
:hehe:
November 14, 2008 at 12:47 pm
Hey Thanks buddy.. yes not your fault... I'm sorry I didnt let you know that it wasnt the only thing I was working with.. but is your query more efficient?
It looks nicer than mine.. I could possibly impress my manager with it!! 😀 It just took 3 more seconds than the other one to execute... regardless THANK YOU Garadin!
--
:hehe:
November 14, 2008 at 12:47 pm
Sure. Good luck and take a look on syntacs suggested in Garadin message. Not that old syntacs is not going to work, but since you are new to SQL server it is better to learn how to use it the proper way from the beginning 🙂
November 14, 2008 at 12:53 pm
Omair Aleem (11/14/2008)
Hey Thanks buddy.. yes not your fault... I'm sorry I didnt let you know that it wasnt the only thing I was working with.. but is your query more efficient?It looks nicer than mine.. I could possibly impress my manager with it!! 😀 It just took 3 more seconds than the other one to execute... regardless THANK YOU Garadin!
Nope, shouldn't be any more efficient... it's just using newer syntax. These two statements are exactly the same:
SELECT A.*
FROM TableA A, TableB B
WHERE A.ID = B.ID
SELECT A.*
FROM TableA A
INNER JOIN TableB B ON A.ID = B.ID
I did make one change (I doubt it'd account for 3 seconds difference, but it's probably one you want to make anyways) to your case statement that I didn't note; I added a set of parens.
(LR.CompCurrSales-LR.CompHistSales)
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply