September 12, 2018 at 9:12 am
Hi,
Is this possible? Take the below. The 'Everyone' status I want to be a total of all rows in the vwStaffDayActivity view, irrespective of the actual status.
select s.[Status], count(a.ACT_Status) [Count], s.[Rank]
from [(local)\ISYS].Intelligent.dbo.vwStaffStatus2 s left join
[(local)\ISYS].Intelligent.dbo.vwStaffDayActivity a on a.ACT_Status in
(
case
when s.[Status] = 'Everyone' then <anything>
else s.[Status]
end
)
group by s.[Status], s.[Rank]
order by [Rank];
Only thing I can think of is a sub-query finding all statuses in the vwStaffStatus2 view. Is this the only way?
select s.[Status], count(a.ACT_Status) [Count], s.[Rank]
from [(local)\ISYS].Intelligent.dbo.vwStaffStatus2 s left join
[(local)\ISYS].Intelligent.dbo.vwStaffDayActivity a on a.ACT_Status in
(
select [Status]
from [(local)\ISYS].Intelligent.dbo.vwStaffStatus2
where (s.[Status] = 'Everyone' and [Status] in
(
select [Status]
from [(local)\ISYS].Intelligent.dbo.vwStaffStatus2
)
) or [Status] = s.[Status]
)
group by s.[Status], s.[Rank]
order by [Rank];
Thanks
September 12, 2018 at 9:49 am
Assuming that ACT_Status and Status are not nullable, this should work.
select s.[Status], count(a.ACT_Status) [Count], s.[Rank]
from [(local)\ISYS].Intelligent.dbo.vwStaffStatus2 s left join
[(local)\ISYS].Intelligent.dbo.vwStaffDayActivity a on s.[Status] IN (a.ACT_Status, 'Everyone')
group by s.[Status], s.[Rank]
order by [Rank];
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 12, 2018 at 2:56 pm
Do you know what, I tried (almost) that. My mistake was having the columns the wrong way around (but seeing it has helped me see why):
a.ACT_Status IN (s.[Status], 'Everyone')
Thanks for helping me 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply