[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
6
Medium Priority
?
189 Views
Last Modified: 2010-04-23
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
Comment
Question by:pepper777
  • 4
  • 2
6 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
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

Open in new window

0
 

Author Comment

by:pepper777
ID: 22875806
The .05 is a percentage:

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

Expert Comment

by:Kevin Cross
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

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:pepper777
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

by:Kevin Cross
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

Open in new window

0
 
LVL 60

Accepted Solution

by:
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

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question