Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kshitizgp
Starting Member
31 Posts |
 Posted - 2012-02-17 : 02:31:35
|
| i have a query which is giving me dynamic data using pivotIn that am using total as both columns and rows like thisname ..type...PART... .hOR..... total xyz ......a........RT.........4..........4pqr.......b.........RT........6..........6TOTAL........................10........10NOW I WANT add one more column based on this footer totalmy formula is 10(which is total in footer)/4 how can i get access to footer total am using 2 pivots using union all ..if i use the sum it sayz divide by zero !;with cte as (select EM.Employee_Name as Name , BN.Block_Name as Block_Name, ......... ) SELECT Name ,BlockType, Block_Name ,[1],[2],cast((([1]+[2])*(16))/(8) as decimal(4,0)) As Proportion,,cast((((([1]+[2])*(16))/(8))*100)/(sum( (([1]+[2])*(16))/(8))) as float) as cs1- change here i want from CTE PIVOT (MAX(HOURS) FOR Date_Issued IN ( [1],[2] ) ) AS pvt GROUP BY BlockType, Block_Name, Name,[1],[2]UNION ALL SELECT Name , BlockType, Block_Name,[1],[2],cast((([1]+[2])*(16))/(8) as decimal(4,0)) As Proportion,,cast(((([1]+[2])*(16))/(8))/( sum( (([1]+[2])*(16))/(8))) as float) as cs1 -- its wrking only in footerFROM (select Name , 'Total' as BlockType, space(1) as Block_Name, Hours, Date_Issued FROM CTE) P PIVOT (SUM(hours) FOR Date_Issued IN ( [1],[2] ) ) as pvt GROUP BY BlockType, Block_Name, Name,[1],[2] |
|
|
|
|
|
|
|
access to footer total