This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and expected output to find those missing years where no any products have been purchased. Check from the year 2000.
Input Data:
|
1 2 3 4 5 6 7 8 |
ProductID ProductName PurcheseDate ----------- ----------- ------------ 1 ABC 2004-01-01 2 XYZ 2009-01-01 3 PQR 2012-01-01 4 EDF 2013-01-01 5 TYU 2016-01-01 6 ASD 2017-01-01 |
Expected Output:
|
1 2 3 4 5 6 |
MissingYears -------------- 2000-2003 2005-2008 2010-2011 2014-2015 |
Create a table with data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE TABLE tbl_Products ( ProductID int PRIMARY KEY ,ProductName VARCHAR(10) ,PurcheseDate DATE ) GO INSERT INTO tbl_Products VALUES (1,'ABC','2004-01-01') ,(2,'XYZ','2009-01-01') ,(3,'PQR','2012-01-01') ,(4,'EDF','2013-01-01') ,(5,'TYU','2016-01-01') ,(6,'ASD','2017-01-01') GO |
Solution:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT CONCAT(MIN(PurcheseDate),'-' ,MAX(PurcheseDate)) MissingYears FROM ( SELECT PurcheseDate, PurcheseDate - ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rnk FROM ( SELECT DISTINCT Number PurcheseDate FROM MASTER..SPT_VALUES WHERE Number >= 2000 and Number <= YEAR(GETDATE()) EXCEPT SELECT DISTINCT YEAR(PurcheseDate) PurcheseDate FROM tbl_products )a )t GROUP BY Rnk |
Please try the different solution for this puzzle and share it via comment...
