outstanding amounts - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

outstanding amounts

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-01-04 : 08:29:56

hi,
how can I show the sum of outstanding amounts grouped by month..
eg,
I have a person and payment table, a person has many payments.
for outstanding amounts I can do
totaldue - sum(payments).
but I need to show for example what was outstanding in november.
so I need to do
totaldue - sum(payments uptill november).
Is there a clever whay to do this for all months, or do I need to hardcode dates each month ?

regards,
j

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2008-01-04 : 08:35:09

Hi Jamie,
I think in another thread yesterday - I pointed out to you that you should make use of a calendar table.

Now this is a very good illustration of how important a calendar table is.
If you had a calendar table that had a column indicating an id for a month then this same month id could be used in your payments table and grouping by month would be a breeze.

unless I am missing the point here,
in which case it will be easier if you posted the ddl for your tables here.

Duane.

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-01-04 : 09:12:08

I have a originaldate in my person table with the totaldue,then in payments I have a paymentdate and amount paid..
Im thinking I should beable to use datediff and other functions possibly to work out the dates the payments fall into..

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2008-01-04 : 09:18:13

Surely with a "debtors" system like that there must be cut-off dates per month which aren't necessarily the same date each month.
I really think that you should look at using a calendar table in your design - it will make your life a lot easier - your problem from yesterday and todays problem would easily be solved using a calendar table.

Duane.

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-01-04 : 09:30:24

ok, cheers ditch

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-01-04 : 11:22:03

hi,
a quick question regarding the maths involved in working out what is outstanding..
say I have a person with 5000 total, and they have may 4 payments of 1000 each, how can I show the omount outstanding ?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-01-04 : 11:47:39

This might give you a sarting point.
create table #tbl (theDate datetime, theAmount int)

INSERT INTO #tbl

SELECT DATEADD(dd,val.number,'01/01/2007'),val.Number

from master..spt_values val
where number between 0 and 61
and type = 'P'

SELECT a.theDate,a.theAmount
,'MonthToDate' =
SUM (
CASE
WHEN a.theDate
<= DATEADD(month, DATEDIFF(month, -1, b.theDate), -1)
THEN b.theAmount
ELSE 0
END
)
,'YearToDate' = SUM(b.theAmount)
,'Weekly' =
SUM (
CASE
WHEN DATEPART(weekday,a.theDate) >= DATEPART(weekday,b.theDate)
and DATEPART(week,a.theDate) = DATEPART(week,b.theDate)
THEN b.theAmount
ELSE 0
END

)

from #tbl a
inner join
#tbl b
on
a.theDate >= b.theDate

group by a.theDate,a.theAmount
order by 1

Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources