March 31, 2016 at 9:45 am
I created this query to return some data grouped by placement month. The result set is correct, but I was wondering is there a better way of doing this. I feel like there is too many sub queries in this statement. Is there an alternative way of doing this with less code.
Thanks in advance.
IF OBJECT_ID('tempdb..#Test') IS NOT NULL DROP TABLE #Test
CREATE TABLE #Test
(PlacementMonth VARCHAR(10),
TimeZoneNameLiq VARCHAR(50),
TimeZoneNamePlaced VARCHAR(50),
LiquidationPercent DECIMAL(10,9),
NumberOfActiveAccounts INT
)
INSERT INTO #Test
( PlacementMonth ,
TimeZoneNameLiq ,
TimeZoneNamePlaced ,
LiquidationPercent ,
NumberOfActiveAccounts
)
VALUES
('2-2016','Mountain Liq','Mountain Placed',0.25420357225119,382),
('1-2016','Mountain Liq','Mountain Placed',0.81183256592356,386),
('1-2016','Other Liq','Other Placed',0.14265108809629,401),
('2-2016','Eastern Liq','Eastern Placed',0.44148909410136,4862),
('1-2016','Eastern Liq','Eastern Placed',0.48148333136518,3889),
('2-2016','Central Liq','Central Placed',0.41321650476249,3587),
('1-2016','Central Liq','Central Placed',0.44545377413084,2836),
('1-2016','Pacific Liq','Pacific Placed',0.59245486005060,1246),
('2-2016','Pacific Liq','Pacific Placed',0.42840499067780,1532),
('2-2016','Other Liq','Other Placed',0.24454259173256,482)
SELECT PlacementMonth
,EasternPlaced = MAX([EasternPlaced])
,EasternLiq = MAX([EasternLiq])
,CentralPlaced = MAX([CentralPlaced])
,CentralLiq = MAX([CentralLiq])
,MountainPlaced = MAX([MountainPlaced])
,MountainLiq = MAX([MountainLiq])
,PacificPlaced = MAX([PacificPlaced])
,PacificLiq = MAX([PacificLiq])
,OtherPlaced = MAX([OtherPlaced])
,OtherLiq = MAX([OtherLiq])
FROM (
SELECT PlacementMonth
,EasternPlaced =[Eastern Placed]
,EasternLiq = [Eastern Liq]
,CentralPlaced =[Central Placed]
,CentralLiq = [Central Liq]
,MountainPlaced = [Mountain Placed]
,MountainLiq = [Mountain Liq]
,PacificPlaced = [Pacific Placed]
,PacificLiq = [Pacific Liq]
,OtherPlaced = [Other Placed]
,OtherLiq = [Other Liq]
FROM (
SELECT *
FROM #Test
) sub
PIVOT (
MAX(LiquidationPercent)
FOR TimeZoneNameLiq IN ([Other Liq], [Mountain Liq], [Central Liq], [Pacific Liq], [Eastern Liq] )
) AS PivotTbl
PIVOT (
MAX(NumberOfActiveAccounts)
FOR TimeZoneNamePlaced IN ([Other Placed], [Mountain Placed], [Central Placed], [Pacific Placed], [Eastern Placed])
) AS PivotTbl2
) x
GROUP BY PlacementMonth
March 31, 2016 at 10:15 am
The alternative is using cross tabs, which will make the query run faster.
SELECT PlacementMonth,
MAX(CASE WHEN TimeZoneNamePlaced = 'Eastern Placed' THEN NumberOfActiveAccounts END) AS [Eastern Placed],
MAX(CASE WHEN TimeZoneNamePlaced = 'Eastern Placed' THEN LiquidationPercent END) AS [Eastern Liq],
MAX(CASE WHEN TimeZoneNamePlaced = 'Central Placed' THEN NumberOfActiveAccounts END) AS [Central Placed],
MAX(CASE WHEN TimeZoneNamePlaced = 'Central Placed' THEN LiquidationPercent END) AS [Central Liq],
MAX(CASE WHEN TimeZoneNamePlaced = 'Mountain Placed' THEN NumberOfActiveAccounts END) AS [Mountain Placed],
MAX(CASE WHEN TimeZoneNamePlaced = 'Mountain Placed' THEN LiquidationPercent END) AS [Mountain Liq],
MAX(CASE WHEN TimeZoneNamePlaced = 'Pacific Placed' THEN NumberOfActiveAccounts END) AS [Pacific Placed],
MAX(CASE WHEN TimeZoneNamePlaced = 'Pacific Placed' THEN LiquidationPercent END) AS [Pacific Liq],
MAX(CASE WHEN TimeZoneNamePlaced = 'Other Placed' THEN NumberOfActiveAccounts END) AS [Other Placed],
MAX(CASE WHEN TimeZoneNamePlaced = 'Other Placed' THEN LiquidationPercent END) AS [Other Liq]
FROM #Test
GROUP BY PlacementMonth;
More information in here:
March 31, 2016 at 10:45 am
Thanks for the reply. The query does perform better and the alternative is more readable.
Thanks,
Sooch
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply