Obtaining a data spread - 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 2000 Forums

Transact-SQL (2000)

Obtaining a data spread

Author

Topic

WindChaser
Posting Yak Master

225 Posts

Posted - 2005-03-03 : 12:17:00

Hi folks.

I have a dataset of financial transactions where the clients incur debt, each taking place on a given date. Something like:

Date Debt
1. Mr.Turner 38503 $100
2. Mr.Johnson 38454 $200
3. Mr.Clark 38506 $500

The dates are in integer format. I would like to be able to get a spread of the debt for each client based on the age of the debt. Something like:
Debt < 30 days Debt < 60 days
1. Mr.Turner $100
2. Mr.Johnson $200
3. Mr.Clark $500

All I've been able to do so far is get the aged debt individually for each given age by doing:

Select Client, Sum(Debt) where Date > 38475 Group By Client
Select Client, Sum(Debt) where Date > 38445 AND Date <= 38475 Group By Client

But is there a statement that I can use to parse the debt appropriately in a single resultset to include all ages for all clients as per the second illustration above?

Thanks!

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-03-03 : 12:25:39

Have a look at CASE in BOL

Something like


SELECT Client, 
	SUM(CASE WHEN DATEDIFF(dd,YourDate,GETDATE()) < 30 THEN Debt ELSE 0 END) AS Under30Days,
	SUM(CASE WHEN DATEDIFF(dd,YourDate,GETDATE()) BETWEEN 30 AND 60 THEN Debt ELSE 0 END) AS 30to60Days
FROM YourTable
GROUP BY Client

Andy

Beauty is in the eyes of the beerholder

WindChaser
Posting Yak Master

225 Posts

Posted - 2005-03-03 : 15:25:48

That looks absolutely beautiful. I'll try it out. Thanks Andy !!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources