Hi,
I have one table with relevant fields
Report
ReportId Value RaisedDate status
1 12 12-09-2022 R
2 34 14-09-2022 Y
3 34 16-09-2022 B
this was expected o/p
Section Label status Flag Count
Unactioned Today records R NULL 0
Unactioned Today records Y NULL 0
;with cte as (
select 1 order,'Red' status,1 Flag
union
select 1 order,'Yellow' status,1 Flag
union
select 1 order,'Black' status,0 Flag
)
select 'Unactioned' Section,'Today records' Label,c.status
,case when A.status='R' Then 'Red'
when A.status='Y' Then 'Yellow'
when A.status='B' Then 'Black' End Flag,
count(ReportId) count
from cte as c
left outer join Report A
on c.status=(
case when A.status='R' Then 'Red'stat
when A.status='Y' Then 'Yellow'
when A.status='B' Then 'Black' End)
and cast(RaisedDate as date)=cast(getdate() as date)
where (Flag=1 and A.ActionId is null)
and (Value <> '' or Value !=null)
group by c.status,A.status
As of now after executing above query getting 0 rows as per as i need to o/p like above (mentioned above)
Can you please help me with updated query?
