[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# How can I perform a variance on two fields and utilize that result in a where clause.

Posted on 2008-11-03
Medium Priority
189 Views
I'm trying to do something like this:
DECLARE
@date AS INT,
@prior AS INT
SELECT
@date = 6757,
@prior = 6575

SELECT
a.countydimid, a.State, a.County, a.planname,
a.TotalLives, b.TotalLives
FROM
dbo.vMMSCountyManagedMarketShare AS a
INNER JOIN dbo.vMMSCountyManagedMarketShare AS b
ON b.dailytimedimid = @Prior
AND b.planid = a.planid
AND b.countydimid = a.countydimid
WHERE
a.dailytimedimid = @date
AND a.planid IS NOT NULL
AND Var(a.TotalLives, b.TotalLives) > .05
0
Question by:pepper777
• 4
• 2

LVL 60

Expert Comment

ID: 22870713
Try this as:
``````DECLARE
@date AS INT,
@prior AS INT
SELECT
@date = 6757,
@prior = 6575

SELECT
a.countydimid, a.State, a.County, a.planname,
a.TotalLives, b.TotalLives
FROM
dbo.vMMSCountyManagedMarketShare AS a
INNER JOIN dbo.vMMSCountyManagedMarketShare AS b
ON b.dailytimedimid = @Prior
AND b.planid = a.planid
AND b.countydimid = a.countydimid
WHERE
a.dailytimedimid = @date
AND a.planid IS NOT NULL
AND ABS(a.TotalLives - b.TotalLives) > .05
``````
0

Author Comment

ID: 22875806
The .05 is a percentage:

AND Var(a.TotalLives, b.TotalLives) > .05%
0

LVL 60

Expert Comment

ID: 22876098
pepper777,

Try this then.  Variance should be the difference in the two numbers as a percentage of first number, correct?  Therefore, I wrapped with absolute value so it can be +/- .05%.

mwvisa1
``````DECLARE
@date AS INT,
@prior AS INT
SELECT
@date = 6757,
@prior = 6575

SELECT
a.countydimid, a.State, a.County, a.planname,
a.TotalLives, b.TotalLives
FROM
dbo.vMMSCountyManagedMarketShare AS a
INNER JOIN dbo.vMMSCountyManagedMarketShare AS b
ON b.dailytimedimid = @Prior
AND b.planid = a.planid
AND b.countydimid = a.countydimid
WHERE
a.dailytimedimid = @date
AND a.planid IS NOT NULL
AND ABS((a.TotalLives - b.TotalLives) * 1.0/a.TotalLives) > .05
``````
0

Author Comment

ID: 22877312
We have already tried that and that will function for most of the values except for those which are zero. SQL will throw an error for a.TotalLives if it is equal to zero and we need to be able to account for those values which are equal to zero.
0

LVL 60

Expert Comment

ID: 22879000
If you have zero's in data then do this:
``````DECLARE
@date AS INT,
@prior AS INT
SELECT
@date = 6757,
@prior = 6575

SELECT
a.countydimid, a.State, a.County, a.planname,
a.TotalLives, b.TotalLives
FROM
dbo.vMMSCountyManagedMarketShare AS a
INNER JOIN dbo.vMMSCountyManagedMarketShare AS b
ON b.dailytimedimid = @Prior
AND b.planid = a.planid
AND b.countydimid = a.countydimid
WHERE
a.dailytimedimid = @date
AND a.planid IS NOT NULL
AND CASE a.TotalLives WHEN 0 THEN 0 ELSE ABS((a.TotalLives - b.TotalLives) * 1.0/a.TotalLives) END > .05
``````
0

LVL 60

Accepted Solution

Kevin Cross earned 2000 total points
ID: 22879017
And actually, you probably want that to show as a variance if b.TotalLives is not zero, so can do this:
``````DECLARE
@date AS INT,
@prior AS INT
SELECT
@date = 6757,
@prior = 6575

SELECT
a.countydimid, a.State, a.County, a.planname,
a.TotalLives, b.TotalLives
FROM
dbo.vMMSCountyManagedMarketShare AS a
INNER JOIN dbo.vMMSCountyManagedMarketShare AS b
ON b.dailytimedimid = @Prior
AND b.planid = a.planid
AND b.countydimid = a.countydimid
WHERE
a.dailytimedimid = @date
AND a.planid IS NOT NULL
AND CASE a.TotalLives WHEN 0 THEN b.TotalLives ELSE ABS((a.TotalLives - b.TotalLives) * 1.0/a.TotalLives) END > .05
``````
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
###### Suggested Courses
Course of the Month18 days, 20 hours left to enroll