The Weird and The Wonderful

SELECT DISTINCT
 
TDB_Child.id_childtdb, 
TDB_Child.id_tdb, 
TDB_Child_Horizontal.dim5, 
ISNULL  ((SELECT description FROM TDB_ChildDesc WHERE (id_childtdb = TDB_Child.id_childtdb) AND (id_lang = @Language) AND (id_tdb = @Template) AND (id_dim = 'PDC_SINT')),'[DESC - ' + CAST(TDB_Child.id_childtdb AS nvarchar) + ']') AS childtdb_Description, 
ISNULL((SELECT description FROM TDB_ChildDesc AS TDB_ChildDesc_3 WHERE (id_childtdb = TDB_Child_Horizontal.dim5) AND (id_lang = @Language) AND (id_tdb = @Template) AND (id_dim = 'COM_PROP')), '[DESC - ' + CAST(TDB_Child_Horizontal.dim5 AS nvarchar) + ']') AS childtdb_Description2,
TDB_Child.flag, 
TDB_Child.ordine,
(SELECT ISNULL(SUM(valore), 0) AS Expr1 FROM TDB_FlatTable AS Act WHERE (dim10 LIKE @Country + '%') AND (dim1 = @Year) AND (dim6 <> 'TotaleBu') AND (dim5 = TDB_Child_Horizontal.dim5) AND (dim3 = 'R4') AND (dim8 = TDB_FlatTable.dim8) AND (dim2 = @Period)) AS R4,
(SELECT ISNULL(SUM(valore), 0) AS Expr1 FROM TDB_FlatTable AS Act WHERE (dim10 LIKE @Country + '%') AND (dim1 = @Year) AND (dim6 <> 'TotaleBu') AND (dim5 = TDB_Child_Horizontal.dim5) AND (dim3 = '3YP2008-2010') AND (dim8 = TDB_FlatTable.dim8) AND (dim2 = @Period)) AS Budget,
(SELECT ISNULL(SUM(valore), 0) AS Expr1 FROM TDB_FlatTable AS Act WHERE (dim10 LIKE @Country + '%') AND (dim1 = @Year - 1) AND (dim6 <> 'TotaleBu') AND (dim5 = TDB_Child_Horizontal.dim5) AND (dim3 = 'Consuntivo') AND (dim8 = TDB_FlatTable.dim8) AND (dim2 = @Period)) AS Consuntivo,
TDB_Child_Horizontal.Ordine2, 
CAST(0 AS NUMERIC) AS Delta 

FROM  TDB_Child 
INNER JOIN (SELECT DISTINCT TDB_Child_5.id_childtdb, TDB_H.dim5, TDB_H.Ordine2 FROM TDB_Child AS TDB_Child_5 CROSS JOIN (SELECT     id_childtdb AS dim5, ordine AS Ordine2  FROM TDB_Child AS TDB_Child_4 WHERE (id_report = 'CE') AND (id_tdb = @Template) AND (id_dim = 'COM_PROP') AND (visibile = 1)) AS TDB_H WHERE  (TDB_Child_5.id_tdb = @Template) AND (TDB_Child_5.id_dim = 'PDC_SINT') AND (TDB_Child_5.id_report = 'CE') AND (TDB_Child_5.visibile = 1)) AS TDB_Child_Horizontal ON TDB_Child.id_childtdb = TDB_Child_Horizontal.id_childtdb 
LEFT OUTER JOIN TDB_FlatTable ON TDB_Child.id_childtdb = TDB_FlatTable.dim8

WHERE
(TDB_Child.id_tdb = @Template) AND 
(TDB_Child.id_dim = 'PDC_SINT') AND 
(TDB_Child.id_report = 'CE') AND 
(TDB_Child.id_childtdb NOT IN ('Taxes', 'NetIncome', '<SPACES6>', '<SPACES7>', 'ROS')) AND (TDB_Child.visibile = 1)
UNION
SELECT
id_childtdb, 
id_tdb, 
dim5, 
childtdb_Description, 
childtdb_Description2, 
flag, 
ordine, 
CASE R4_PV WHEN 0 THEN 0 ELSE R4_GM / R4_PV * 100 END AS R4, 
CASE Budget_PV WHEN 0 THEN 0 ELSE Budget_GM / Budget_PV * 100 END AS Budget, 
CASE Consuntivo_PV WHEN 0 THEN 0 ELSE Consuntivo_GM / Consuntivo_PV * 100 END AS Consuntivo, 
Ordine2, 
CAST(0 AS NUMERIC) AS Delta
FROM (SELECT DISTINCT TDB_Child_3.id_childtdb, TDB_Child_3.id_tdb, TDB_Child_Horizontal_1.dim5, ISNULL
                                                  ((SELECT     description
                                                      FROM         TDB_ChildDesc AS TDB_ChildDesc_2
                                                      WHERE     (id_childtdb = TDB_Child_3.id_childtdb) AND (id_lang = @Language) AND (id_tdb = @Template) AND (id_dim = 'PDC_SINT')), 
                                              '[DESC - ' + CAST(TDB_Child_3.id_childtdb AS nvarchar) + ']') AS childtdb_Description, ISNULL
                                                  ((SELECT     description
                                                      FROM         TDB_ChildDesc AS TDB_ChildDesc_1
                                                      WHERE     (id_childtdb = TDB_Child_Horizontal_1.dim5) AND (id_lang = @Language) AND (id_tdb = @Template) AND 
                                                                            (id_dim = 'COM_PROP')), '[DESC - ' + CAST(TDB_Child_Horizontal_1.dim5 AS nvarchar) + ']') AS childtdb_Description2, 
                                              TDB_Child_3.flag, TDB_Child_3.ordine,
                                                  (SELECT     ISNULL(SUM(valore), 0) AS Expr1
                                                    FROM          TDB_FlatTable AS Act
                                                    WHERE      (dim10 LIKE @Country + '%') AND (dim1 = @Year) AND (dim6 <> 'TotaleBu') AND (dim5 = TDB_Child_Horizontal_1.dim5) AND 
                                                                           (dim3 = 'R4') AND (dim8 = 'ProductionValue') AND (dim2 = @Period)) AS R4_PV,
                                                  (SELECT     ISNULL(SUM(valore), 0) AS Expr1
                                                    FROM          TDB_FlatTable AS Act
                                                    WHERE      (dim10 LIKE @Country + '%') AND (dim1 = @Year) AND (dim6 <> 'TotaleBu') AND (dim5 = TDB_Child_Horizontal_1.dim5) AND 
                                                                           (dim3 = '3YP2008-2010') AND (dim8 = 'ProductionValue') AND (dim2 = @Period)) AS Budget_PV,
                                                  (SELECT     ISNULL(SUM(valore), 0) AS Expr1
                                                    FROM          TDB_FlatTable AS Act
                                                    WHERE      (dim10 LIKE @Country + '%') AND (dim1 = @Year - 1) AND (dim6 <> 'TotaleBu') AND (dim5 = TDB_Child_Horizontal_1.dim5) 
                                                                           AND (dim3 = 'Consuntivo') AND (dim8 = 'ProductionValue') AND (dim2 = @Period)) AS Consuntivo_PV,
                                                  (SELECT     ISNULL(SUM(valore), 0) AS Expr1
                                                    FROM          TDB_FlatTable AS Act
                                                    WHERE      (dim10 LIKE @Country + '%') AND (dim1 = @Year) AND (dim6 <> 'TotaleBu') AND (dim5 = TDB_Child_Horizontal_1.dim5) AND 
                                                                           (dim3 = 'R4') AND (dim8 = 'GrossOperatingMargin') AND (dim2 = @Period)) AS R4_GM,
                                                  (SELECT     ISNULL(SUM(valore), 0) AS Expr1
                                                    FROM          TDB_FlatTable AS Act
                                                    WHERE      (dim10 LIKE @Country + '%') AND (dim1 = @Year) AND (dim6 <> 'TotaleBu') AND (dim5 = TDB_Child_Horizontal_1.dim5) AND 
                                                                           (dim3 = '3YP2008-2010') AND (dim8 = 'GrossOperatingMargin') AND (dim2 = @Period)) AS Budget_GM,
                                                  (SELECT     ISNULL(SUM(valore), 0) AS Expr1
                                                    FROM          TDB_FlatTable AS Act
                                                    WHERE      (dim10 LIKE @Country + '%') AND (dim1 = @Year - 1) AND (dim6 <> 'TotaleBu') AND (dim5 = TDB_Child_Horizontal_1.dim5) 
                                                                           AND (dim3 = 'Consuntivo') AND (dim8 = 'GrossOperatingMargin') AND (dim2 = @Period)) AS Consuntivo_GM, 
                                              TDB_Child_Horizontal_1.Ordine2, CAST(0 AS NUMERIC) AS Delta
                       FROM          TDB_Child AS TDB_Child_3 INNER JOIN
                                                  (SELECT DISTINCT TDB_Child_2.id_childtdb, TDB_H_1.dim5, TDB_H_1.Ordine2
                                                    FROM          TDB_Child AS TDB_Child_2 CROSS JOIN
                                                                               (SELECT     id_childtdb AS dim5, ordine AS Ordine2
                                                                                 FROM          TDB_Child AS TDB_Child_1
                                                                                 WHERE      (id_report = 'CE') AND (id_tdb = @Template) AND (id_dim = 'COM_PROP') AND (visibile = 1)) AS TDB_H_1
                                                    WHERE      (TDB_Child_2.id_tdb = @Template) AND (TDB_Child_2.id_dim = 'PDC_SINT') AND (TDB_Child_2.id_report = 'CE') AND 
                                                                           (TDB_Child_2.visibile = 1)) AS TDB_Child_Horizontal_1 ON 
                                              TDB_Child_3.id_childtdb = TDB_Child_Horizontal_1.id_childtdb LEFT OUTER JOIN
                                              TDB_FlatTable AS TDB_FlatTable_1 ON TDB_Child_3.id_childtdb = TDB_FlatTable_1.dim8
                       WHERE      (TDB_Child_3.id_tdb = @Template) AND (TDB_Child_3.id_dim = 'PDC_SINT') AND (TDB_Child_3.id_childtdb IN ('ROS')) AND 
                                              (TDB_Child_3.id_report = 'CE') AND (TDB_Child_3.visibile = 1)) AS ROS
ORDER BY TDB_Child.ordine, TDB_Child_Horizontal.Ordine2