|
Templates, Tools and Utilities
for Clarion Developers
|
 |
 |
|
Icetips Article
Back to article list
Search Articles
Add Comment
Printer friendly
Direct link
SQL Related Articles: Sum Calculations in select statement and filtering based on value
2003-02-05 -- Andy Stapleton
Newsgroups: TopSpeed.Products.Drivers
Let me take a stab at it...
SELECT
F.PayerID,
CAST(F.LastName + ', ' + F.FirstName AS VARCHAR(52)) AS 'Family
Name',
SUM(CASE WHEN L.TType = 'Charge' THEN L.Amount END) AS Charges,
SUM(CASE WHEN L.TType = 'Credit' THEN L.Amount END) AS Credits,
SUM(CASE WHEN L.TType = 'Payment' THEN L.Amount END) AS Payments,
SUM(CASE WHEN L.TType = 'Charge' THEN L.Amount WHEN L.TType =
'Credit' THEN 0 - L.Amount WHEN L.TType = 'Payment' THEN 0 - L.Amount END)
AS BalanceDue
FROM Ledger AS L, Family AS F
WHERE F.PayerID = L.PayerID
GROUP BY F.LastName, F.FirstName, F.PayerID
HAVING
SUM(CASE WHEN L.TType = 'Charge' THEN L.Amount
WHEN L.TType = 'Credit' THEN 0 - L.Amount WHEN L.TType = 'Payment' THEN 0 -
L.Amount
END) > 10.00
Have you tried the above?
--
Andy <> Stapleton
CIO DB|Wired.com / (ccs) Cowboy Computing Solutions
www.dbwired.com Web and Clarionet hosting
www.ccscowboy.com CCS SQL templates
www.Paywire.com ACH / Credit Card payment processing
"Ben Kim (Vertical Dimensions, Inc.)" wrote in
message news:3e4147d8$1@news.softvelocity.com...
> Hello all,
>
> I am trying to program a sql-list that shows:
>
> Payer ID Family Name Charges Credits Payments Balance
Due
>
> Every thing works as expected with one exception. If the user only wants
to
> see all balances over lets say $10, how do I build that into the statement
> below without getting an error: ERROR CONVERTING DATA TYPE VARCHAR TO
> NUMERIC
>
> SQL Statement:
> --------------------------------------------------------------------------
--
> ---
> SELECT
> F.PayerID,
> CAST(F.LastName + ', ' + F.FirstName AS VARCHAR(52)) AS 'Family
> Name',
> SUM(CASE WHEN L.TType = 'Charge' THEN L.Amount END) AS Charges,
> SUM(CASE WHEN L.TType = 'Credit' THEN L.Amount END) AS Credits,
> SUM(CASE WHEN L.TType = 'Payment' THEN L.Amount END) AS Payments,
> SUM(CASE WHEN L.TType = 'Charge' THEN L.Amount WHEN L.TType =
> 'Credit' THEN 0 - L.Amount WHEN L.TType = 'Payment' THEN 0 - L.Amount END)
> AS 'BalanceDue'
> FROM Ledger AS L, Family AS F
> WHERE F.PayerID = L.PayerID
> GROUP BY F.LastName, F.FirstName, F.PayerID
> HAVING CAST('BalanceDue' AS DECIMAL(19, 2)) >
> 10.00 <---tried BD > 10.00, CONVERT(DECIMAL...)
> --------------------------------------------------------------------------
--
> -
>
> I have tried CAST and CONVERT and nothing at all but HAVING 'BalanceDue' >
> 10.00 but get the same error. If I remove the HAVING clause it works like
a
> champ.
>
> Thanks Ahead Of Time!
>
> Ben Kim
> Using C5.5EE Latest Patches (ABC/Legacy 32-bit)
> MS-SQL 2K Enterprise Edition
> Windows XP Pro
>
>
Today is April 18, 2024, 2:45 pm
This article has been viewed 35113 times.
Google search
has resulted in 3112 hits on this article since January 25, 2004.
Back to article list
Search Articles
Add Comment
Printer friendly
|
|
|
