Solved

Select syntax based on two tables and a dropdownlist value

Posted on 2014-04-09
8
232 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
 

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

759 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

18 Experts available now in Live!

Get 1:1 Help Now