Sum column base on where clause and then subtract it from a different table's column based on a where cluase

It's a little bit hard to tell without table DDL. Something like this should do it:

WITH Summed AS (
		SELECT	StaffMemberID, 
			RequestType, 
			SUM(RequestHours) AS SumRequested
		FROM	TimeUsed
	)
	SELECT	S.StaffMemberID,
		S.RequestType,
		S.Summed - COEALESCE(TE.SumEarned)
	FROM	Summed S
		LEFT JOIN TimeEarned TE ON S.StaffMemberID = TE.StaffMemberID 
		AND S.RequestType = TE.RequestType; 

Select allOpen in new window

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

Select allOpen in new window

Thank you both for your replies.

This is the table structure, if it helps:

Then I would guess:

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; 

Select allOpen in new window

btw, the GROUP BY was missing in my first answer. And also the wrong sum column name. And the , 0.

John_Vidmar

Your statement works, but I need it to display only the selected StaffMemberID's  record.
It is showing all of them.

ste5an, John_Vidmar,

Is there a way to make this so that it doesn't return all of the records? I only want it to display the record associated with a dropdown list on my page. (See picture above.)

A where clause maybe?

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 time_used
 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 time_earned as te
where te.staffmemberid = req.staffmemberid
and te.staffmemberid = 2;  ==> used 2 because of your example but this can be parameterized

I am getting a "Cannot have multiple items selected in a DropDownList." error.

Is there a way to base this solely on the selected value of the dropdownlist? the control is called StaffMemberID

When a supervisor selects a StaffMember from the dropdown list its selected value (StaffMemberId) will be passed the statement.

On a very simple statement, the where clause looks like this:

WHERE ([StaffMemberID] = @StaffMemberID)

I understand that what we are trying to accomplish is much more complex. I just wanted you to see an example of using the StaffMemberName dropdownlist's selected value as the StaffMemberID.

I have been trying to edit the statement:

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;

Select allOpen in new window

but now I am getting :

"The multi-part identifier "req.staffmemberid" could not be bound"
"The multi-part identifier "req.vacationreq" could not be bound"
"The multi-part identifier "req.sickreq" could not be bound"
"The multi-part identifier "personalreq" could not be bound"

Any ideas will be appreciated.

This is what I have, but it doesn't work. I need the StaffMemberID derived from the dropdownlist control on the page. It cant be coded in.

"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 allOpen in new window

The error is "Must declare the scalar variable "@StaffMemberID".

Maybe I am going about this the hard way?

All I really want is the following:


What I have done is create a table with the Staff member's name, unique ID (StaffMemberID), and the amount of each type of benefit time they have earned.

I then created a request table. It uses the staffmember ID, requesttype, requesthours, and a approved, and declined column.

If the supervisor approves the request, the approved column is checked and the information is written in the  timeused table.

I then want to use simple queries to display the time earned, time used and time remaining based on StaffMemberID. A dropdown list control is used to switch between the Staff Members, which in turn updates the data based on the selected Staff Member's StaffMemberID

Would it be simpler to put all of the information in one table?

This:

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 allOpen in new window

Returns this:


Its summing the amount of each type of benefit time based on the dropdownlist. Now I need it to subtract that figure from the TimeEarned

Looks like you are in ASP.NET, attempting to (dynamically?) build the SQL, you've introduced a replacement-parameter, and now you need some ADO.NET technique to replace that parameter with the user selected value.  That part appears to be missing, you cannot just send that query to the database, ADO.NET must replace @StaffMemberID with a value. Click here to follow a MSDN link that shows how to introduce a SQL parameter into ADO.NET.

Personally, I prefer to use a stored-procedure (SP) because:

1.

You can perform a small miracle via the SP that cannot be achieved by a single ADO.NET command.

2.

You can change the SP without having to compile a new executable.

3.

You save the database some work because the query-plan is cached.

4.

More secure because you are not sending plain-text across a network that potentially can be intercepted.

Disregard my recent posts.

YOU ARE AWESOME.... THANK YOU !!

I changed the staffmemberid= 2 to staffmemberid=@staffmemberID in two places and viola... IT WORKS !!!

"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>

Select allOpen in new window

AWESOME.. THANK YOU !!!