none
CTE para reswtar dos filas RRS feed

  • Pregunta

  • Hi,

    Estoy queriendo hacer un CTE para calcular la resta de dos filas. Estas dos filas estan calculadas mediante :

    Select Level0_Account  , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010, 
    sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
    FROM Aer_Lingus_data_Income
    Where  Level0_Account ='Revenue'
    Group by Level0_Account


    y la otra fila  mediante:

    UNION ALL
    Select Name_Account   , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010, 
    sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
    FROM Aer_Lingus_data_Income
    Where  Name_Account ='Employee profit share' and Level0_Account ='Operating expenses'
    Group by Name_Account


    el CTE la tengo en codigo de la siguiente forma:

    WITH sumasRevenue
    AS (
        SELECT  Name_Account, ID 
            , sum(Year_2006) AS Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010, 
    sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
        FROM Aer_Lingus_data_Income
        WHERE  Level0_Account = 'Revenue'
        GROUP BY Name_Account, ID
        WITH ROLLUP
        )
        , total
    AS (
        SELECT Y2006,Y2007 ,Y2008,Y2009 ,Y2010 ,Y2011 ,Y2012 ,Y2013 
        FROM sumasRevenue
        WHERE Name_Account IS NULL
        ), 
    sumasOperatingExpensive
    AS (
        SELECT  Name_Account, ID 
            , sum(Year_2006) AS Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010, 
    sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
        FROM Aer_Lingus_data_Income
        WHERE  Level0_Account = 'Operating expenses'
        GROUP BY Name_Account, ID
        WITH ROLLUP
        )
        , total1
    AS (
        SELECT Y2006,Y2007 ,Y2008,Y2009 ,Y2010 ,Y2011 ,Y2012 ,Y2013 
        FROM sumasOperatingExpensive
        WHERE Name_Account IS NULL
        )
    	
    	SELECT distinct  s.Name_Account,
        (s.Y2006* 1.0 - t.Y2006) AS [V2006] ,(s.Y2007* 1.0 -T.Y2007 ) as [V2007]  , (s.Y2008* 1.0 -T.Y2008 ) as [V2008],(s.Y2009* 1.0 -T.Y2009 ) as [V2009], 
    	(s.Y2010* 1.0 -T.Y2010 ) as [V2010],(s.Y2011* 1.0 -T.Y2011 ) as [V2011],(s.Y2012* 1.0 -T.Y2012 ) as [V2012],(s.Y2013* 1.0 -T.Y2013 ) as [V2013]
    FROM sumasRevenue AS s , sumasOperatingExpensive AS t
    
    
    UNION ALL
    
    SELECT distinct COALESCE(s.Name_Account,'NON CURRENT ASSETS') AS   Name_Account  , 
    sum (s.Y2006* 1.0 - t.Y2006) as  V2006,sum (s.Y2007* 1.0 - t.Y2007) as  V2007,sum (s.Y2008* 1.0- t.Y2008) as  V2008,sum (s.Y2009* 1.0- t.Y2009) as  V2009,sum (s.Y2010* 1.0- t.Y2010) as  V2010,
    sum (s.Y2011* 1.0 - t.Y2011) as  V2011,sum (s.Y2012* 1.0- t.Y2012) as  V2012,sum (s.Y2013* 1.0- t.Y2013) as  V2013 
    FROM sumasRevenue as s , sumasOperatingExpensive as t
    Where  s.Name_Account IS NOt NULL 
    GROUP BY s.Name_Account
        WITH ROLLUP

    Pero estoy hacienda algo mal por que no funciona.

    Agradeceria vuestra ayuda o consejo.

    Gracias

    jueves, 12 de febrero de 2015 8:58

Respuestas

Todas las respuestas

  • Y que es lo que no funciona? te da algun error o simplemente no trae los datos que esperas?

    Sería bueno que agregues el script de creacion de tablas y datos de ejemplo en forma de INSERT para que podamos simular tu caso, ademas del resultado esperado en base a dichos datos.

    jueves, 12 de febrero de 2015 14:15
  • Hola Ronald,

    Gracias por responder te dejo todo el scrip para tu analisis:

    USE Modelling   
    GO
    --TABLE Aer_Lingus_Income_Statement
    
    IF OBJECT_ID('Aer_Lingus_Income_Statement') IS NOT NULL
      DROP TABLE Aer_Lingus_Income_Statement
    GO
    CREATE TABLE Aer_Lingus_Income_Statement
    (
    	ID [nvarchar](255) NOT NULL,
    	Name_Account [nvarchar](255) NULL,
    	Company [nvarchar](255) NULL,
    	Level0_Account [nvarchar](255) NULL,
    	Level1_Account [nvarchar](255) NULL,
    	Level2_Account [nvarchar](255) NULL,
    	Level3_Account [nvarchar](255) NULL,
    	Level4_Account [nvarchar](255) NULL,
    	Level5_Account [nvarchar](255) NULL,
    	Level6_Account [nvarchar](255) NULL,
    	Level7_Account [nvarchar](255) NULL,
    	Level8_Account [nvarchar](255) NULL,
    	Year_2006 decimal (15,2)  null,
    	Year_2007 decimal (15,2)  null,
    	Year_2008 decimal (15,2)  null,
    	Year_2009 decimal (15,2)  null,
    	Year_2010 decimal (15,2)  null,
    	Year_2011 decimal (15,2)  null,
    	Year_2012 decimal (15,2)  null,
    	Year_2013 decimal (15,2)  null,
    	)
    GO
    
    ALTER TABLE Aer_Lingus_Income_Statement
      ADD CONSTRAINT PK_Aer_Lingus_Income_Statement PRIMARY KEY (ID)
    GO
    
    INSERT INTO Aer_Lingus_Income_Statement 
    SELECT *
    FROM  Aer_Lingus_data_Income
    
    
    
    IF OBJECT_ID('Aer_Lingus_Income_Statement_Historic') IS NOT NULL
      DROP VIEW Aer_Lingus_Income_Statement_Historic
      GO
    CREATE VIEW Aer_Lingus_Income_Statement_Historic
    as
    
    
    Select Level0_Account  , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010, 
    sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
    FROM Aer_Lingus_data_Income
    Where  Level0_Account ='Revenue'
    Group by Level0_Account
    UNION ALL
    Select Name_Account  , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010, 
    sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
    FROM Aer_Lingus_data_Income
    Where Name_Account= 'Passenger revenue' and  Level0_Account ='Revenue'
    Group by Name_Account
    UNION ALL
    Select Name_Account  , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010, 
    sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
    FROM Aer_Lingus_data_Income
    Where Name_Account= 'Ancillary revenue' and  Level0_Account ='Revenue'
    Group by Name_Account
    UNION ALL
    Select Name_Account  , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010, 
    sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
    FROM Aer_Lingus_data_Income
    Where Name_Account= 'Other revenue' and  Level0_Account ='Revenue'
    Group by Name_Account
    UNION ALL
    Select Name_Account  , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010, 
    sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
    FROM Aer_Lingus_data_Income
    Where Name_Account= 'Cargo revenue' and  Level0_Account ='Revenue'
    Group by Name_Account
    UNION ALL
    Select Level0_Account  , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010, 
    sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
    FROM Aer_Lingus_data_Income
    Where  Level0_Account ='Operating expenses'
    Group by Level0_Account
    UNION ALL
    Select Level1_Account  , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010, 
    sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
    FROM Aer_Lingus_data_Income
    Where  Level1_Account ='Staff costs' and Level0_Account ='Operating expenses'
    Group by Level1_Account
    UNION ALL
    Select Level1_Account  , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010, 
    sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
    FROM Aer_Lingus_data_Income
    Where  Level1_Account ='Depreciation, amortisation and impairment' and Level0_Account ='Operating expenses'
    Group by Level1_Account
    UNION ALL
    Select Name_Account  , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010, 
    sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
    FROM Aer_Lingus_data_Income
    Where  Name_Account ='Aircraft operating lease costs' and Level0_Account ='Operating expenses'
    Group by Name_Account
    UNION ALL
    Select Name_Account  , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010, 
    sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
    FROM Aer_Lingus_data_Income
    Where  Name_Account ='Fuel and oil costs' and Level0_Account ='Operating expenses'
    Group by Name_Account
    UNION ALL
    Select Name_Account  , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010, 
    sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
    FROM Aer_Lingus_data_Income
    Where  Name_Account ='Maintenance expenses' and Level0_Account ='Operating expenses'
    Group by Name_Account
    UNION ALL
    Select Name_Account  , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010, 
    sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
    FROM Aer_Lingus_data_Income
    Where  Name_Account ='Airport charges' and Level0_Account ='Operating expenses'
    Group by Name_Account
    UNION ALL
    Select Name_Account  , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010, 
    sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
    FROM Aer_Lingus_data_Income
    Where  Name_Account ='En-route charges' and Level0_Account ='Operating expenses'
    Group by Name_Account
    UNION ALL
    Select Name_Account  , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010, 
    sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
    FROM Aer_Lingus_data_Income
    Where  Name_Account ='Distribution costs' and Level0_Account ='Operating expenses'
    Group by Name_Account
    UNION ALL
    Select Name_Account  , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010, 
    sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
    FROM Aer_Lingus_data_Income
    Where  Name_Account ='Ground operations, catering and other operating costs' and Level0_Account ='Operating expenses'
    Group by Name_Account
    UNION ALL
    Select Level1_Account   , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010, 
    sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
    FROM Aer_Lingus_data_Income
    Where  Level1_Account ='Other (gains)/losses - net' and Level0_Account ='Operating expenses'
    Group by Level1_Account
    UNION ALL
    Select Name_Account   , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010, 
    sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
    FROM Aer_Lingus_data_Income
    Where  Name_Account ='Employee profit share' and Level0_Account ='Operating expenses'
    Group by Name_Account
    GO
    
    WITH sumasRevenue
    AS (
        SELECT  Name_Account, ID 
            , sum(Year_2006) AS Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010, 
    sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
        FROM Aer_Lingus_data_Income
        WHERE  Level0_Account = 'Revenue'
        GROUP BY Name_Account, ID
        WITH ROLLUP
        )
        , total
    AS (
        SELECT Y2006,Y2007 ,Y2008,Y2009 ,Y2010 ,Y2011 ,Y2012 ,Y2013 
        FROM sumasRevenue
        WHERE Name_Account IS NULL
        ), 
    sumasOperatingExpensive
    AS (
        SELECT  Name_Account, ID 
            , sum(Year_2006) AS Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010, 
    sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
        FROM Aer_Lingus_data_Income
        WHERE  Level0_Account = 'Operating expenses'
        GROUP BY Name_Account, ID
        WITH ROLLUP
        )
        , total1
    AS (
        SELECT Y2006,Y2007 ,Y2008,Y2009 ,Y2010 ,Y2011 ,Y2012 ,Y2013 
        FROM sumasOperatingExpensive
        WHERE Name_Account IS NULL
        )
    	
    	SELECT distinct ID , s.Name_Account,
        (s.Y2006* 1.0 - t.Y2006) AS [V2006] ,(s.Y2007* 1.0 -T.Y2007 ) as [V2007]  , (s.Y2008* 1.0 /T.Y2008 ) as [V2008],(s.Y2009* 1.0 /T.Y2009 ) as [V2009], 
    	(s.Y2010* 1.0 /T.Y2010 ) as [V2010],(s.Y2011* 1.0 /T.Y2011 ) as [V2011],(s.Y2012* 1.0 /T.Y2012 ) as [V2012],(s.Y2013* 1.0 /T.Y2013 ) as [V2013]
    FROM sumasRevenue AS s , sumasOperatingExpensive AS t
    Where ID IS NOT NULL
    
    UNION ALL
    
    SELECT distinct ID,COALESCE(s.Name_Account,'NON CURRENT ASSETS') AS   Name_Account  , 
    sum (s.Y2006* 1.0 / t.Y2006) as  V2006,sum (s.Y2007* 1.0 / t.Y2007) as  V2007,sum (s.Y2008* 1.0/ t.Y2008) as  V2008,sum (s.Y2009* 1.0/ t.Y2009) as  V2009,sum (s.Y2010* 1.0/ t.Y2010) as  V2010,
    sum (s.Y2011* 1.0 / t.Y2011) as  V2011,sum (s.Y2012* 1.0/ t.Y2012) as  V2012,sum (s.Y2013* 1.0/ t.Y2013) as  V2013 
    FROM sumasRevenue as s , sumasOperatingExpensive as t
    Where ID IS NULL and Name_Account IS NULL 
    GROUP BY Name_Account, ID
        WITH ROLLUP
    
    
    
    
    
    select *
    from  Aer_Lingus_Income_Statement_Historic 

     Lo que intento es restar la fila de datos REVENUE y La fila de Datos Operating Expenses.

    Error:

    (37 row(s) affected)
    Msg 209, Level 16, State 1, Line 190
    Ambiguous column name 'ID'.
    Msg 209, Level 16, State 1, Line 186
    Ambiguous column name 'ID'.


    • Editado Frank0401 jueves, 12 de febrero de 2015 15:28
    jueves, 12 de febrero de 2015 15:27
  • Lo que te aparece es un error, que en uno de tus selects, tienes 2 o mas tablas que tienen la columna ID y no SQL Server no sabe a cual quieres invocar, revisa la linea 190 y 186 de tu script, agregale el nombre de tabla o alias para identificar de forma independiente cada una de ellas..
    jueves, 12 de febrero de 2015 17:59
  • Hi Ronand,

    I hope that you are well. Respecto a lo que me dices los datos son extraidos de la misma tabla, en este caso Aer_Lingus_data_Income, por eso creo que me da ese error.

    Hay otra forma de resolverlo, Gracias

    Un saludo

    jueves, 12 de febrero de 2015 18:08
  • lo que te falla son los group by y los select al poner ID y tener dos tablas casi siempre "s"y "t" tendras que poner en los select s.ID y en el group by igual s.ID


    saludos.

    viernes, 13 de febrero de 2015 12:27