123aaa Posted August 15, 2019 Share Posted August 15, 2019 Hi I am trying to create a table that looks like this: Status | FY20 | FY21 | FY22 | FY23 | FY24 | Active | 1000 | 2000 | 3000 | 0 | 0 Inactive | 500 | 100 | 0 | 1000 | 200 Pending | 600 | 2000 | 10 | 6000 | 0 Where the table is grouped by Status (i.e. either Active, Inactive or Pending) and the FY values are sums. This is what I've written so far: ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SELECT TOP (1000) CASE WHEN big_status = 913800000 THEN 'Active' WHEN big_status = 913800001 THEN 'Inactive' WHEN big_status >= 913800002 THEN 'Pending' WHEN big_status <= 913800006 THEN 'Pending' ELSE 'Closed' END AS Pipeline ,[big_fy20revenue_base] AS FY20 ,[big_fy21revenue_base] AS FY21 ,[big_fy22revenue_base] AS FY22 ,[big_fy23revenue_base] AS FY23 ,[big_fy24revenue_base] AS FY24 FROM [MSCRM].[dbo].[Filtered] WHERE (big_unitname='Projects' or big_unitname='Products' or big_unitname='Strategy') AND big_revenue = 'External Revenue' AND statecode = 'Open' SELECT Pipeline, SUM(FY20), SUM(FY21), SUM(FY22), SUM(FY23), SUM(FY24)FROM [MSCRM].[dbo].[Filtered] GROUP BY Pipeline ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- However, when I do the code in italics two things happen: 1. It underlines Pipeline, FY20, FY21, FY 22, FY23, FY24 and says they are invalid columns 2. The following error message: Msg 207, Level 16, State 1, Line 26 Invalid column name 'Pipeline'. Msg 207, Level 16, State 1, Line 24 Invalid column name 'Pipeline'. Msg 207, Level 16, State 1, Line 24 Invalid column name 'FY20'. Msg 207, Level 16, State 1, Line 24 Invalid column name 'FY21'. Msg 207, Level 16, State 1, Line 24 Invalid column name 'FY22'. Msg 207, Level 16, State 1, Line 24 Invalid column name 'FY23'. Msg 207, Level 16, State 1, Line 24 Invalid column name 'FY24'. Please can someone help? I am very new to SQL and self teaching. Thank you. Link to comment Share on other sites More sharing options...
justsomeguy Posted August 19, 2019 Share Posted August 19, 2019 I would just combine that into one query, I'm not sure what [MSCRM].[dbo].[Filtered] is supposed to be, but apparently it's not that. SELECT Pipeline, SUM(FY20), SUM(FY21), SUM(FY22), SUM(FY23), SUM(FY24) FROM (SELECT TOP (1000) CASE WHEN big_status = 913800000 THEN 'Active' WHEN big_status = 913800001 THEN 'Inactive' WHEN big_status >= 913800002 THEN 'Pending' WHEN big_status <= 913800006 THEN 'Pending' ELSE 'Closed' END AS Pipeline ,[big_fy20revenue_base] AS FY20 ,[big_fy21revenue_base] AS FY21 ,[big_fy22revenue_base] AS FY22 ,[big_fy23revenue_base] AS FY23 ,[big_fy24revenue_base] AS FY24 FROM [MSCRM].[dbo].[Filtered] WHERE (big_unitname='Projects' or big_unitname='Products' or big_unitname='Strategy') AND big_revenue = 'External Revenue' AND statecode = 'Open') AS tmp GROUP BY Pipeline Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now