Combining two sql query

by WonderWoman   Last Updated October 09, 2019 17:26 PM

I am trying to combine two sql queries. but getting error

I have tried using sab query but getting an error. Query 1:

  select
    year(ts.ReceptionDate) as Year,
    count(*) as NewProjects,
    count(case when ts.status = 'Inactive' then 1 
               when ts.status = 'Cancelled' then 1 end
        ) as InactiveCancelled,

 year(ReceptionDate) = 2010 and id = id ) then 1 end  ) as Value

 from rpt.transmissionservicesrpt ts
Where ts.Type = 'Retail / Utility POI' and  ts.ReceptionDate > '12-31- 
2011'
group by year(ts.receptiondate)
order by year(ts.receptiondate);

Query 2 :

Select  
        year(tsr.CompletedProject) as Year,
    count(case when tsr.Status = 'Completed' then 1
              end ) as CompletedProject


 from
 [rpt].[TSRRetailUtilityHistoricalWorkloadView] tsr
 where tsr.Type = 'Retail / Utility POI' 
 group by year(tsr.CompletedProject)
 order by year(tsr.CompletedProject);

   select
    year(ts.ReceptionDate) as Year,
    count(*) as NewProjects,
    count(case when ts.status = 'Inactive' then 1 
               when ts.status = 'Cancelled' then 1 end
        ) as InactiveCancelled,

    (select count (case when t.Status = 'Completed' then 1 end ) 
               as CompletedProject
    from [rpt].[TSRRetailUtilityHistoricalWorkloadView] t 
    group by year(t.CompletedProject)
     )


  year(ReceptionDate) = 2010 and id = id ) then 1 end  ) as Value

 from rpt.transmissionservicesrpt ts
 left join [rpt].[TSRRetailUtilityHistoricalWorkloadView] Tr on ts.id= 
 tr.id
 Where ts.Type = 'Retail / Utility POI' and  ts.ReceptionDate > '12-31- 
2011'
 group by year(ts.receptiondate)
order by year(ts.receptiondate);



Projects New Projects "Completed" Cancelled & Inactive
2013      32             46 5   11  30  
2014      45             75 17  14  44  
2015      46             90 24  20  46  
2016      30             76 26  10  40  
2017      82             122    12  26  84  
2018      84             168    30  32  106 
2019      36             142    22  5   115
Tags : sql sql-server


Related Questions


Updated November 20, 2017 05:26 AM

Updated February 17, 2016 02:11 AM

Updated December 06, 2017 20:26 PM

Updated August 24, 2017 04:26 AM

Updated March 08, 2018 18:26 PM