Tags

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


T-SQL Query | [ The Deal Puzzle ]  – In this puzzle we have find for what time the deal was activated from the sample input table. Please check out the sample input and expected output for details.

Sample Input

deal_id price quantity total_quantity months
2 4 10000 310000 00:00.0
2 4 10000 280000 00:00.0
2 4.5 10000 310000 00:00.0
2 4 10000 310000 00:00.0
2 4 15000 280000 00:00.0
2 4 15000 310000 00:00.0

Expected Output

DealId Price Quantity TotalQuantity StartMonth EndMonth
2 4 10000 590000 00:00.0 00:00.0
2 4.5 10000 310000 00:00.0 00:00.0
2 4 10000 310000 00:00.0 00:00.0
2 4 15000 590000 00:00.0 00:00.0

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 deal_details
(
deal_id               INT,
contract_month        VARchar(7),
price                 FLOAT,
quantity              INT,
total_quantity        INT
)
GO

insert into deal_details values(2,'Jan2013',4,10000,310000);
insert into deal_details values(2,'Feb2013',4,10000,280000);
insert into deal_details values(2,'Mar2013',4.5,10000,310000);
insert into deal_details values(2,'Apr2013',4,10000,310000);
insert into deal_details values(2,'May2013',4,15000,280000);
insert into deal_details values(2,'Jun2013',4,15000,310000);

UPDATE – 20-Apr-2015 – Solution 1


--


--Solution 1

;WITH CTE AS 
(
       SELECT deal_id , price , quantity , total_quantity 
	   , cast(d.contract_month as datetime ) months FROM deal_details d
)
,
CTE2 AS
(
       SELECT * , ROW_NUMBER() OVER  (ORDER BY Months) rnk FROM CTE
)
,CTE3 AS
(
SELECT *, CASE WHEN
              deal_id = ( select deal_id from CTE2 c3 WHERE c3.rnk =   ( SELECT max(c1.rnk) FROM CTE2 c1 WHERE c1.rnk < c2.rnk )  ) AND 
              price = ( select price from CTE2 c3 WHERE c3.rnk =   ( SELECT max(c1.rnk) FROM CTE2 c1 WHERE c1.rnk < c2.rnk )  ) AND
              quantity = ( select quantity from CTE2 c3 WHERE c3.rnk =   ( SELECT max(c1.rnk) FROM CTE2 c1 WHERE c1.rnk < c2.rnk )  ) 
              THEN 0 ELSE 1 END cols FROM CTE2 c2
)
,CTE4 AS
( SELECT * , SUM(cols) OVER (ORDER BY rnk) grouper FROM CTE3 )
SELECT MIN(deal_id) DealId , MIN(price) Price , MIN(quantity) Quantity , SUM(total_quantity) TotalQuantity 
, MIN(months) StartMonth , MAX(months) EndMonth FROM CTE4
GROUP BY grouper



--

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

Keep Learning

http://MSBISkills.com