Tags

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


T-SQL Query | [ The All Products Puzzle ] – In this puzzle we have to two table i.e. SpecialProductMaster table – this table contains master information of the products and Sales Table- This table is used for capture product sales. Here we to find out customers who have purchase all the products mentioned in the  SpecialProductMaster  Table that appears in Sales table for a single customer. In the below example only customer 345 purchased Maggi and surf so we need 345 only as the output. Please check out the sample input and expected output for details.

Sample Input

SpecialProductMaster

ID ProductName
1 Maggi
2 Surf

Sales

SaleID ProductName CustID SaleDATE
101 Maggi 345 30-05-2014
201 Surf 345 30-05-2014
302 Rin 122 30-05-2014
303 TV 142 30-05-2014
109 Parle 345 30-05-2014
110 GoodDay 346 30-05-2014
112 Surf 346 30-05-2014

Expected Output

SaleID ProductName CustID SaleDATE
101 Maggi 345 30-05-2014
201 Surf 345 30-05-2014

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.

CREATE TABLE SpecialProductMaster
(
ID INT
,ProductName VARCHAR(10)
)
GO

INSERT INTO SpecialProductMaster(Id,ProductName)
VALUES
(1,'Maggi'),
(2,'Surf')

CREATE TABLE Sales
(
SaleID INT
,ProductName VARCHAR(10)
,CustID INT
,SaleDATE DATETIME DEFAULT GETDATE()
)
GO

INSERT INTO Sales(SaleID,ProductName,CustID)
VALUES
(112,'Surf',346)
(109,'Parle',345),
(101,'Maggi',345),
(201,'Surf',345),
(302,'Rin',122),
(303,'TV',142)

UPDATE – 20-Apr-2015 – Solution 1


--


--Solution 1

SELECT s.* FROM 
(
      SELECT Ar.CustID , Ar.ProductCount ,ProductName FROM
      (
            SELECT Ar.CustId , COUNT(*) OVER ( PARTITION BY Ar.CustID ORDER BY (SELECT NULL)) ProductCount , ProductName
            FROM ( SELECT m.ID,m.ProductName,s.CustID FROM Sales s INNER JOIN SpecialProductMaster m ON s.ProductName = m.ProductName ) Ar
      )
      Ar WHERE ProductCount =  ( SELECT COUNT(*) FROM SpecialProductMaster m )
) Br
INNER JOIN Sales s ON s.CustID = Br.CustID AND s.ProductName = Br.ProductName

--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com

Advertisements