Case and Grouping
-
I am having a problem when analysing a sum with data from 2 x tables (ITEMS,CUST):
I want to do the following (this is in english rather than SQL)
I want to return the value "ON STOP" when EITHER of the following conditions are met:
1. When the SUM(ITEMS.AMOUNT) grouped by ITEMS.CUSTOMER > CUST.CREDIT_LIMIT then "ON STOP"
or
2. When the SUM(ITEMS.AMOUNT) by ITEMS.CUSTOMER where ITEMS.DUE_DATE < GETDATE() -14 > 0 then "ON STOP"
The CUST table has single entries for the Customer (ie Customer main detail table)
The ITEMS table has multiple transaction records for customers.
mmmm...... Any ideas
Thanks in advance - Mark
-
You need to join the two tables. If you post the table definitions, I am sure we can help you write a join
I am a little puzzled by condition 2. Where does the sum come in? Do you want items or customers returned? And you probably don't mean "GETDATE() -14 > 0", do you?
-
Hi Jesper,
Thanks for the reply:
Sorry - to clear up what i mean in condition 2:
When the (SUM(ITEMS.AMOUNT) by ITEMS.CUSTOMER where ITEMS.DUE_DATE < GETDATE() -14) > 0 then "ON STOP"
in other words, sum the amount where the due_date is less than 2 weeks old, and when that sum is >0 then "ON STOP"
TABLES as follows:
CUST TABLE
CUSTOMER CREDIT_LIMIT
CUST01 1000
CUST02 800
CUST03 1500
ITEMS TABLE
CUSTOMER DUE_DATE AMOUNT
CUST01 12/07/2005 60.00
CUST01 20/07/2005 100
CUST02 01/07/2005 85.50
CUST03 01/08/2005 45
CUST03 30/06/2005 150.00
CUST03 14/06/2005 34.90
ETC....
Cheers,
Mark
-
oops... and to answer your other point Jesper,
I would like CUSTOMER,"ON STOP" returned - maybe also, where conditions are NOT met - "OK" instead of "ON STOP" ???
Thanks again
-
Maybe something like this?
create table CUST
(CUSTOMER varchar(10), CREDIT_LIMIT int)
go
create table ITEMS
(CUSTOMER varchar(10), DUE_DATE datetime, AMOUNT decimal(6,2))
go
insert into CUST
select
'CUST01', 1000
union all select
'CUST02', 800
union all select
'CUST03', 1500
insert into ITEMS
select
'CUST01', '2005-07-12', 60.00
union all select
'CUST01', '2005-07-20', 100
union all select
'CUST02', '2005-07-01', 85.50
union all select
'CUST03', '2005-08-01', 45
union all select
'CUST03', '2005-06-30', 150.00
union all select
'CUST03', '2005-06-14', 34.90
select cust.customer, case when total1.total > cust.credit_limit or total2.total > 0 then 'ON STOP' else 'OK' end
from cust inner join
(
select cust.customer as customer, sum(items.amount) as total
from cust left join items on cust.customer = items.customer
group by cust.customer
)
total1
on cust.customer = total1.customer
inner join
(
select cust.customer as customer, sum(items.amount) as total
from cust left join items on cust.customer = items.customer and
datediff(d, items.due_date, getdate()) < 14
group by cust.customer
)
total2
on cust.customer = total2.customer
-
PERFECT!
Thanks so much for that Jesper
-
A couple of suggestions:
1. Don't query the same table twice if you can get both results in one pass
2. Group the "many" side of a one-to-many relation before joining
3. Compare the date column to a DATEADD(,,GETDATE()) expression (which will be evaluated once) rather than evaluating DATEDIFF(,fld,GETDATE()) for every row
It also seems that you need to filter out only unpaid bills, unless records are removed from the items table when the bill is paid.
select c.customer, ISNULL(i.AccountBalance, 0) as AccountBalance,
case when i.AccountBalance > c.credit_limit or i.PastDue > 1 then 'ON STOP' else '' end as Status
from customer c
left join (
select customer, sum(items.amount) as AccountBalance,
sum(case when due_date < dateadd(d, -14, getdate()) then 1 else 0 end) as PastDue
from items
{ where Unpaid = 1 ? }
group by customer
) i on c.customer = i.customer
-
You certainly have some points there and your query is definitely simpler
I am not sure your query meets condition 2 of the original query if amounts can be negative. Maybe they can't, but I guess there is a reason to require that the sum is > 0 and not just one of the amounts (in any case, i.PastDue should probably be > 0 in your query). Therefore I have rewritten your query as follows:
select c.customer, ISNULL(i.AccountBalance, 0) as AccountBalance,
case when i.AccountBalance > c.credit_limit or i.PastDue > 0 then 'ON STOP' else 'OK' end as Status
from cust c
left join (
select customer, sum(items.amount) as AccountBalance,
sum(case when due_date < dateadd(d, -14, getdate()) then items.amount else 0 end) as PastDue
from items
group by customer
) i on c.customer = i.customer
Thanks for your post, Scott, I feel that I have learned something (but I guess that the point in participating here
). -
Guys,
Thanks for the further comments - i'm actually gonna stick with the original (Jesper) query as I understand that - the further posts make my brain go mushy as i'm from Devon 🙂
Again though, thanks for all the input.
Mark
-
Mark: If someone suggests a different query might be better, or you can think of two or more versions yourself but can't decide which to use, put both versions in Query Analyzer and get the execution plan (press control-L). It will tell you which one it thinks will be better for your data. If they split 50-50 or even 45-55, go with the one you find more readable (understandable, maintainable, etc). If they split 80-20 or 98-2, you will have learned something useful if you can figure out why. (Assuming both versions produce correct results.) I can't overemphasize how quickly my SQL improved after I discovered this technique.
Jesper: You're correct that "> 1" should have been "> 0".
Changing "else '' " to "else 'OK' " is not necessarily an improvement, it depends on user requirements. It the results are going to be printed or displayed directly then the 'ON STOP' will stand out much better if the other rows are blank. But this could be any literal string (or NULL) without affecting performance.
Changing the 1 to items.amount when calculating PastDue might be closer to the stated requirement of past due balance > 0. I was focusing on checking for unprocessed past due items in general, but whether negative amounts might occur is not stated. It does seem wrong to put an account on hold because they are owed a credit.
If credits are possible then a more detailed statement of the business rules is required. Are credits entered as separate items with a negative amount, or is the original item deleted or updated to a zero amount? If the desired test is whether there are outstanding payments due in spite of any credits, it might be " SUM(CASE WHEN due_date < dateadd(...) AND amount > 0 THEN 1 ELSE 0 END) ". There is usually a delay between the order and any credit, so there will be a period where the original item is older than 14 days but the corresponding credit isn't, and none of the queries suggested so far will handle this. Maybe past due payments AND all credits need to be summed separately, and the final test is whether past due payments are greater than all credits to date.
I'm still curious whether the items table only contains unpaid items, or whether a test for payment status is required. Here's another version, and I put the unpaid test back in.
select c.customer, ISNULL(i.AccountBalance, 0) as AccountBalance,
case when i.AccountBalance > c.credit_limit or i.PastDuePayment > TotalCredit then 'ON STOP' else '' end as Status
from customer c
left join (
select customer, sum(items.amount) as AccountBalance,
sum(case when due_date < dateadd(d, -14, getdate()) and amount > 0 then amount else 0 end) as PastDuePayment,
sum(case when amount < 0 then amount else 0 end) as TotalCredit
from items
{ where Unpaid = 1 ? }
group by customer
) i on c.customer = i.customer
-
Scott, Mark has stated that he wants 'OK' back. Furthermore, he has stated quite clearly what the condition on past due amounts should be. You may of course start a discussion of his business rules, or suggest simplifications or improvements, but I don't think you should write code that reflects what you think his business rules should be rather than what he has stated they are. After all, he knows that better than anyone here
-
Jesper: My orignal comment to Mark was to try to help him evaluate alternative queries that he may not immediatly understand. If the benefit of the alternate version is not obvious, then finding out whether it is a 1% improvement or a 1000% improvement will tell you whether it is worth spending time on. This is a useful learning tool outside of any discussion about this particular query.
I don't think the problem statement included every detail of the business rules, nor do I think it should. If you put a complete scope document at the front of a post, most people won't read all the way down to your actual question. But as far as inventing business rules, you're the one who brought up negative amounts. I think it is correct to point out that if your suggestion is possible, there are a lot of rules missing for trying to deal with them.
-
The negative amount only came up because I wanted to say that your query might produce a different output. As I said, this may not be the case, I don't know. But I think Mark has stated clearly what he wants. Of course you don't state every business rule when posing a question here, but that just means that you will carry out any modifications yourself. My impression is that he has the output he wants, and I think we should concentrate on finding the best query that produces this output.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply