>Try
>
>
>DECLARE @YEAR int
>DECLARE @PrevMonth int
>
>SET @Year = YEAR(getdate())
>SET @PrevMonth = MONTH(DATEADD(mm, -1, getdate()))
>
>select site, impactedMedia, incidentCategory,
> SUM( CASE WHEN Year(EventDate) = @Year
> AND month(EventDate) = @PrevMonth
> THEN 1 ELSE 0 END) AS MTD,
> SUM( CASE WHEN Year(EventDate) = @Year
> AND month(EventDate) <= @PrevMonth
> THEN 1 ELSE 0 END) AS YTD
> from tb_EnvIncident
> left join
> tb_sc_data_codes
> on impactedMedia = code
> and codetype = 'iris_EnvImpactedMedia'
> AND Status = 'Approved'
> group by site, impactedMedia, incidentCategory
>
>
Thanks.
