Sql Query issue and error regarding groupby cause

I am trying to calculate the total number of Projects in every year. and also how many projects are active, how many of them are canceled.

I tried to group by cause for PRojects dates so we have a total number of project in every year but I am not sure where to start and what to do

Select  ts.Id as projectid ,
    --a.ParentObjectId,
    ts.RequestName as ProjectDates,
    ts.Type,
    ts.Category,
    ts.SubType,
    ts.status as projectstatus,
    Count (ts.ReceptionDate),
    cast (ts.ReceptionDate as  Date) as ReceptionDate,

from [rpt].[TransmissionServicesRpt] ts  
left join [dbo].[AuditHistory] a on a.ParentObjectId = ts.Id
Left join [dbo].[User] u on a.CreatedById = u.id
Group by ts.id, ts.ReceptionDate

​x
 
Select  ts.Id as projectid ,    --a.ParentObjectId,    ts.RequestName as ProjectDates,    ts.Type,    ts.Category,    ts.SubType,    ts.status as projectstatus,    Count (ts.ReceptionDate),    cast (ts.ReceptionDate as  Date) as ReceptionDate,​from [rpt].[TransmissionServicesRpt] ts  left join [dbo].[AuditHistory] a on a.ParentObjectId = ts.IdLeft join [dbo].[User] u on a.CreatedById = u.idGroup by ts.id, ts.ReceptionDate​

+ -------------+--------+-----------+------------+----------+-----------------+
| New Projects | Active | Cancelled | Terminated | Inactive | Carried Forward |
+ -------------+--------+-----------+------------+----------+-----------------+
| 2013         |     32 |       45  |          4 |       11 |              30 |
| 2014         |     45 |       75  |         17 |       14 |              44 |
| 2015         |     46 |       90  |         25 |       21 |              44 |
| 2016         |     30 |       74  |         27 |       10 |              37 |
| 2017         |     82 |      119  |         11 |       26 |              82 |
| 2018         |     86 |      168  |         29 |       24 |             115 |
| 2019         |     23 |      138  |          9 |        4 |             125 |
+ -------------+--------+-----------+------------+----------+-----------------+

Answer

You want one result row per year. So group by year. You get it via YEAR or DATEPART. Then count conditionally:

select
  year(receptiondate) as year,
  count(*) as total,
  count(case when status = 'Active' then 1 end) as active,
  count(case when status = 'Cancelled' then 1 end) as cancelled,
  count(case when status = 'Terminated' then 1 end) as terminated,
  count(case when status = 'Inactive' then 1 end) as inactive,
  count(case when status = 'Carried Forward' then 1 end) as carried_forward
from rpt.transmissionservicesrpt
group by year(receptiondate)
order by year(receptiondate);

 
select  year(receptiondate) as year,  count(*) as total,  count(case when status = 'Active' then 1 end) as active,  count(case when status = 'Cancelled' then 1 end) as cancelled,  count(case when status = 'Terminated' then 1 end) as terminated,  count(case when status = 'Inactive' then 1 end) as inactive,  count(case when status = 'Carried Forward' then 1 end) as carried_forwardfrom rpt.transmissionservicesrptgroup by year(receptiondate)order by year(receptiondate);​

Advertisement

Answer