Counting Data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Counting Data

Author

Topic

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2012-07-04 : 14:15:57

I am trying to count how many rows in the Invoice_Lines table have an Approved_Price that is +/- 50.00 difference from the PO_Price.

Everything I come up with is wrong because I can't figure out how to get that 50.00 criteria in the query. Not only that, but the only thing I can think to do is use a case statement to figure out if the Approved_Price is above or below the PO_Price (not necessarily by how much - just that it is above or below) and then the count is wrong because it counts each record singly and starts over with 1 each time. (The results were 6000+ records displayed in the editor with the number 1 next to it.)

Here is my code for what it is worth - but any help with solving my problem and better code would be much appreciated.

declare @LoDate as datetime
declare @HiDate as datetime

set @LoDate = '06/01/2012'
set @HiDate = '06/30/2012'

select
CASE
WHEN APPR_PRICE > PO_PRICE then COUNT(*)
WHEN APPR_PRICE < PO_PRICE then COUNT(*)
END TOTAL_LINES

from INV_LINE
where REC_UPDATE_DATE >= @LoDate and
REC_UPDATE_DATE <= @HiDate

group by APPR_PRICE, PO_PRICE

SLReid
Forum Newbie
Renton, WA USA

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-04 : 15:19:25

You should put the case expression inside the COUNT (or SUM as I have done below). Also, you don't need the group by clause.

DECLARE @LoDate  AS DATETIME 
DECLARE @HiDate  AS DATETIME

SET @LoDate = '06/01/2012'
SET @HiDate = '06/30/2012'

SELECT 
	SUM(CASE WHEN ABS(APPR_PRICE-PO_PRICE) > 50 THEN 1 ELSE 0 END) AS Total_lines,
	SUM(CASE WHEN APPR_PRICE-PO_PRICE > 50 THEN 1 ELSE 0 END) AS Appr_Price_Greater,
	SUM(CASE WHEN APPR_PRICE-PO_PRICE < -50 THEN 1 ELSE 0 END) AS App_Price_Lower
FROM   INV_LINE
WHERE  REC_UPDATE_DATE >= @LoDate
       AND REC_UPDATE_DATE <= @HiDate;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-04 : 18:52:18

is 50 a hardcoded criteria or do you want it to be based on user input? If yes, probably worth making it into a parameter

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2012-07-05 : 15:19:28

Excellent - that worked. Thank you very much!! I am not sure I totally understand ABS - but I get what it is doing in this instance. As for the question visakh16 posed - yes, 50 is hardcoded. It is the tolerance level (amount) that an invoice can differ from a PO and still be paid without being marked discrepent and being sent back to the purchusor for review. This is a system setting that is rarely if ever changed.

SLReid
Forum Newbie
Renton, WA USA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-05 : 15:34:53

quote:
Originally posted by sherrireid

Excellent - that worked. Thank you very much!! I am not sure I totally understand ABS - but I get what it is doing in this instance. As for the question visakh16 posed - yes, 50 is hardcoded. It is the tolerance level (amount) that an invoice can differ from a PO and still be paid without being marked discrepent and being sent back to the purchusor for review. This is a system setting that is rarely if ever changed.

SLReid
Forum Newbie
Renton, WA USA

ABS stands for absolute value

see

http://msdn.microsoft.com/en-us/library/ms189800.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources