[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Select syntax based on two tables and a dropdownlist value

Posted on 2014-04-09
8
Medium Priority
?
245 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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

Industry Leaders: 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!

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard 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.
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…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

650 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