Solved

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

Posted on 2014-04-17
16
228 Views
Last Modified: 2014-04-21
I have a table name TimeUsed. It contains columns:

StaffMemberID, RequestType, RequestHours.

I need to sum Request hours based upon RequestType and StaffMemberID.

The possible values for RequestType are Vacation, Sick, and Personal

I then need to subtract that figure from the TimeEarned table's VacationEarned, PersonalEarned and SickEarned columns.

Your help will be greatly appreciated.
0
Comment
Question by:ICantSee
  • 10
  • 3
  • 2
  • +1
16 Comments
 
LVL 33

Expert Comment

by:ste5an
ID: 40007412
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; 

Open in new window

0
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 40007429
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

Open in new window

0
 

Author Comment

by:ICantSee
ID: 40007460
Thank you both for your replies.

This is the table structure, if it helps:

Time Used
Time Earned
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 33

Expert Comment

by:ste5an
ID: 40007473
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; 

Open in new window


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

Author Comment

by:ICantSee
ID: 40007538
John_Vidmar

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

I am getting everyone's record instead of just hers
0
 

Author Comment

by:ICantSee
ID: 40007582
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?
0
 
LVL 32

Expert Comment

by:awking00
ID: 40007687
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
0
 

Author Comment

by:ICantSee
ID: 40012477
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.
0
 

Author Comment

by:ICantSee
ID: 40012524
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;

Open 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.
0
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 500 total points
ID: 40012764
Add additional filters, you may want to review query-plans to see is it more efficient to add a where-clause to each query (example below) or to add the filter to the on-clause:
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
                                            

Open in new window

0
 

Author Comment

by:ICantSee
ID: 40012938
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>

Open in new window


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

Author Comment

by:ICantSee
ID: 40012953
Maybe I am going about this the hard way?

All I really want is the following:

A way to track a persons benefit time earned"
A way to track how much time that they have taken and of what type (personal, sick, vacation)
A way to calculate how much of each type of benefit time they have left

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?
0
 

Author Comment

by:ICantSee
ID: 40013265
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

Open in new window


Returns this:

Results so far...
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
0
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 40013296
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.
0
 

Author Comment

by:ICantSee
ID: 40013308
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>

Open in new window

0
 

Author Closing Comment

by:ICantSee
ID: 40013310
AWESOME.. THANK YOU !!!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question