Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fill in Missing Data


Fill in Missing Data

Author
Message
AndrewSQLDBA
AndrewSQLDBA
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1008 Visits: 3427
Hello Everyone
I hope that you all had a wonderful weekend.

I am working with some data that is not so clean. I have some rows that are Very close to being a duplicate row. I need to take all the rows with the same SKU number and fill in the missing data for the Price, Cost or the SalePrice, what ever may be missing. The data is set like @DiamondsMissingData and I need the row to look like @DiamondsWithData table. I can remove the duplicate values once I get the missing data filled in. I am not sure how the best way to go about coding for this situation.



DECLARE @DiamondsMissingData TABLE
(
SKU varchar(20)
, VendorNumber varchar(20)
, VendorSKU varchar(20)
, GradingReportType varchar(20)
, GradingReportNumber bigint
,Price money
, Cost money
,SalePrice money
)

DECLARE @DiamondsWithData TABLE
(
SKU varchar(20)
, VendorNumber varchar(20)
, VendorSKU varchar(20)
, GradingReportType varchar(20)
, GradingReportNumber bigint
, Price money
, Cost money
, SalePrice money
)


INSERT INTO @DiamondsMissingData
SELECT '6157560941C','Crown','CR50135','GIA',6157560941,35588000,177940,NULL UNION ALL
SELECT '6157560941C','Crown','CR50135','GIA',6157560941,35588000,NULL,27375500 UNION ALL
SELECT '6157560941C','Crown','CR50135','GIA',6157560941,NULL,177940,27375500


INSERT INTO @DiamondsWithData
SELECT '6157560941C','Crown','CR50135','GIA',6157560941,35588000,177940,27375500

SELECT * FROM @DiamondsMissingData
SELECT * FROM @DiamondsWithData



Thank You in advance for your assistance, time, and code sample.

Andrew SQLDBA
Andrew Kernodle
Andrew Kernodle
SSChasing Mays
SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)

Group: General Forum Members
Points: 610 Visits: 8135
It seems like you're wanting to copy the non-NULL values for any given SKU into the NULL values, correct? If so, this should do it:


;WITH NullFill(SKU,FillPrice,FillCost,FillSalePrice) AS(
SELECT VendorSKU,MAX(Price)AS Price, MAX(Cost) AS Cost, MAX(SalePrice) AS SalePrice
FROM @DiamondsMissingData
GROUP BY VendorSKU
)
UPDATE @DiamondsMissingData
SET Price = CASE WHEN Price IS NULL THEN FillPrice ELSE Price END,
Cost = CASE WHEN Cost IS NULL THEN FillCost ELSE Cost END,
SalePrice = CASE WHEN SalePrice IS NULL THEN FillSalePrice ELSE SalePrice END
FROM @DiamondsMissingData A
INNER JOIN NullFill B
ON A.VendorSKU = B.SKU



That should take the non-NULL value for each of the three potentially-NULL columns, and fill it in where the value is NULL, ignoring it if it's not NULL.

However, this won't work if the same SKU can have multiple values for any of the three nullable columns; if there's a variation in price, for example, this will make all price values the same. In your provided data, this isn't a problem, but if it could happen in your real data, this will be problematic.

If that's the case, a better solution can certainly be worked out; please provide an example of a case where that might happen, and adjustments will be made! :-)

- :-D
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8530 Visits: 18130
This will deduplicate the rows that have missing values. However, if the duplicate rows are the majority, I might use a different approach.


INSERT INTO @DiamondsMissingData
SELECT '6157560941C','Crown','CR50135','GIA',6157560941,35588000,177940,NULL UNION ALL
SELECT '6157560941C','Crown','CR50135','GIA',6157560941,35588000,NULL,27375500 UNION ALL
SELECT '6157560941C','Crown','CR50135','GIA',6157560941,NULL,177940,27375500


INSERT INTO @DiamondsWithData
SELECT SKU
,VendorNumber
,VendorSKU
,GradingReportType
,GradingReportNumber
,MAX( Price)
,MAX( Cost)
,MAX( SalePrice)
FROM @DiamondsMissingData
WHERE Price IS NULL
OR Cost IS NULL
OR SalePrice IS NULL
GROUP BY SKU
,VendorNumber
,VendorSKU
,GradingReportType
,GradingReportNumber

DELETE FROM @DiamondsMissingData
WHERE Price IS NULL
OR Cost IS NULL
OR SalePrice IS NULL

INSERT INTO @DiamondsMissingData
SELECT *
FROM @DiamondsWithData




Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
AndrewSQLDBA
AndrewSQLDBA
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1008 Visits: 3427
Thank You to you both. That code worked very nicely. I had to change it just slightly, but you got me going down the correct path

Thank you again

Andrew SQLDBA
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search