October 4, 2007 at 6:26 pm
I'm writing a report and I'd like to be able to not show a result if a certain field is less than or equal to zero.
I don't even know where to start on this because I'm new to sql and I just haven't come across that example so far in documentation I've read.
If someone can post pseudo code using Northwind or pubs tables, I might be able to figure it out from there.
T.I.A.
JM
October 4, 2007 at 7:13 pm
October 5, 2007 at 3:16 pm
I guess I'm so new to sql, I don't know how to phrase the question.
The script I've written so far is returning something like...
visit_id pmt_amt
6854 89.92
6854 -89.92
7387 275.00
8103 75.85
8275 46.51
8712 505.50
8712 -505.50
6854 150.75
6854 -150.75
7387 -275.00
I'm trying to group visit_id's but I'm not having any success in the grouping but I'm still reading things I'm finding on grouping. I can't figure out how to do the grouping in QA but I know how to do that in Crystal Reports by throwing the sql code in as a command. I'd really like to figure that out for QA.
Anyway, I'm trying to sum the pmt field and anything that comes up less than or equal to zero, I don't want it showing. Can that be done?
October 5, 2007 at 3:47 pm
Two quick methods. The second one only if you need the negative total as well....
Method #1 - "include only credits"
select visit_ID, sum(pmt_amt) from [yourtablename] where pmt_amt>=0 GROUP BY visit_ID
Method #2 "tabulate debits and credits separately
select visit_ID, sum(case when pmt_amt>0 then pmt_amt else 0 end) as creditamt,
sum(case when pmt_amt>0 then 0 else pmt_amt end) as debitamt
from [yourtablename]
GROUP BY visit_ID
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 5, 2007 at 4:07 pm
I've tried method #1 and it gives the exact same results as my example in my previous post. I'll try method #2.
October 5, 2007 at 4:19 pm
one sec - you want only want TOTALS greater than zero? or only add up the positive lineitems?
If you only want positive totals, that's a little different - that's a HAVING statement (not WHERE).
select visit_ID, sum(pmt_amt) from [yourtablename] GROUP BY visit_ID
HAVING sum(pmt_amt)>0
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 5, 2007 at 4:28 pm
I want numbers summed up that have the same visit_id and if > 0, I'd like it listed. I'll try HAVING. Thanks.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply