August 12, 2004 at 3:12 am
hi!
i have a stored procedure that is used for debt collection. the query sums the amount owed by each debtor if the transaction date is 90 or more days before the parameter date entered by the user (asp.net front- end) so i get an output like this in a spreadsheet.
Debtor No Amount Outstanding
1 1000
2 5555
3 600
i need to alter this so that i get 3 more columns so that the output would look like this
Debtor No Amount Outstanding 1-30 days 31-60d ays 61-90 days
so that i get a break down of what is less than 30 days overdue, less than 60 days overdue and what is 90 days overdue.
currently i use:
convert(datetime,convert(varchar(8),SE.TRANS_DATE)) <= DATEADD(DAY, -90, @pDateEntered)
in the where clause
and i select Debtor_Number, SUM(Amount)
i have tried using nested select statements but the SUM function on the AMOUNT is giving me back the entire grand total for all debtors.
i really would appreciate any suggestion as to how i would go about doing this.
thanks
August 12, 2004 at 6:03 am
You are going about it the wrong way. You need to use a case statement when generating anything that looks like a pivot table. Checks BOL for examples. For what you want, you would want something like this:
table - product
columns(id, code, amount, pdate)
select id,
CASE WHEN datediff(d, pdate,getdate()) 61
THEN amount else 0 end as OVER2M
from product
Gives:
id under1m between1and2m over2m
12.50.0 0.0
20.05.5 5.5
30.00.0 0.0
This will only give you a line by line analysis, not a sum per group. This article is another way that is more complicated. I will take a look at it myself when I get the time.
http://www.sqlteam.com/item.asp?ItemID=2955
August 12, 2004 at 7:12 am
Just had a brainwave and have found how to sum by group OK.
Use
select id,
sum(CASE WHEN datediff(d, pdate,getdate()) 61
THEN amount else 0 end) as OVER2M
from product
group by id
I was trying to sum in the wrong place.
August 17, 2004 at 3:21 am
thanks - this really pointed me in the right direction - i ended up using the case statement so that i could sum the amount owed by debtors based on the amount of days the transaction date was from the date entered - i was then able to say well these debtors are between 1 ans 30 overdue with this payment and between 31 and 60 days overdue with this payment etc.
my sql looks like this:
SELECT Debtor_Number, SUM(case WHEN (convert(datetime,convert(varchar(8),TRANS_DATE)) <= DATEADD(DAY, -0, @pDate)
and convert(datetime,convert(varchar(8),TRANS_DATE)) >= DATEADD(DAY, -30, @pDate))
then (AMOUNT*-1) else 0 end) as "1-30 Days",
SUM(case WHEN (convert(datetime,convert(varchar(8),TRANS_DATE)) <= DATEADD(DAY, -31, @pDate)
and convert(datetime,convert(varchar(8),TRANS_DATE)) >= DATEADD(DAY, -60, @pDate))
then (AMOUNT*-1) else 0 end) as "31-60 Days", ..................
thanks for your help
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply