CODEWITHSUNDEEP
sqlsql-serverpivot
user3115933
user3115933

Reputation: 4443

SQL Query to express output from a Pivot Query as a percentage

I have the following SQL Query running in SQL Server 2014 and it works fine. Now I need to modify the query so that the output is expressed as a percentage (more details on this below).

SELECT *
FROM   (SELECT [PropertyCode],
           [MTH],
           [ROOM NIGHTS]
    FROM   HOLDINGS) a
   PIVOT (Sum([ROOM NIGHTS])
         FOR [MTH] IN ([NOVEMBER 2014],
                       [DECEMBER 2014],
                       [JANUARY 2015])) AS PVTTABLE

HOLDINGS in the query above is a View Table.

The output from the above query looks like this:

PropertyCode   NOVEMBER 2014    DECEMBER 2014  JANUARY 2015
HOTEL A          1,200              560            700
HOTEL B          900                430            500
HOTEL C          800                650            200

HOTEL A has 65 rooms, Hotel B 120 rooms and Hotel C has 50 rooms and these info are located in a table called CAPACITY with columns "PropertyCode" and "Rooms" (listing the number of rooms as mentioned above).

I need to modify my pivot query so that the output looks like below:

PropertyCode   NOVEMBER 2014    DECEMBER 2014  JANUARY 2015
HOTEL A          61%               28%             35%
HOTEL B          25%               11%             13%
HOTEL C          53%               42%             13%

The percentages are calculated like this: ROOM NIGHTS / (No of Rooms of specific hotel * Days in Month) * 100

I think I need a LEFT JOIN to match the PropertyCode from the HOLDINGS view Table and the CAPACITY table and I will also need to modify the "PIVOT (Sum([ROOM NIGHTS])" part in my original query so that it calculates the output as a % but I'm stuck about how to write the syntax.

Upvotes: 1

Views: 254

Answers (1)

Jayvee
Jayvee

Reputation: 10875

something on these lines should work:

SELECT  [PropertyCode],[MTH], 
[NOVEMBER 2014]/(SELECT rooms FROM capacity c WHERE c.propertycode=pvttable.propertycode)*30*100. as nov2014, 
[DECEMBER 2014]/(SELECT rooms FROM capacity c WHERE c.propertycode=pvttable.propertycode)*31*100. as dec2014, 
[JANUARY 2015]/(SELECT rooms FROM capacity c WHERE c.propertycode=pvttable.propertycode)*31*100.  as jan2015
FROM   (SELECT [PropertyCode],
           [MTH],
           [ROOM NIGHTS]
    FROM   HOLDINGS) a
   PIVOT (Sum([ROOM NIGHTS])
         FOR [MTH] IN ([NOVEMBER 2014],
                       [DECEMBER 2014],
                       [JANUARY 2015])) AS PVTTABLE

Or using JOIN instead of correlated queries:

SELECT  [PropertyCode],[MTH], 
[NOVEMBER 2014]/rooms * 30 * 100. AS Nov2014, 
[DECEMBER 2014]/rooms * 31 * 100. AS Dec2014, 
[JANUARY 2015]/rooms  * 31 * 100. AS Jan2015
FROM   (SELECT [PropertyCode],
           [MTH],
           [ROOM NIGHTS]
    FROM   HOLDINGS) a
   PIVOT (Sum([ROOM NIGHTS])
         FOR [MTH] IN ([NOVEMBER 2014],
                       [DECEMBER 2014],
                       [JANUARY 2015])) AS PVTTABLE
JOIN capacity c ON c.propertycode=pvttable.propertycode

Upvotes: 1

Related Questions