Select syntax based on two tables and a dropdownlist value

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
ICantSeeAsked:
Who is Participating?
 
ICantSeeAuthor Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
How are you currently executing your SQL from your code?
0
 
ICantSeeAuthor Commented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
Alan WarrenApplications DeveloperCommented:
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
 
ICantSeeAuthor Commented:
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
 
ICantSeeAuthor Commented:
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
 
ICantSeeAuthor Commented:
Yes, the code in my last post works. I am closing the ticket.
0
 
ICantSeeAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.