T-SQL Query

Question

Post reply

T-SQL Query

sumishri

Grasshopper

Points: 15
More actions
December 10, 2009 at 3:12 am

#385925

Hi,
My table structure is as below.I want to get rid of NULL values by a Non Null values available in that column before (ie,the value before the valid from of that particular row)
ProductId ValiFrom ValidTO ProductDescription CountryCode OpenDate
123 01/01/2008 29/11/2009 WWW GB 26/09/2002
123 30/11/2009 07/12/2009 NULL NULL 15/09/1998
123 08/12/2009 12/12/2009 NULL NULL NULL
123 13/12/2009 31/12/2009 YYY GB NULL
123 01/01/2010 09/12/2010 ZZZ GB NULL
123 10/12/2010 11/12/2010 NULL GB 28/11/2004
123 12/12/2010 13/12/2010 NULL GB NULL
123 14/12/2010 06/06/2079 NULL GB NULL
This is my required result set.
ProductId ValiFrom ValidTO ProductDescription CountryCode OpenDate
123 01/01/2008 29/11/2009 WWW GB 26/09/2002
123 30/11/2009 07/12/2009 WWW GB 15/09/1998
123 08/12/2009 12/12/2009 WWW GB 15/09/1998
123 13/12/2009 31/12/2009 YYY GB 15/09/1998
123 01/01/2010 09/12/2010 ZZZ GB 15/09/1998
123 10/12/2010 11/12/2010 ZZZ GB 28/11/2004
123 12/12/2010 13/12/2010 ZZZ GB 28/11/2004
123 14/12/2010 06/06/2079 ZZZ GB 28/11/2004
Any clue?
Warm Regards
Arun

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Hi Arun

Can you precisely define the rules which determine that this row:

123 30/11/2009 07/12/2009 NULL NULL 15/09/1998

should be updated to this:

123 30/11/2009 07/12/2009 WWW GB 15/09/1998

And so on for the other rows of data which contain NULLs?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 2

Something like this ? untested , also assumed that the can be independently null

select ProductId ,

ValiFrom,

ValidTO,

ProductDescription,

CountryCode,

OpenDate,

NewProductDescription.ProductDescription,

NewCountryCode.CountryCode

from yourtable

outer apply(select top 1 ProductDescription

from yourtable innertable

where innertable.ProductId = yourtable.ProductId

and ProductDescription is not null

order by validFrom desc) as NewProductDescription

outer apply(select top 1 CountryCode

from yourtable innertable

where innertable.ProductId = yourtable.ProductId

and CountryCode is not null

order by validFrom desc) as NewCountryCode

Clear Sky SQL
My Blog[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Run this:

SET DATEFORMAT DMY

DROP TABLE #Temp

CREATE TABLE #Temp (ProductId INT,

ValiFrom DATETIME, ValidTO DATETIME,

ProductDescription CHAR(3), CountryCode CHAR(2), OpenDate DATETIME)

INSERT INTO #Temp (ProductId, ValiFrom, ValidTO, ProductDescription, CountryCode, OpenDate)

SELECT 123, '01/01/2008', '29/11/2009', 'WWW', 'GB', '26/09/2002' UNION ALL

SELECT 123, '30/11/2009', '07/12/2009', NULL, NULL, '15/09/1998' UNION ALL

SELECT 123, '08/12/2009', '12/12/2009', NULL, NULL, NULL UNION ALL

SELECT 123, '13/12/2009', '31/12/2009', 'YYY', 'GB', NULL UNION ALL

SELECT 123, '01/01/2010', '09/12/2010', 'ZZZ', 'GB', NULL UNION ALL

SELECT 123, '10/12/2010', '11/12/2010', NULL, 'GB', '28/11/2004' UNION ALL

SELECT 123, '12/12/2010', '13/12/2010', NULL, 'GB', NULL UNION ALL

SELECT 123, '14/12/2010', '06/06/2079', NULL, 'GB', NULL

CREATE CLUSTERED INDEX IdRunningTotalOrder ON #Temp (ProductId, ValiFrom)

SELECT * FROM #Temp

DECLARE @ProductDescription CHAR(3), @CountryCode CHAR(2), @OpenDate DATETIME

UPDATE #Temp SET

@ProductDescription = ProductDescription = ISNULL(ProductDescription, @ProductDescription),

@CountryCode = CountryCode = ISNULL(CountryCode, @CountryCode),

@OpenDate = OpenDate = ISNULL(OpenDate, @OpenDate)

SELECT * FROM #Temp

And read this to see how it works:

http://www.sqlservercentral.com/articles/T-SQL/68467/

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sumi.mcp Grasshopper Points: 14 More actions · Answer 4

Hi Chris,

Thank you for your help with the code.However I am getting the Collation Conflict error

Msg 549, Level 16, State 1, Line 21

The collation 'SQL_Latin1_General_CP1_CI_AS' of receiving variable is not equal to the collation 'Latin1_General_CI_AS' of column 'CountryCode'.

With Regards

Arun

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Interesting - from BOL "Character literals and variables are assigned the default collation of the current database". So the default collation of your db is different to that of the column [CountryCode]. You can explicitly change it:

UPDATE #Temp SET

@ProductDescription = ProductDescription = ISNULL(ProductDescription, @ProductDescription),

@CountryCode = CountryCode = ISNULL(CountryCode, @CountryCode) COLLATE SQL_Latin1_General_CP1_CI_AS,

@OpenDate = OpenDate = ISNULL(OpenDate, @OpenDate)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 6

Chris Morris-439714 (12/10/2009)
Interesting - from BOL "Character literals and variables are assigned the default collation of the current database". So the default collation of your db is different to that of the column [CountryCode]. You can explicitly change it:
UPDATE #Temp SET
@ProductDescription = ProductDescription = ISNULL(ProductDescription, @ProductDescription),
@CountryCode = CountryCode = ISNULL(CountryCode, @CountryCode) COLLATE SQL_Latin1_General_CP1_CI_AS,
@OpenDate = OpenDate = ISNULL(OpenDate, @OpenDate)

I think this means that the collation of the current database is different to that of the tempdb.

Clear Sky SQL
My Blog[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Dave Ballantyne (12/10/2009)
I think this means that the collation of the current database is different to that of the tempdb.

Depends if the code failed on the sample using the #table, or on his production tables - Arun didn't specify which in his post. I'd wager a beer on your assumption being correct, though.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sumi.mcp Grasshopper Points: 14 More actions · Answer 8

Hi Chris,

Thank you! the code worked!.

Now I have an issue,I have only giveen part of my table sturcture with first few columns.My table consists of about 22 coulmns and might change in future too.

It am afraid it going to be a trouble if I Declare variable for each and every column.

Regards

Sumi