Advanced SQL | Fill the Price for missing months

13 Monday Aug 2018

Posted by Pawan Kumar Khowal in SQL Concepts, SQL Performance Tuning, SQL Puzzles, SQL SERVER, SQL Server Interview Questions, SQL SERVER Puzzles, T SQL Puzzles, Tricky SQL Queries

≈ 4 Comments

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

4 thoughts on “Advanced SQL | Fill the Price for missing months”

Lex van de Pol (@LexvandePol1) said:

May 25, 2020 at 6:23 PM

This is perhaps shorter (and faster):

SELECT
SKU,
ColorId,
ISNULL(
(SELECT TOP 1 Price
FROM SkuData
WHERE
SkuData.SKU = Products.SKU AND
SkuData.ColorId = Products.ColorId AND
SkuData.YM <= CAST(YearNr + MonthNr AS INTEGER)
ORDER BY SkuData.YM DESC), 0) AS Price,
YearNr + MonthNr AS Ym
FROM
(SELECT DISTINCT SKU, ColorId FROM SkuData) AS Products
CROSS JOIN (SELECT DISTINCT CAST(SUBSTRING(CAST(YM AS VARCHAR), 1, 4) AS VARCHAR) AS YearNr FROM SkuData) AS Years
CROSS JOIN (VALUES('01'),('02'),('03'),('04'),('05'),('06'),('07'),('08'),('09'),('10'),('11'),('12')) AS Months(MonthNr)
WHERE CAST(YearNr + MonthNr AS INTEGER) <= (SELECT MAX(Ym) FROM SkuData)
ORDER BY sku, ColorId, Ym

LikeLike

Reply
Avadh Kishore said:

August 30, 2020 at 9:28 PM

Another Solution

WITH
Time (TimeKey) AS
(
SELECT 201701 AS TimeKey
UNION ALL
SELECT CASE WHEN TimeKey= 201712 THEN 201800 ELSE TimeKey END+1 AS TimeKey
FROM Time
WHERE TimeKey= S.YM
AND M.Sku = S.Sku
AND M.ColorID = S.ColorID
)
SELECT SKU, ColorID, TimeKey, ISNULL(Price, 0) AS Price FROM C WHERE RankYM= 1

LikeLike

Reply
Muhammad Usman Shad said:

September 23, 2020 at 11:19 PM

declare @startdate as date
set @startdate = ’01 jan 2017′

declare @i as int
set @i = 1;

with
cteDates(Id, YMDate, YM) as
(
select 1, @startdate, Cast(Cast(YEAR(@startdate) as varchar) + SUBSTRING(convert(varchar, @startdate, 101) , 1, 2) as int)

union all

select Id + 1, DATEADD(M, 1, YMDate), Cast(Cast(YEAR(DATEADD(M, 1, YMDate)) as varchar) + SUBSTRING(convert(varchar, DATEADD(M, 1, YMDate), 101) , 1, 2) as int)

from cteDates
where DATEADD(M, 1, YMDate) <= '2018-08-01'
),

cteSKUandColors(SKU, ColorId)
as
(
select SKU, ColorId from SkuData
group by SKU, ColorId
)

select cS.SKU, cS.ColorId,

IsNull(Case When (S.Price is Null)
then
(
select top(1) iSD.Price from SkuData iSD where

— A ROW = top(1)
— only = will or will not return A ROW
— <= will definitely return A ROW

iSD.YM <=
Cast(Cast(YEAR(DateAdd(M, -1, cD.YMDate)) as varchar) +
SUBSTRING(convert(varchar, DateAdd(M, -1, cD.YMDate), 101) , 1, 2) as int)
and
iSD.SKU = cS.SKU
and
iSD.ColorId = cS.ColorId

— we have to fill the missing price with
— the previous month's value,
— therefore, we must have to use
— 'desc' in order by clause
order by iSD.YM desc
)
else
S.Price
end, 0)
as Price,

cD.YM, cD.YMDate

from cteDates cD

cross join cteSKUandColors cS
left join SkuData S on
— this condition is v.v. important
S.YM = cD.YM and S.SKU = cS.SKU and S.ColorId = cS.ColorId

— this order is v.v. important too
order by cS.SKU asc, cS.ColorId, cD.YM asc

LikeLiked by 1 person

Reply
Avadh Kishore said:

September 26, 2020 at 10:40 PM

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

WITH
Time (TimeKey) AS
(
SELECT 201701 AS TimeKey
UNION ALL
SELECT CASE WHEN TimeKey= 201712 THEN 201800 ELSE TimeKey END+1 AS TimeKey
FROM Time
WHERE CASE WHEN TimeKey= 201712 THEN 201800 ELSE TimeKey END+1<=201808
),
CSE AS
(
SELECT DISTINCT SKU, COlorID FROM #SkuData
),
CTE_Master
AS
(
SELECT
SKU, ColorID, TimeKey
FROM CSE
CROSS JOIN Time
), CTE_Base
as
(

SELECT
CM.SKU, CM.ColorID, TimeKey, Price, SUM(Price) OVER(PARTITION BY CM.SKU, CM.COlorID ORDER BY TimeKey) AS PriceNew
FROM CTE_master CM
LEFT JOIN #SkuData SD
ON CM.SKU = SD.SKU
AND CM.ColorId = SD.ColorId
AND CM.TimeKey = SD.YM
)
SELECT
CM.SKU, CM.ColorID, TimeKey, SUM(ISNULL(Price, 0)) OVER(PARTITION BY CM.SKU, CM.ColorID, CM.PriceNew ORDER BY TimeKey) AS NewPrice
FROM CTE_Base CM
ORDER BY CM.SKU, CM.ColorID, TimeKey

LikeLike

Reply

Improving my SQL BI Skills

Advanced SQL | Fill the Price for missing months

4 thoughts on “Advanced SQL | Fill the Price for missing months”

Leave a comment Cancel reply

Share this

Related

4 thoughts on “Advanced SQL | Fill the Price for missing months”

Leave a comment Cancel reply