Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.
SELECT EntityName, EntityCode
, SUM( CASE SalesMonth WHEN 'January' THEN 1 ELSE 0 END) AS January
, SUM( CASE SalesMonth WHEN 'February' THEN 1 ELSE 0 END) AS February
, SUM( CASE SalesMonth WHEN 'March' THEN 1 ELSE 0 END) AS March
FROM SalesTable
WHERE SalesMonth IN ('January', 'February', 'March')
GROUP BY EntityName, EntityCode
ORDER BY EntityName, EntityCode
--CREATE A TEMP TABLE
CREATE TABLE #SalesTable (EntityName Varchar(20)
,EntityCode Varchar(5)
,SalesMonth Varchar(10)
,[Tour Ref] VARCHAR(5)
,[Sales Unit] VARCHAR(2)
,[GBP Net Margin] MONEY
)
--POPULATE THE TEMP TABLE
INSERT INTO #SalesTable (EntityName, EntityCode, SalesMonth, [Tour Ref],[Sales Unit],[GBP Net Margin])
VALUES ('SalesItemType1','ITM1','January','ABC','ZY',30.25),
('SalesItemType1','ITM1','January','ABC','ZY',19.56),
('SalesItemType1','ITM1','January','EFG','ZY',25.32),
('SalesItemType1','ITM1','February','EFG','XC',25.25),
('SalesItemType1','ITM1','March','ABC','ZY',22.50),
('SalesItemType1','ITM1','February','ABC','XC',22.30),
('SalesItemType73','ITM73','February','ABC','XC',22.30),
('SalesItemType73','ITM73','February','ABC','XC',25.78),
('SalesItemType73','ITM73','January','EFG','ZY',33.33),
('SalesItemType73','ITM73','January','ABC','XC',42.50),
('SalesItemType73','ITM73','January','ABC','XC',19.80),
('SalesItemType73','ITM73','March','ABC','XC',19.80),
('SalesItemType73','ITM73','March','EFG','ZY',28.60)
SELECT *
FROM #SalesTable
--PIVOT
SELECT *
FROM #SalesTable AS s
PIVOT (
COUNT(SalesMonth) FOR SalesMonth
IN (January,February,March)) AS pvt
--PIVOT WITH SUM PER MONTH
SELECT EntityName, EntityCode, [Tour Ref],[Sales Unit], SUM([January]) AS JanuarySalesCount, SUM([February]) AS FebruarySalesCount, SUM([March]) AS MarchSalesCount
FROM
(SELECT EntityName, EntityCode, [Tour Ref],[Sales Unit],[January], [February], [March]
FROM #SalesTable AS s
PIVOT (
COUNT(SalesMonth) FOR SalesMonth
IN (January,February,March)) AS pvt) a
GROUP BY EntityName, EntityCode, [Tour Ref],[Sales Unit]
--PIVOT WITH AVG MARGIN
SELECT EntityName, EntityCode, [Tour Ref],[Sales Unit], SUM([January]) AS JanuarySalesCount, SUM([February]) AS FebruarySalesCount, SUM([March]) AS MarchSalesCount,AVG([GBP Net Margin]) AS AverageMarginALLMONTHS
FROM
(SELECT EntityName, EntityCode, [Tour Ref],[Sales Unit],[January], [February], [March],[GBP Net Margin]
FROM #SalesTable AS s
PIVOT (
COUNT(SalesMonth) FOR SalesMonth
IN (January,February,March)) AS pvt) a
GROUP BY EntityName, EntityCode, [Tour Ref],[Sales Unit]
--CREATE A TEMP TABLE
CREATE TABLE #SalesTable (EntityName Varchar(20)
,EntityCode Varchar(5)
,SalesMonth Varchar(10)
,[Tour Ref] VARCHAR(5)
,[Sales Unit] VARCHAR(2)
,[GBP Net Margin] MONEY
,[SalesYear] VARCHAR(4)
)
--POPULATE THE TEMP TABLE
INSERT INTO #SalesTable (EntityName, EntityCode, SalesMonth, [Tour Ref],[Sales Unit],[GBP Net Margin], SalesYear)
VALUES ('SalesItemType1','ITM1','January','ABC','ZY',30.25,2013),
('SalesItemType1','ITM1','January','ABC','ZY',19.56,2012),
('SalesItemType1','ITM1','January','EFG','ZY',25.32,2012),
('SalesItemType1','ITM1','February','EFG','XC',25.25,2013),
('SalesItemType1','ITM1','March','ABC','ZY',22.50,2013),
('SalesItemType1','ITM1','February','ABC','XC',22.30,2013),
('SalesItemType73','ITM73','February','ABC','XC',22.30,2012),
('SalesItemType73','ITM73','February','ABC','XC',25.78,2012),
('SalesItemType73','ITM73','January','EFG','ZY',33.33,2013),
('SalesItemType73','ITM73','January','ABC','XC',42.50,2013),
('SalesItemType73','ITM73','January','ABC','XC',19.80,2012),
('SalesItemType73','ITM73','March','ABC','XC',19.80,2012),
('SalesItemType73','ITM73','March','EFG','ZY',28.60,2012)
SELECT *
FROM #SalesTable
ORDER BY EntityName,SalesYear
--PIVOT
SELECT *
FROM #SalesTable AS s
PIVOT (
COUNT(SalesMonth) FOR SalesMonth
IN (January,February,March)) AS pvt
--PIVOT WITH SUM PER MONTH
SELECT EntityName, EntityCode, [Tour Ref],[Sales Unit], SalesYear, SUM([January]) AS JanuarySalesCount, SUM([February]) AS FebruarySalesCount, SUM([March]) AS MarchSalesCount
FROM
(SELECT EntityName, EntityCode, [Tour Ref],[Sales Unit], SalesYear, [January], [February], [March]
FROM #SalesTable AS s
PIVOT (
COUNT(SalesMonth) FOR SalesMonth
IN (January,February,March)) AS pvt) a
GROUP BY EntityName, EntityCode, SalesYear,[Tour Ref],[Sales Unit]
ORDER BY EntityName,SalesYear
--PIVOT WITH AVG MARGIN
SELECT EntityName, EntityCode, [Tour Ref],[Sales Unit], SalesYear, SUM([January]) AS JanuarySalesCount, SUM([February]) AS FebruarySalesCount, SUM([March]) AS MarchSalesCount,AVG([GBP Net Margin]) AS AverageMarginALLMONTHS
FROM
(SELECT EntityName, EntityCode, [Tour Ref],[Sales Unit],SalesYear,[January], [February], [March],[GBP Net Margin]
FROM #SalesTable AS s
PIVOT (
COUNT(SalesMonth) FOR SalesMonth
IN (January,February,March)) AS pvt) a
GROUP BY EntityName, EntityCode, [Tour Ref],[Sales Unit],SalesYear
ORDER BY EntityName,SalesYear
--Onnlight method
SELECT EntityName, EntityCode, [Tour Ref],[Sales Unit], SalesYear, AVG([GBP Net Margin]) AS AverageMarginALLMONTHS
, SUM( CASE SalesMonth WHEN 'January' THEN 1 ELSE 0 END) AS January
, SUM( CASE SalesMonth WHEN 'February' THEN 1 ELSE 0 END) AS February
, SUM( CASE SalesMonth WHEN 'March' THEN 1 ELSE 0 END) AS March
FROM #SalesTable
WHERE SalesMonth IN ('January', 'February', 'March')
GROUP BY EntityName, EntityCode, [Tour Ref],[Sales Unit], SalesYear
ORDER BY EntityName, SalesYear
Open in new window