Substituting NULL for a particular value

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

General SQL Server Forums

New to SQL Server Programming

Substituting NULL for a particular value

Author

Topic

thenearfuture
Starting Member

35 Posts

Posted - 2007-02-02 : 14:56:36

Hello,

I've been working on a query and have reached a dead end, so I'm hoping a more experienced person might be able to shine a light.

I have the following table:

CaseID CaseStartDate PaymentID PaymentDate AmountPaid
1 01-2004 11 05-2004 100
1 01-2004 12 10-2004 200
1 01-2004 13 01-2005 300
2 01-2005 21 02-2005 100
2 01-2005 22 01-2006 200
2 01-2005 23 02-2006 300
3 01-2006 31 04-2006 100
3 01-2006 32 09-2006 200
3 01-2006 33 12-2006 300
4 01-2007 41 01-2007 100
4 01-2007 42 01-2007 200
4 01-2007 43 02-2007 300

The table shows four individual cases, each of which has 3 payments made at different times. I need to do a query showing the sum of all the payments made during three different time periods for each case: payments made up to 5 months after the case started, up 10 months after, and up to 15 months after.

I know how to do that part of the task, but I'm having trouble with the next part.

For two of the cases, all the timelines have not yet run their course. Case 3, for example, started in January 2006. I can easily sum up the payments made up to 5 months later ($100 paid in April 2006) and payments made up to 10 months later ($300 = the previous $100 + the $200 from September 2006), but I don't want to sum up all the payments made up to 15 months later, since it's only February 2007 (and therefore less than 15 months later).

Instead, I would like the value for "Payments made up to 15 months later" to be NULL, since the 15-month period is not over yet. The same goes for Case 4, except that all the values should be NULL since the case is so new.

Here's the result I'm looking for:

CaseID CaseStartDate AmountPaid5 AmountPaid10 AmountPaid15
1 01-2004 100 300 500
2 01-2005 100 100 500
3 01-2006 100 300 NULL
4 01-2007 NULL NULL NULL

Any ideas/suggestions?

Thank you for your help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-02 : 17:37:22

The 500 values should really be 600 right?

-- prepare sample data
declare	@sample table (caseid int, casestartdate varchar(7), paymentid int, paymentdate varchar(7), amountpaid int)

insert	@sample
select	1, '01-2004', 11, '05-2004', 100 union all
select	1, '01-2004', 12, '10-2004', 200 union all 
select	1, '01-2004', 13, '01-2005', 300 union all
select	2, '01-2005', 21, '02-2005', 100 union all
select	2, '01-2005', 22, '01-2006', 200 union all
select	2, '01-2005', 23, '02-2006', 300 union all
select	3, '01-2006', 31, '04-2006', 100 union all
select	3, '01-2006', 32, '09-2006', 200 union all
select	3, '01-2006', 33, '12-2006', 300 union all
select	4, '01-2007', 41, '01-2007', 100 union all
select	4, '01-2007', 42, '01-2007', 200 union all
select	4, '01-2007', 43, '02-2007', 300

-- Show the result
SELECT	d.CaseID,
		d.CaseStartDate,
		SUM(CASE
				WHEN d.CSD + 5 > d.Today THEN NULL
				WHEN d.PD - d.CSD <= 5 THEN d.AmountPaid
		END) AS AmountPaid5,
		SUM(CASE
				WHEN d.CSD + 10 > d.Today THEN NULL
				WHEN d.PD - d.CSD <= 10 THEN d.AmountPaid
		END) AS AmountPaid10,
		SUM(CASE
				WHEN d.CSD + 15 > d.Today THEN NULL
				WHEN d.PD - d.CSD <= 15 THEN d.AmountPaid
		END) AS AmountPaid15
FROM		(
			SELECT	CaseID,
					CaseStartDate,
					CAST(LEFT(CaseStartDate, 2) AS INT) + 12 * CAST(RIGHT(CaseStartDate, 4) AS INT) AS CSD,
					CAST(LEFT(PaymentDate, 2) AS INT) + 12 * CAST(RIGHT(PaymentDate, 4) AS INT) AS PD,
					AmountPaid,
					12 * DATEPART(year, GETDATE()) + DATEPART(month, GETDATE()) AS Today
			FROM		@Sample
		) AS d
GROUP BY	d.CaseID,
		d.CaseStartDate

Peter Larsson
Helsingborg, Sweden

thenearfuture
Starting Member

35 Posts

Posted - 2007-02-04 : 11:23:45

Yep, the 500 values should really be 600 - my mistake.

Once again, thank you for taking the time to do this. Your code, as usual, worked masterfully.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources