` Comment on an Icetips Article
Icetips - Templates, Tools & Utilities for Clarion Developers

Templates, Tools and Utilities
for Clarion Developers

Add a comment to an Icetips Article

Please add your comments to this article. Please note that you must provide both a name and a valid email address in order for us to publish your comment. Comments are moderated and are not visible until they have been approved. Spam is never approved!

Your Name:  
Email:  
Header text/Subject:  

Please enter your comment in the box below:
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 November 21, 2024, 6:39 am
This article has been viewed 35233 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

Login

User Name:

Password:


Gold Subscription
Buy Now
Silver Subscription
Buy Now
Solo Subscription
Buy Now
Multiple Copy Pricing
Renew Subscription