Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


Advanced SQL | Fill the Price for missing months

A very close friend given this to me. In this puzzle you have to fill the price of SKU & Color Id for missing months. Note that SKU & Color Id should be considered as a business unit. So you have to set the previous value available to the missing month. Please check out the sample input and the expected output. In this solution I have not considered any performance considerations. 🙂

Sample Input

SKU ColorId Price YM
1 1 10.0000 201801
1 1 12.0000 201804
2 1 80.0000 201704
3 1 28.0000 201704
3 1 20.0000 201804
3 1 19.0000 201806
3 1 27.0000 201808

Expected Output

SKU ColorId Price Ym
1 1 0.0000 201701
1 1 0.0000 201702
1 1 0.0000 201703
1 1 0.0000 201704
1 1 0.0000 201705
1 1 0.0000 201706
1 1 0.0000 201707
1 1 0.0000 201708
1 1 0.0000 201709
1 1 0.0000 201710
1 1 0.0000 201711
1 1 0.0000 201712
1 1 10.0000 201801
1 1 10.0000 201802
1 1 10.0000 201803
1 1 12.0000 201804
1 1 12.0000 201805
1 1 12.0000 201806
1 1 12.0000 201807
1 1 12.0000 201808
2 1 0.0000 201701
2 1 0.0000 201702
2 1 0.0000 201703
2 1 80.0000 201704
2 1 80.0000 201705
2 1 80.0000 201706
2 1 80.0000 201707
2 1 80.0000 201708
2 1 80.0000 201709
2 1 80.0000 201710
2 1 80.0000 201711
2 1 80.0000 201712
2 1 80.0000 201801
2 1 80.0000 201802
2 1 80.0000 201803
2 1 80.0000 201804
2 1 80.0000 201805
2 1 80.0000 201806
2 1 80.0000 201807
2 1 80.0000 201808
3 1 0.0000 201701
3 1 0.0000 201702
3 1 0.0000 201703
3 1 28.0000 201704
3 1 28.0000 201705
3 1 28.0000 201706
3 1 28.0000 201707
3 1 28.0000 201708
3 1 28.0000 201709
3 1 28.0000 201710
3 1 28.0000 201711
3 1 28.0000 201712
3 1 28.0000 201801
3 1 28.0000 201802
3 1 28.0000 201803
3 1 20.0000 201804
3 1 20.0000 201805
3 1 19.0000 201806
3 1 19.0000 201807
3 1 27.0000 201808

Script – DDL and INSERT Sample Data

--

CREATE TABLE SkuData
(
	 SKU BIGINT 
	,ColorId BIGINT  
	,Price DECIMAL(30,4)
	,YM INT	
)
GO

INSERT INTO SkuData VALUES 
(1,	1,10,201801),
(1,	1,12,201804),  /* Price changed in 2018 April */
(2, 1,80,201704),  /* Started in the 2017 April */
(3, 1,28,201704),  /* Price changed for this product 4 times*/
(3, 1,20,201804),
(3, 1,19,201806),
(3, 1,27,201808)
*/

SELECT * FROM SkuData
GO

--

–SOLUTION 1

--

DECLARE @StartYear AS INT = (SELECT MIN(LEFT(YM,4)) FROM SkuData)
;WITH CTE(Num) AS
(
    SELECT Number
    FROM (VALUES ('01'), ('02'), ('03'), ('04'), ('05'), ('06'), ('07'), ('08'),
    ('09'), ('10'),('11'),('12')) AS X(Number)
)
,SingleDigits(Number) AS
(
    SELECT Number
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
    (9), (0)) AS X(Number)
)
,Series AS
(
    SELECT (d1.Number+1) + (10*d2.Number) Number
    from
    SingleDigits as d1,
    SingleDigits as d2
)
,CTE2 AS 
(
	SELECT  sku, colorId, CONCAT(xx,Num) * 1 Num FROM
	(
		SELECT * , (@StartYear+(Number-1)) xx  FROM Series
		CROSS JOIN CTE
		CROSS JOIN (SELECT DISTINCT Sku,ColorId FROM SkuData)x
		WHERE (@StartYear+(Number-1)) <= YEAR(GETDATE())
	)k	
)
,CTE3 AS
(
	SELECT x.ColorId , x.SKU , b.Price , b.YM ,x.Num FROM CTE2 x
	LEFT JOIN SkuData b ON x.Num = b.YM  AND x.ColorId = b.ColorId and x.SKU = b.SKU	
	WHERE Num <= CONCAT(YEAR(GETDATE()),RIGHT(CONCAT('0',MONTH(GETDATE())),2))
)
,CTE4 AS
(
	SELECT z.ColorId , z.SKU , z.Price , Num 
	,ROW_NUMBER() OVER (PARTITION BY z.ColorId,z.SKU ORDER BY Num) rnk 
	FROM CTE3 z	
)
SELECT SKU,ColorId,ISNULL(Price,0) Price, Ym FROM 
(
	SELECT a.SKU, a.ColorId,ISNULL(a.Price,r.Price) Price, Num Ym
	FROM CTE4 a
	OUTER APPLY (
				 SELECT TOP 1 rnk Mins FROM CTE4 x WHERE x.ColorId = a.ColorId AND x.SKU = a.SKU
				 AND a.rnk > x.rnk AND x.Price IS NOT NULL
				 ORDER BY x.Num DESC
				)x
	OUTER APPLY
			   (
				 SELECT TOP 1 Price 
				 FROM CTE4 b			 
				 WHERE b.rnk = x.Mins
				 AND a.ColorId = b.ColorId AND a.SKU = b.SKU
			   )r
)z
ORDER BY Sku , ColorId,Ym

--

OUTPUT – 1

--

SKU                  ColorId              Price                                   Ym
-------------------- -------------------- --------------------------------------- -----------
1                    1                    0.0000                                  201701
1                    1                    0.0000                                  201702
1                    1                    0.0000                                  201703
1                    1                    0.0000                                  201704
1                    1                    0.0000                                  201705
1                    1                    0.0000                                  201706
1                    1                    0.0000                                  201707
1                    1                    0.0000                                  201708
1                    1                    0.0000                                  201709
1                    1                    0.0000                                  201710
1                    1                    0.0000                                  201711
1                    1                    0.0000                                  201712
1                    1                    10.0000                                 201801
1                    1                    10.0000                                 201802
1                    1                    10.0000                                 201803
1                    1                    12.0000                                 201804
1                    1                    12.0000                                 201805
1                    1                    12.0000                                 201806
1                    1                    12.0000                                 201807
1                    1                    12.0000                                 201808
2                    1                    0.0000                                  201701
2                    1                    0.0000                                  201702
2                    1                    0.0000                                  201703
2                    1                    80.0000                                 201704
2                    1                    80.0000                                 201705
2                    1                    80.0000                                 201706
2                    1                    80.0000                                 201707
2                    1                    80.0000                                 201708
2                    1                    80.0000                                 201709
2                    1                    80.0000                                 201710
2                    1                    80.0000                                 201711
2                    1                    80.0000                                 201712
2                    1                    80.0000                                 201801
2                    1                    80.0000                                 201802
2                    1                    80.0000                                 201803
2                    1                    80.0000                                 201804
2                    1                    80.0000                                 201805
2                    1                    80.0000                                 201806
2                    1                    80.0000                                 201807
2                    1                    80.0000                                 201808
3                    1                    0.0000                                  201701
3                    1                    0.0000                                  201702
3                    1                    0.0000                                  201703
3                    1                    28.0000                                 201704
3                    1                    28.0000                                 201705
3                    1                    28.0000                                 201706
3                    1                    28.0000                                 201707
3                    1                    28.0000                                 201708
3                    1                    28.0000                                 201709
3                    1                    28.0000                                 201710
3                    1                    28.0000                                 201711
3                    1                    28.0000                                 201712
3                    1                    28.0000                                 201801
3                    1                    28.0000                                 201802
3                    1                    28.0000                                 201803
3                    1                    20.0000                                 201804
3                    1                    20.0000                                 201805
3                    1                    19.0000                                 201806
3                    1                    19.0000                                 201807
3                    1                    27.0000                                 201808

(60 rows affected)



--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Performance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com

Advertisements