Compute Clause????

Question

Compute Clause????

xsaycocie

Grasshopper

Points: 22
More actions
December 12, 2008 at 2:32 pm

#72267

--The compute clause works great...but is there a way to have the results as part of the select query? Or somehow pass that on to a variable?
Select
Case when
(AVG(cip.OriginalInvoiceAmount) = 0 ) then 0
else Round((1-(AVG(ci.udf_PaymentAmount)/AVG(cip.OriginalInvoiceAmount)))*SUM(cii.NetCompleteValue),2) end TT
From
ContractInvoiceItems cii
LEFT JOIN ContractInvoices ci
ON ci.ContractInvoiceID = cii.ContractInvoiceID
LEFT JOIN PotentialCOItems pcoi
ON pcoi.PCOItemID = cii.PCOItemID
LEFT JOIN Projects p
ON p.ProjectID = cii.ProjectID
LEFT JOIN Contracts c
ON c.ContractID = ci.ContractID
LEFT JOIN ContractInvoicePayments cip
ON cip.ContractInvoiceID = ci.ContractInvoiceID
LEFT JOIN Budget b
ON b.BdgtCodeID = cii.BdgtCodeID
Where
ci.InvoiceNumber = 'LCSTeat0124Nov008'
Group By
p.Sequence, ci.ProjectID, b.Group3, b.Group4, c.FirstWitness, ci.InvoiceNumber,
ci.ContractInvoiceId, pcoi.SCONumber, ci.ContractID, pcoi.ChangeOrderID, pcoi.SortOrderCO, cii.Udf_LineValidated
Compute sum(Case when
(AVG(cip.OriginalInvoiceAmount) = 0 ) then 0
else Round((1-(AVG(ci.udf_PaymentAmount)/AVG(cip.OriginalInvoiceAmount)))*SUM(cii.NetCompleteValue),2)end)

Viewing 12 posts - 1 through 12 (of 12 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

To return it to a variable it would have to be in a separate query. To get results in the same dataset, you will either need to add extra columns and/or use GROUP BY with WITH ROLLUP or WITH CUBE.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

xsaycocie Grasshopper Points: 22 More actions · Answer 2

Yep did that with a group by but the result is .01 off

The above return the following:

TT

---

3.05

7.64

2.55

25.45 and the compute total of 38.69

When I group by... the total is 38.68

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

This is neither unexpected nor necessarily incorrect. It is inevitable when calculating sums from fractional amounts that different accumulation orders and truncation points will result in small fractional differences in the results.

If you want to increase the precision of the similarity between these two results, the easiest way is to add more decimal places to the source data and the intermediate calculations.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Try your ROLLUP results with the following changes to you source precisions:

Select

Case when

(AVG(CAST(cip.OriginalInvoiceAmount as MONEY)) = 0 ) then 0

else Round((1-(AVG(CAST(ci.udf_PaymentAmount as MONEY))

/AVG(cip.OriginalInvoiceAmount)))*SUM(cii.NetCompleteValue),4) end TT

From

ContractInvoiceItems cii

LEFT JOIN ContractInvoices ci

ON ci.ContractInvoiceID = cii.ContractInvoiceID

LEFT JOIN PotentialCOItems pcoi

ON pcoi.PCOItemID = cii.PCOItemID

LEFT JOIN Projects p

ON p.ProjectID = cii.ProjectID

LEFT JOIN Contracts c

ON c.ContractID = ci.ContractID

LEFT JOIN ContractInvoicePayments cip

ON cip.ContractInvoiceID = ci.ContractInvoiceID

LEFT JOIN Budget b

ON b.BdgtCodeID = cii.BdgtCodeID

Where ci.InvoiceNumber = 'LCSTeat0124Nov008'

Group By

p.Sequence, ci.ProjectID, b.Group3, b.Group4, c.FirstWitness, ci.InvoiceNumber,

ci.ContractInvoiceId, pcoi.SCONumber, ci.ContractID, pcoi.ChangeOrderID,

pcoi.SortOrderCO, cii.Udf_LineValidated

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 5

xsaycocie (12/12/2008)
Yep did that with a group by but the result is .01 off
The above return the following:
TT
---
3.05
7.64
2.55
25.45 and the compute total of 38.69
When I group by... the total is 38.68

Nah... SUM and GROUP BY will never do that on fixed decimal place sums. There must be some other calculation that you are taking the sum of and displaying as a rounded number that actually has more precision that what you are displaying.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Fourth line of the code, "Round(..)" function.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 7

Dang... I gotta get new glasses or stronger coffee. I mean really... I didn't even see the ops first post last night, only the short one I quoted in my previous response.

Sorry guys.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

Heh, tell me about it. There really is no upside to getting older. 🙂

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 9

Sure there is... today, I'm on THIS side of the dirt!!! 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

Well yes, it does beat the alternative.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

xsaycocie Grasshopper Points: 22 More actions · Answer 11

xsaycocie

Grasshopper

Points: 22

December 13, 2008 at 9:56 pm

#912161

Thx guys I really appreciate the help.