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

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
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chief Technology OfficerCommented:
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 Commented:
The .05 is a percentage:

AND Var(a.TotalLives, b.TotalLives) > .05%
0
Chief Technology OfficerCommented:
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 Commented:
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
Chief Technology OfficerCommented:
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
Chief Technology OfficerCommented:
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

Experts Exchange Solution brought to you by