Best method to add two additional calculated rows based on a Group By, incorporated into an unpivot piece of T-SQL?
-
Hi,
Pasted at the very bottom of this post is the T-SQL I wrote to unpivot, and format, some data. I require the data in this structure for Power BI.
The returned dataset is below too.What I require is two things:
1) Based on the returned dataset. I require a new row for each Division and Category that has the new value of 'SSGP' for its Grouping_Level that contains the calculation of (Sales - Costs). For example the new rows should look like:
DIVISION______GROUPING_LEVEL_______CATEGORY_______VALUE
Central________SSGP___________________DEValue__________(whatever the Grouping_Level.Sales - Costs is for Grouping_Level.DEValue for Central)
Central________SSGP___________________DETarget_________(whatever the Grouping_Level.Sales - Grouping_Level.Costs is for DETarget for Central)
Central________SSGP___________________MTDValue________(whatever the Grouping_Level.Sales - Grouping_Level.Costs is for MTDValue for Central)
Central________SSGP___________________MTDTarget________(whatever the Grouping_Level.Sales - Grouping_Level.Costs is for MTDTarget for Central)
This needs to be worked out for every Division and the four Category values.2) The second thing I require is, for each Division and Category I required the Grouping_Level to have the value of 'Gross Profit' and for the Category values of SSGP, FCP, CoS Adj, Customer Rebate, Settlement, Supplier Rebate summed. For example,
DIVISION______GROUPING_LEVEL_______CATEGORY________VALUE
DAT__________Gross Profit______________DEValue___________(SSGP + FCP + CoS Adj + Customer Rebate + Settlement + Supplier Rebate)
DAT__________Gross Profit______________DETarget__________(SSGP + FCP + CoS Adj + Customer Rebate + Settlement + Supplier Rebate)
DAT__________Gross Profit______________MTDValue_________(SSGP + FCP + CoS Adj + Customer Rebate + Settlement + Supplier Rebate)
DAT__________Gross Profit______________MTDTarget_________(SSGP + FCP + CoS Adj + Customer Rebate + Settlement + Supplier Rebate)
This needs to be worked out for every Division and the four Category values.Please can someone help me incorporate the required into the T-SQL I have written/pasted below?
Any help will be much appreciated. Thanks in advance.
SELECT [Division], [Grouping_Level] , Category , Value
FROM
(
SELECT
CASE
WHEN Division = 'South West & South' THEN 'South West'
WHEN Division = 'Design & Technology' THEN 'DAT'
WHEN Division = 'Flex-R' THEN 'FlexR'
ELSE REPLACE( Division, 'S ', '' )
END AS Division
, Grouping_Level
, ROUND( CONVERT( DECIMAL( 36,2 ), MTDValue ), 2 ) AS MTDValue
, ROUND( CONVERT( DECIMAL( 36,2 ), MTDTarget ), 2 ) AS MTDTarget
, ROUND( CONVERT( DECIMAL( 36,2 ), DEValue ), 2 ) AS DEValue
, ROUND( CONVERT( DECIMAL( 36,2 ), DETarget ), 2 ) AS DETarget
FROM [MISReporting].[dbo].[tbl_DailyE2E_FC] ) p
UNPIVOT
(
Value FOR Category IN ( MTDValue, MTDTarget, DEValue, DETarget )
) AS unpiv
WHERE Division NOT IN ( 'Central Admin', 'Central Admin SVS', 'eCommerce', 'Historical' )
UNION
SELECT Division, Grouping_Level , Category , Value
FROM
(
SELECT 'Central' AS Division
, Grouping_Level
, SUM( ROUND( CONVERT( DECIMAL( 36,2 ), MTDValue ), 2 ) ) AS MTDValue
, SUM( ROUND( CONVERT( DECIMAL( 36,2 ), MTDTarget ), 2 ) ) AS MTDTarget
, SUM( ROUND( CONVERT( DECIMAL( 36,2 ), DEValue ), 2 ) ) AS DEValue
, SUM( ROUND( CONVERT( DECIMAL( 36,2 ), DETarget ), 2 ) ) AS DETarget
FROM [MISReporting].[dbo].[tbl_DailyE2E_FC]
WHERE Division IN (
'Central Admin'
, 'Central Admin SVS'
, 'eCommerce'
, 'Historical'
)
GROUP BY Grouping_Level ) p
UNPIVOT
(
Value FOR Category IN ( MTDValue, MTDTarget, DEValue, DETarget
)
) AS unpiv
-
Bump - can anyone help please?
-
You haven't gotten a response, because you haven't supplied readily consumable sample data and expected results. (A picture is not consumable.) If you follow the instructions in the first link in my signature, people would be more willing to help.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply