The data returned is presented in a web app. I want to return a lit of departments that are bound to a .NET repeater control, which itself has a nested repeater control for people in each group. The pairing is done based on the DEP_Code value (this exists for each user). The first SELECT is a list of favourite people for each person.
That means the presentation in the web app is grouped by Department. I want the ability to turn of grouping, so it would be just a list of people. I still want Favourites to be there whether grouping is on or off.
So:
select'FAV' DEP_Code, 'Favourites' DEP_Name, cast(1 as bit) Favourite, 1 [Rank],
count(a.EMP_ClockNumber) Total,
count(case when a.ACT_Status = 'In' then a.EMP_ClockNumber end) [In],
count(case when a.ACT_Status = 'Out' then a.EMP_ClockNumber end) [Out],
count(case when a.ACT_Status = 'Break' then a.EMP_ClockNumber end) [Break],
count(case when a.ACT_Status = 'Absent' then a.EMP_ClockNumber end) [Absent]
fromtbl_Users u join
tbl_User_Favourites uf on uf.Person_ID = u.[User_ID] and uf.[User_ID] = @user-id left join
[(local)\ISYS].Intelligent.dbo.Employees e on e.EMP_Number collate database_default = u.User_Number left join
[(local)\ISYS].Intelligent.dbo.vwStaffDayActivity1 a on a.EMP_ClockNumber = e.EMP_ClockNumber
where(getdate() >= e.EMP_Join_Date) and
(
(e.EMP_Left_Date is null) or
(
getdate() <=
case when e.EMP_Proposal_Date <= e.EMP_Left_Date then e.EMP_Proposal_Date
else e.EMP_Left_Date
end
)
)
union
select'EVY', 'Everyone', cast(0 as bit), 2,
count(a.EMP_ClockNumber) Total,
count(case when a.ACT_Status = 'In' then a.EMP_ClockNumber end),
count(case when a.ACT_Status = 'Out' then a.EMP_ClockNumber end),
count(case when a.ACT_Status = 'Break' then a.EMP_ClockNumber end),
count(case when a.ACT_Status = 'Absent' then a.EMP_ClockNumber end)
from[(local)\ISYS].Intelligent.dbo.Employees e left join
[(local)\ISYS].Intelligent.dbo.vwStaffDayActivity1 a on a.EMP_ClockNumber = e.EMP_ClockNumber
where(getdate() >= e.EMP_Join_Date) and
(
(e.EMP_Left_Date is null) or
(
getdate() <=
case when e.EMP_Proposal_Date <= e.EMP_Left_Date then e.EMP_Proposal_Date
else e.EMP_Left_Date
end
)
) and @Grouping = 'false'
union
selectcoalesce(s.SDP_Code, d.DEP_Code), coalesce(s.SDP_Name, d.DEP_Name), cast(0 as bit),
cast(replace(coalesce(s.SDP_Email, d.DEP_Email), '@', '') as int),
count(a.EMP_ClockNumber),
count(case when a.ACT_Status = 'In' then a.EMP_ClockNumber end),
count(case when a.ACT_Status = 'Out' then a.EMP_ClockNumber end),
count(case when a.ACT_Status = 'Break' then a.EMP_ClockNumber end),
count(case when a.ACT_Status = 'Absent' then a.EMP_ClockNumber end)
from[(local)\ISYS].Intelligent.dbo.Employees e left join
[(local)\ISYS].Intelligent.dbo.Departments d on d.DEP_Code = e.EMP_DEP_Code left join
[(local)\ISYS].Intelligent.dbo.[Sub Departments] s on s.SDP_Code = e.EMP_SUB_DEP_Code left join
[(local)\ISYS].Intelligent.dbo.vwStaffDayActivity1 a on a.EMP_ClockNumber = e.EMP_ClockNumber
where(getdate() >= e.EMP_Join_Date) and
(
(e.EMP_Left_Date is null) or
(
getdate() <=
case when e.EMP_Proposal_Date <= e.EMP_Left_Date then e.EMP_Proposal_Date
else e.EMP_Left_Date
end
)
) and @Grouping = 'true'
group bycoalesce(s.SDP_Code, d.DEP_Code), coalesce(s.SDP_Name, d.DEP_Name),
cast(replace(coalesce(s.SDP_Email, d.DEP_Email), '@', '') as int)
order by[Rank];
When grouping is false, this is the dataset which is spot on:
DEP_Code DEP_Name Favourite Rank Total In Out Break Absent
FAV Favourites 1 1 5 1 2 0 2
EVY Everyone 0 2 73 13 49 0 11
But when grouping is true, the Everyone select is still there, because I am using literal values.
DEP_Code DEP_Name Favourite Rank Total In Out Break Absent
FAV Favourites 1 1 5 1 2 0 2
EVY Everyone 0 2 0 0 0 0 0
PART Partners 0 2 11 5 3 0 3
ATTO Attorneys 0 3 7 1 6 0 0
TRAIN Trainees 0 4 14 5 4 0 5
CON Consultants 0 5 1 0 1 0 0
LON PAs Group 1 0 6 6 0 6 0 0
SEV PAs Group 2 0 7 11 1 10 0 0
ACHR Accounts & HR 0 8 11 0 11 0 0
IT IT 0 9 2 0 0 0 2
REC Records 0 10 10 1 8 0 1
I want favourites plus everyone or favourites plus groups.