Be seen. Boost your questionâ€™s priority for more expert views and faster solutions
SELECT StaffMemberID = ISNULL(a.StaffMemberID,b.StaffMemberID)
, VacationRemaining = ISNULL(b.VacationEarned,0) - ISNULL(a.VacationUsed,0)
, PersonalRemaining = ISNULL(b.PersonalEarned,0) - ISNULL(a.PersonalUsed,0)
, SickRemaining = ISNULL(b.SickEarned,0) - ISNULL(a.SickUsed,0)
FROM
( SELECT StaffMemberID
, VacationUsed = sum(case when RequestType = 'Vacation' then RequestHours end)
, PersonalUsed = sum(case when RequestType = 'Personal' then RequestHours end)
, SickUsed = sum(case when RequestType = 'Sick' then RequestHours end)
FROM TimeUsed
GROUP
BY StaffMemberID
) a
FULL
JOIN
( SELECT StaffMemberID
, VacationEarned = sum(VacationEarned)
, PersonalEarned = sum(PersonalEarned)
, SickEarned = sum(SickEarned)
FROM TimeEarned
GROUP
BY StaffMemberID
) b ON a.StaffMemberID = b.StaffMemberID
WITH Summed AS (
SELECT StaffMemberID,
RequestType,
SUM(RequestHours) AS SumRequested
FROM TimeUsed
GROUP BY StaffMemberID,
RequestType
)
SELECT S.StaffMemberID,
S.RequestType,
S.SumRequested - COEALESCE( CASE WHEN S.RequestType = 'Vacation' THEN TE.VacationEarned
WHEN S.RequestType = 'Personal' THEN TE.PersonalEarned
WHEN S.RequestType = 'Sick' THEN TE.SickEarned
END, 0 )
FROM Summed S
LEFT JOIN TimeEarned TE ON S.StaffMemberID = TE.StaffMemberID
AND S.RequestType = TE.RequestType;
With req as
(select staffmemberid,
sum(case when requesttype = 'Vacation' then requesthours else 0 end) as vacationreq,
sum(case when requesttype = 'Sick' then requesthours else 0 end) as sickreq,
sum(case when requesttype = 'Personal' then requesthours else 0 end) as personalreq
from timeused
group by staffmemberid)
select staffmemberid,
(vacationEarned - req.vacationreq) as vac_req_earn_diff,
(SickEarned - req.sickreq) as sick_req_earn_diff,
(PersonalEarned - req.personalreq) as pers_req_earn_diff
From timeearned as te
where te.staffmemberid = req.staffmemberid
and te.staffmemberid = 2;
but now I am getting :SELECT StaffMemberID = ISNULL(a.StaffMemberID,b.StaffMemberID)
, VacationRemaining = ISNULL(b.VacationEarned,0) - ISNULL(a.VacationUsed,0)
, PersonalRemaining = ISNULL(b.PersonalEarned,0) - ISNULL(a.PersonalUsed,0)
, SickRemaining = ISNULL(b.SickEarned,0) - ISNULL(a.SickUsed,0)
FROM
( SELECT StaffMemberID
, VacationUsed = sum(case when RequestType = 'Vacation' then RequestHours end)
, PersonalUsed = sum(case when RequestType = 'Personal' then RequestHours end)
, SickUsed = sum(case when RequestType = 'Sick' then RequestHours end)
FROM TimeUsed
WHERE StaffMemberID = 2
GROUP
BY StaffMemberID
) a
FULL
JOIN
( SELECT StaffMemberID
, VacationEarned = sum(VacationEarned)
, PersonalEarned = sum(PersonalEarned)
, SickEarned = sum(SickEarned)
FROM TimeEarned
WHERE StaffMemberID = 2
GROUP
BY StaffMemberID
) b ON a.StaffMemberID = b.StaffMemberID
"SELECT StaffMemberID = ISNULL(a.StaffMemberID,b.StaffMemberID)
, VacationRemaining = ISNULL(b.VacationEarned,0) - ISNULL(a.VacationUsed,0)
, PersonalRemaining = ISNULL(b.PersonalEarned,0) - ISNULL(a.PersonalUsed,0)
, SickRemaining = ISNULL(b.SickEarned,0) - ISNULL(a.SickUsed,0)
FROM
( SELECT StaffMemberID
, VacationUsed = sum(case when RequestType = 'Vacation' then RequestHours end)
, PersonalUsed = sum(case when RequestType = 'Personal' then RequestHours end)
, SickUsed = sum(case when RequestType = 'Sick' then RequestHours end)
FROM TimeUsed
GROUP
BY StaffMemberID
) a
FULL
JOIN
( SELECT StaffMemberID
, VacationEarned = sum(VacationEarned)
, PersonalEarned = sum(PersonalEarned)
, SickEarned = sum(SickEarned)
FROM TimeEarned
Where [StaffMemberID] = @StaffMemberID)">
<SelectParameters><asp:ControlParameter Name="Title"
ControlID="FilterByNameDropDownList"
PropertyName="SelectedValue"/></SelectParameters>
</asp:SqlDataSource>
Select s.StaffMemberFirstName 'First Name',s.StaffMemberLastName 'Last Name'
, VacationEarned 'Vacation Earned',SickEarned 'Sick Earned',PersonalEarned 'Personal Earned'
,TU.RequestType, sum(TU.RequestHours)'Hours Requested'
from
TimeEarned TE Join TimeUsed TU
on Te.StaffMemberID = tu.StaffMemberID
join Requests R on
R.StaffMemberID = TU.StaffMemberID
join StaffMembers s on
s.StaffMemberID=R.StaffMemberID
where tu.StaffMemberID = @StaffMemberID
group by s.StaffMemberFirstName,s.StaffMemberLastName, VacationEarned, SickEarned,PersonalEarned
,TU.RequestType
"SELECT StaffMemberID = ISNULL(a.StaffMemberID,b.StaffMemberID)
, VacationRemaining = ISNULL(b.VacationEarned,0) - ISNULL(a.VacationUsed,0)
, PersonalRemaining = ISNULL(b.PersonalEarned,0) - ISNULL(a.PersonalUsed,0)
, SickRemaining = ISNULL(b.SickEarned,0) - ISNULL(a.SickUsed,0)
FROM
( SELECT StaffMemberID
, VacationUsed = sum(case when RequestType = 'Vacation' then RequestHours end)
, PersonalUsed = sum(case when RequestType = 'Personal' then RequestHours end)
, SickUsed = sum(case when RequestType = 'Sick' then RequestHours end)
FROM TimeUsed
WHERE StaffMemberID = @StaffMemberID
GROUP
BY StaffMemberID
) a
FULL
JOIN
( SELECT StaffMemberID
, VacationEarned = sum(VacationEarned)
, PersonalEarned = sum(PersonalEarned)
, SickEarned = sum(SickEarned)
FROM TimeEarned
WHERE StaffMemberID = @StaffMemberID
GROUP
BY StaffMemberID
) b ON a.StaffMemberID = b.StaffMemberID">
<SelectParameters>
<asp:ControlParameter ControlID="FilterByNameDropDownList" Name="StaffMemberID" PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Open in new window