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
226 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 32

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
 
LVL 32

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 31

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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Sql server lock cursor 13 41
SQL query 4 31
Very interesting Access query problem. 13 44
affinity mask in sql server 1 6
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now