April 8, 2014 at 2:18 pm
I have a query I'm trying to get a percent difference for Average Count vs Actual count.
I've gotten everything up and running except for the actual mathematics. I've tried to convert to decimal and cast as float for my numbers but for the life of me I cannot get the true % difference....
Here is my query.
WITH ProductionCnt
AS (SELECT DATENAME(dw, GETDATE()-1) AS Todays_Date
,COUNT(srs.ProviderID) AS [Count]
FROM healthmaster.inv.SurveyRespondentToSurvey srs
WHERE CAST(SubmittedDate AS NVARCHAR) = CAST(CURRENT_TIMESTAMP -1 AS DATE)
and srs.SourceCode = 'HGMOBILEWEB'
),
AvgCnt
AS (SELECT da.DayofWeek,
da.Count,
da.ConfirmedSourceCode
FROM HealthMaster.dbo.AvgDailyPESCount da
WHERE cast(da.DayofWeek as nvarchar) = DATENAME(dw, GETDATE()-1)
AND da.ConfirmedSourceCode = 'HGMOBILEWEB'
)
SELECT CONVERT(DECIMAL(3), (CAST(ProductionCnt.Count AS FLOAT)
- CAST(avgcnt.Count AS FLOAT))
/ CAST(avgcnt.Count AS FLOAT) * 100) AS Pctdiff
FROM ProductionCnt
INNER JOIN AvgCnt ON AvgCnt.DayofWeek = ProductionCnt.Todays_Date
If I query my CTE's individually I get the following numbers:
ProductionCnt = 1168
AverageCnt = 1172
I continue to get a -33 as my result... Can anyone help me figure out what I'm doing wrong with my calculations?
April 8, 2014 at 2:50 pm
Not sure what you are looking for as output. Using the values you stated and plugging them into your formula...
select (1168 - 1172) / 1172. * 100
This returns -0.341200
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 8, 2014 at 4:36 pm
4 / 1172.0 is less than 1%, ~0.34%.
To show less than 1%, change your code to this:
CONVERT(DECIMAL(5, 2), (CAST(ProductionCnt.Count AS FLOAT)
- CAST(avgcnt.Count AS FLOAT))
/ CAST(avgcnt.Count AS FLOAT) * 100) AS Pctdiff
My result showed 0, since CASTing 0.34 to decimal(3) rounds down to 0.
What % did you expect/want the answer to be?
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
April 8, 2014 at 4:47 pm
I'm losing my mind actually the calculation was correct I'm sorry for wasting anybody's time. I am kept transposing the amt in my head when writing it down.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply