[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
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
Medium Priority
?
247 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 36

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 36

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 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