asked on

Using COALESCE in SQL Server 2000 DTS

Hi all.

I have a table (Table A) with the following fields: ItemNumber, Year, CostPeriod1, CostPeriod2, CostPeriod3...CostPeriod12

Sample Data:
Item123--2007--5.50--3.5--NULL--2.5---etc until CostPeriod12
Item123--2008--10--NULL--NULL--44---etc until CostPeriod12
Item123--2009--NULL--33--NULL--44---etc until CostPeriod12

How can I use COALESCE so that if the period I'm looking at is null then go back and find me the last period with data (regardless of the year).

Below is a sample code that ScottPletcher from a previous post suggested. But as you can see it looks at the previous year, I want to be able to look at ALL the previous years not just last year.

How can I do this?

Thank you in advance!

SELECT
    COAELSCE(CostPeriod1, 
        (SELECT COALESCE(CostPeriod12, CostPeriod11, CostPeriod10, ..., CostPeriod1) 
         FROM TableA aPrevious
         WHERE aPrevious.ItemNumber = a.ItemNumber AND aPrevious.Year = a.Year - 1
        ) ) AS CostPeriod1,
    COALESCE(CostPeriod2, CostPeridod1, 
        (SELECT COALESCE(CostPeriod12, CostPeriod11, CostPeriod10, ..., CostPeriod1) 
         FROM TableA aPrevious
         WHERE aPrevious.ItemNumber = a.ItemNumber AND aPrevious.Year = a.Year - 1
        ) ) AS CostPeriod2,
    COALESCE(CostPeriod3, CostPeriod2, CostPeridod1, 
        (SELECT COALESCE(CostPeriod12, CostPeriod11, CostPeriod10, ..., CostPeriod1) 
         FROM TableA aPrevious
         WHERE aPrevious.ItemNumber = a.ItemNumber AND aPrevious.Year = a.Year - 1
        ) ) AS CostPeriod3,
    ...
FROM TableA a

Open in new window

Cboudroz

I think you are looking for this:

SELECT
	itemNumber
	, COALESCE(a.CostPeriod1, b.CostPeriod12, b.CostPeriod11, b.CostPeriod10 /*,...*/ ) as CostPeriod1 
	, COALESCE(a.CostPeriod2, a.CostPeriod1, b.CostPeriod12, b.CostPeriod11, b.CostPeriod10 /*,...*/ ) as CostPeriod2 
	, COALESCE(a.CostPeriod3, a.CostPeriod2, a.CostPeriod1, b.CostPeriod12, b.CostPeriod11, b.CostPeriod10 /*,...*/ ) as CostPeriod3
FROM 
	TableA a
	left join TableA b
		on a.itemNumber = b.itemNumber
			and a.Year <> b.Year - 1

Open in new window

Cboudroz

woups, error in ON clause, it's not <> but =

SELECT
	itemNumber
	, COALESCE(a.CostPeriod1, b.CostPeriod12, b.CostPeriod11, b.CostPeriod10 /*,...*/ ) as CostPeriod1 
	, COALESCE(a.CostPeriod2, a.CostPeriod1, b.CostPeriod12, b.CostPeriod11, b.CostPeriod10 /*,...*/ ) as CostPeriod2 
	, COALESCE(a.CostPeriod3, a.CostPeriod2, a.CostPeriod1, b.CostPeriod12, b.CostPeriod11, b.CostPeriod10 /*,...*/ ) as CostPeriod3
FROM 
	TableA a
	left join TableA b
		on a.itemNumber = b.itemNumber
			and a.Year = b.Year - 1

Open in new window

printmedia

ASKER

Thanks for the reply.

But I want to go as far back as there is data. Using a.Year = b.Year - 1 will only look at 2009 (if using this year as a reference), but what if I also want to look at 2008, 2007, 2006 as far back as I can go.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Cboudroz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

Lol, still an even split. Good luck on future qs :-)