CTE where amount is > +- 10%

Question

Post reply

CTE where amount is > +- 10%

caippers

SSC-Addicted

Points: 472
More actions
February 27, 2014 at 9:58 am

#288665

I am trying to do a trending query that will take an actual production count and compare that to the average daily count for that day. I then want a value (in this case the percent) to come back if the amount is + or - 10
Here is what I have so far:
WITH ProductionCnt
AS (SELECT DATENAME(dw, GETDATE()) AS Todays_Date
,COUNT(srs.ProviderID) AS [Count]
FROM healthmaster.inv.SurveyRespondentToSurvey srs
WHERE CAST(SubmittedDate AS NVARCHAR) = CAST(CURRENT_TIMESTAMP AS DATE)
and srs.SourceCode = 'HGRDSURVEY'
),
AvgCnt
AS (SELECT da.DataSourceShortDescription
,da.DayOfWeekName
,CAST(da.AvgSurveys AS INT) AS [Count]
FROM BUODS1_20140201.dbo.DailyAverage da
WHERE da.DayOfWeekName = DATENAME(dw, GETDATE())
AND DataSourceShortDescription = 'HGRDSURVEY'
)
SELECT CONVERT(DECIMAL(16, 2), (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.DayOfWeekName = ProductionCnt.Todays_Date
WHERE AvgCnt.DayOfWeekName = DATENAME(dw, GETDATE())
AND avgCnt.DataSourceShortDescription = 'HGRDSURVEY'
AND CONVERT(DECIMAL(16, 2), (CAST(ProductionCnt.Count AS FLOAT)
- CAST(avgcnt.Count AS FLOAT))
/ CAST(avgcnt.Count AS FLOAT) * 100) > 10 or AvgCnt.DayOfWeekName = DATENAME(dw, GETDATE())
AND avgCnt.DataSourceShortDescription = 'HGRDSURVEY'
AND CONVERT(DECIMAL(16, 2), (CAST(ProductionCnt.Count AS FLOAT)
- CAST(avgcnt.Count AS FLOAT))
/ CAST(avgcnt.Count AS FLOAT) * 100) > -10
I keep returning no value but if I take off the last AND clause and get the actual value back it's -89.22. I know the issue is with the last and clause I'm just not sure what I'm missing in this query.
Any help would be appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

caippers SSC-Addicted Points: 472 More actions · Answer 1

I'm apparently not awake this morning. I found the solution. My less then should have been -89 < -10 instead of <

mickyT SSChampion Points: 10360 More actions · Answer 2

Hi

You could wrap your percentage calculation up in an absolute and just check that it's greater than 10.

...

AND ABS((CAST(ProductionCnt.Count AS FLOAT) - CAST(avgcnt.Count AS FLOAT)) / CAST(avgcnt.Count AS FLOAT) * 100) > 10

...

Also I removed the convert as it's not required. Your whole where clause could then be written

WHERE AvgCnt.DayOfWeekName = DATENAME(dw, GETDATE())

AND avgCnt.DataSourceShortDescription = 'HGRDSURVEY'

AND ABS(CAST(ProductionCnt.Count AS FLOAT) - CAST(avgcnt.Count AS FLOAT)) / CAST(avgcnt.Count AS FLOAT) * 100) > 10

caippers SSC-Addicted Points: 472 More actions · Answer 3

Thanks so much for the tips! I'm pretty new at SQL Server so I love the tips.