?
Solved

Select syntax based on two tables and a dropdownlist value

Posted on 2014-04-09
8
Medium Priority
?
243 Views
Last Modified: 2014-04-19
Still working on the benefit time app. the following code works to populate a detailsview control based on a specified StaffMemberID:
SELECT VacationEarned-VacationUsed [Vacation Left],
       PersonalEarned-PersonalUsed [Personal Left],
       SickEarned-SickUsed [Sick Left]
FROM
  (SELECT StaffMemberID,
          SUM(VacationEarned) VacationEarned ,
          SUM(PersonalEarned) PersonalEarned,
          SUM(SickEarned) SickEarned
   FROM TimeEarned
   WHERE StaffMemberID=3

Open in new window


Now I need to do the same thing only have it based upon my StaffMemberNameDropdownList selected value.

As always, thank you for your help
0
Comment
Question by:ICantSee
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
8 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39989798
How are you currently executing your SQL from your code?
0
 

Author Comment

by:ICantSee
ID: 39989865
I am using Visual Studio 2012 and vb as the language.Notice the green arrow. That is a listview that should be showing the remaining time a Staff Member has
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 39990142
Hey ICantSee,
where are you getting the fields [VacationUsed], [PersonalUsed] and [SickUsed] from?
They would need to be included in the inner-select if you want to reference them in the outer-select.

Oh and what is the name of your staff/employee table, could we see some table schema?

Respectfully yours,
Alan
0
Create CentOS 7 Newton Packstack Running Keystone

A bug was filed against RDO for the installation of Keystone v3. This guide is designed to walk you through the configuration for using Keystone v3 with Packstack. You will accomplish this using various repos and the Answers file.

 

Author Comment

by:ICantSee
ID: 39991253
The fields that you are asking about are in a table called TimeUsed.

As embarrassing as it may be, I must admit that I am not an expert with SQL (yet) and am unsure what you mean regarding "inner select" and "outer select".

Basically I have a TimeEarned table that tracks what a Staff Member is given at the beginning of the year.

I have a TimeUsed table that tracks the time he / she uses

Time remaining was actually being calculated with the code I posted with the original question and then displayed to the user on their benefit time request page. That information is not stored in a table. Maybe it should be? :

Notice the green arrow
0
 

Author Comment

by:ICantSee
ID: 39992520
My apologies... I did not copy the entire syntax in my original question. Here it is:
SELECT VacationEarned-VacationUsed [Vacation Left],
       PersonalEarned-PersonalUsed [Personal Left],
       SickEarned-SickUsed [Sick Left]
FROM
  (SELECT StaffMemberID,
          SUM(VacationEarned) VacationEarned ,
          SUM(PersonalEarned) PersonalEarned,
          SUM(SickEarned) SickEarned
   FROM TimeEarned
   WHERE StaffMemberID=3
   GROUP BY StaffMemberID ) TE
JOIN
  (SELECT StaffMemberID,
          SUM(VacationUsed) VacationUsed ,
          SUM(PersonalUsed) PersonalUsed,
          SUM(SickUsed) SickUsed
   FROM TimeUsed
   WHERE StaffMemberID=3
   GROUP BY StaffMemberID ) TU ON TE.StaffMemberID = TU.StaffMemberID

Open in new window


I just need to base this on my dropdown list selected value
0
 

Accepted Solution

by:
ICantSee earned 0 total points
ID: 39998764
This seems to be the answer. I have not fully tested it yet.

The syntax:
        <asp:SqlDataSource ID="TimeRemainingByStaffMemberIDDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:BenefitTimeConnectionString %>" SelectCommand="SELECT VacationEarned-VacationUsed [Vacation Left],
       PersonalEarned-PersonalUsed [Personal Left],
       SickEarned-SickUsed [Sick Left]
FROM
  (SELECT StaffMemberID,
          SUM(VacationEarned) VacationEarned ,
          SUM(PersonalEarned) PersonalEarned,
          SUM(SickEarned) SickEarned
   FROM TimeEarned
   WHERE StaffMemberID=@StaffMemberId
   GROUP BY StaffMemberID ) TE
JOIN
  (SELECT StaffMemberID,
          SUM(VacationUsed) VacationUsed ,
          SUM(PersonalUsed) PersonalUsed,
          SUM(SickUsed) SickUsed
   FROM TimeUsed
   WHERE StaffMemberID=@StaffMemberID
   GROUP BY StaffMemberID ) TU ON TE.StaffMemberID = TU.StaffMemberID">

Open in new window


Also, the following needs to be placed just before your closing asp:datasource tag:

            <SelectParameters>
                <asp:ControlParameter Name="StaffMemberID" ControlID="FilterByNameDropDownList" PropertyName="SelectedValue" />
            </SelectParameters>
        </asp:SqlDataSource>

Open in new window


This needs to be tested more, but it seems to be working.
0
 

Author Comment

by:ICantSee
ID: 39999931
Yes, the code in my last post works. I am closing the ticket.
0
 

Author Closing Comment

by:ICantSee
ID: 40010154
I came up with the answer. My original select statement combined with the            <SelectParameters>
                <asp:ControlParameter Name="StaffMemberID" ControlID="FilterByNameDropDownList" PropertyName="SelectedValue" />
            </SelectParameters>
        </asp:SqlDataSource>

addition to my asp;datasource was the answer.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
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.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

770 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