?
Solved

Select syntax based on two tables and a dropdownlist value

Posted on 2014-04-09
8
Medium Priority
?
248 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
  • 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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 

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

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
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…

862 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