Solved

Using COALESCE in SQL Server 2000 DTS

Posted on 2010-11-30
6
363 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:printmedia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 7

Expert Comment

by:Cboudroz
ID: 34241321
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

0
 
LVL 7

Expert Comment

by:Cboudroz
ID: 34241329
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

0
 

Author Comment

by:printmedia
ID: 34241343
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.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 34241608
SELECT
    COAELSCE(CostPeriod1, aMinus1.CostPeriod12, aMinus1.CostPeriod11, ..., aMinus1.CostPeriod1,
        aMinus2.CostPeriod12, aMinus2.CostPeriod11, ...
       aMinus5.CostPeriod12, ..., aMinus5.CostPeriod1, b.DefaultValue) AS CostPeriod1,
    ...
FROM TableA a
INNER JOIN TableB b ON --default cost if never a match
    a.ItemNumber = b.ItemNumber
LEFT OUTER JOIN TableA aMinus1 ON
    aMinus1.ItemNumber = a.ItemNumber AND aMinus1.Year = a.Year - 1
INNER JOIN TableA aMinus2 ON
    aMinus2.ItemNumber = a.ItemNumber AND aMinus2.Year = a.Year - 2
INNER JOIN TableA aMinus3 ON
    aMinus3.ItemNumber = a.ItemNumber AND aMinus3.Year = a.Year - 3
INNER JOIN TableA aMinus4 ON
    aMinus4.ItemNumber = a.ItemNumber AND aMinus4.Year = a.Year - 4
INNER JOIN TableA aMinus5 ON
    aMinus5.ItemNumber = a.ItemNumber AND aMinus5.Year = a.Year - 5
0
 
LVL 7

Assisted Solution

by:Cboudroz
Cboudroz earned 250 total points
ID: 34241939
If you want to be able to go more then 5 years before used this:


In my example I only used 4 month
WITH Period (itemNumber, CostPeriod1, CostPeriod2, CostPeriod3, CostPeriod4, [Year], Level)
AS
(
-- Anchor member definition
    SELECT 
		itemNumber
		, P.CostPeriod1 AS CostPeriod1
		, COALESCE(P.CostPeriod2, P.CostPeriod1) AS CostPeriod2
		, COALESCE(P.CostPeriod3, P.CostPeriod2, P.CostPeriod1) AS CostPeriod3
		, COALESCE(P.CostPeriod4, P.CostPeriod3, P.CostPeriod2, P.CostPeriod1) AS CostPeriod4
		, [Year] 
		, 0 AS Level
		/* */
	FROM 
		TableA P
	WHERE 
		[Year] = '2010'

    UNION ALL

-- Recursive member definition
    SELECT 
		A.itemNumber
		, COALESCE(P.CostPeriod1,  A.CostPeriod4, A.CostPeriod3, A.CostPeriod2) AS CostPeriod1
		, COALESCE(P.CostPeriod2, P.CostPeriod1,  A.CostPeriod4, A.CostPeriod3) AS CostPeriod2
		, COALESCE(P.CostPeriod3, P.CostPeriod2, P.CostPeriod1,  A.CostPeriod4) AS CostPeriod3
		, COALESCE(P.CostPeriod4, P.CostPeriod3, P.CostPeriod2, P.CostPeriod1) AS CostPeriod4
		, P.[Year] 
		, Level + 1
	FROM 
		TableA A
		INNER JOIN Period AS P
			ON A.itemNumber = P.itemNumber
			   AND A.[Year] = P.[Year] - 1
	WHERE 
		P.CostPeriod1 IS NULL 
		OR P.CostPeriod2 IS NULL 
		OR P.CostPeriod3 IS NULL 
		OR P.CostPeriod4 IS NULL 

)
SELECT TOP 1
	*
FROM 
	Period
ORDER BY 
	Level DESC

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 34243118
Lol, still an even split.  Good luck on future qs :-)
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question