Tags

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


SQL Puzzle | Week on Week sales % Puzzle

In this puzzle we have to get the week on week percent value of sales per area. We would like to have it calculate each financial week, so each week is 100% so add each area sales for each financial week and work out the percent for that day of the week.. Please check out the sample input and the expected output below-

Sample Input

Area Date SalesLocal SalesRemote DayName DayOfWeek FinancialWeek MonthName FinancialYear
1 2015-04-01 0 50 Wed 4 1 April 2015
2 2015-04-01 NULL NULL Wed 4 1 April 2015
3 2015-04-01 100 0 Wed 4 1 April 2015
3 2015-04-01 50 100 Wed 4 1 April 2015
3 2015-04-04 50 50 Sat 7 1 April 2015

Expected Output

Area Date FinancialWeek DayName MonthName SalesLocal% SalesRemote%
1 2015-04-01 1 Wed April 0% 25%
2 2015-04-01 1 Wed April 0% 0%
3 2015-04-01 1 Wed April 75% 50%
3 2015-04-04 1 Sat April 25% 25%

Rules/Restrictions

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

Script | use below script to create table & insert some sample data


--


CREATE TABLE WeekPercentagePuzzle
(
	  Area INT 
	, [Date] DATE
	, SalesLocal INT
	, SalesRemote INT
	, [DayName] VARCHAR(4)
	, [DayOfWeek] SMALLINT
	, FinancialWeek TINYINT
	, [MonthName] VARCHAR(15)
	, FinancialYear INT
)
GO

INSERT INTO WeekPercentagePuzzle VALUES
(1,    '2015-04-01',  0,                  50,                        'Wed',            4,                       1,                        'April',               2015),
(2,   '2015-04-01',   null,              null   ,                   'Wed',           4  ,                      1    ,                     'April',              2015), 
(3,  '2015-04-01',    100 ,             0      ,                    'Wed',           4   ,                      1  ,                      'April',             2015),
(3,  '2015-04-01',   50  ,               100 ,                        'Wed',            4,                        1,                        'April',               2015),
(3,  '2015-04-04',   50  ,              50  ,                          'Sat',             7,                         1,                        'April',              2015)
GO


--

Solution 1


--

SELECT a.Area ,
       a.Date ,
       a.FinancialWeek ,
	   a.DayName ,
	   a.MonthName ,
	   CONCAT(COALESCE(CAST(SUM(a.SalesLocal*1.) / SUM(SUM(a.SalesLocal*1.0)) OVER () * 100. AS INT),0),'%') [SalesLocal%],
       CONCAT(COALESCE(CAST(SUM(a.SalesRemote*1.) / SUM(SUM(a.SalesRemote*1.0)) OVER () * 100. AS INT),0),'%') [SalesRemote%]      
FROM dbo.WeekPercentagePuzzle a
GROUP BY a.Area ,
         a.Date ,
         a.DayName ,
         a.DayOfWeek ,
         a.FinancialWeek ,
         a.MonthName ,
         a.FinancialYear


--

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

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com