Solved

SQL query assistance to change constant to variable

Posted on 2014-01-24
5
200 Views
Last Modified: 2014-03-18
Below is my view where my WHERE statement queries out '7.5'. I would like to pull this as a variable from the field called EmpTaskHours where CalendarDays = today. If today is 1/24/14 - the emptaskhours is 4.75 so the static '7.5' query would not work.

Thanks!

SELECT     Training_Hours_Exception2.TrainingDate, Training_Hours_Exception2.Trainee, Training_Hours_Exception2.TotalHours, 
                      dbo.Training_Employees.SubDept AS EmpDepartment, dbo.Training_Employees.Position, dbo.Calendar.OfficeWorkdays, dbo.Calendar.EmpTaskHours, 
                      dbo.Calendar.CalendarDays
FROM         (SELECT     TrainingDate, Trainee, SUM(TotalHours) AS TotalHours
                       FROM          dbo.Training_Hours_Exception
                       GROUP BY TrainingDate, Trainee) AS Training_Hours_Exception2 INNER JOIN
                      dbo.Calendar ON Training_Hours_Exception2.TrainingDate = dbo.Calendar.CalendarDays LEFT OUTER JOIN
                      dbo.Training_Employees ON Training_Hours_Exception2.Trainee = dbo.Training_Employees.FullName
WHERE     (dbo.Training_Employees.Position NOT LIKE 'Manager') AND (dbo.Calendar.OfficeWorkdays LIKE 'Workday') AND 
                      (Training_Hours_Exception2.TotalHours <> 7.5)

Open in new window

0
Comment
Question by:Bianca
5 Comments
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 39807241
I am not sure if this is the one you are looking for but you can try to change your where clause to:

Training_Hours_Exception2.TotalHours <> (CASE WHEN DATEDIFF(DAY, dbo.Calendar.EmpTaskHours, '1/24/2014') = 0 THEN 4.75 ELSE 7.5 END)

Open in new window

0
 

Author Comment

by:Bianca
ID: 39807380
That will limit my query to either 4.75 or 7.5 but I need it to be a variable of whatever the value is for the day. Tomorrow could be a 6.5 hour day but the standard is 7.5. Today just happens to be a 4.75.  Which is why I want to query the value first based on todays date = CalendarDays. Thanks!
0
 
LVL 1

Expert Comment

by:psreloaded
ID: 39807421
Your query is very diffucult to read. I cleaned it up a bit for my own uderstanding.

--Assuming that you are trying to pull the data from the calendar table. Something like below should work.

SELECT the2.TrainingDate, the2.Trainee, the2.TotalHours,
        te.SubDept AS EmpDepartment, te.Position, c.OfficeWorkdays, c.EmpTaskHours,
        c.CalendarDays
FROM (SELECT TrainingDate, Trainee, SUM(TotalHours) AS TotalHours
        FROM dbo.Training_Hours_Exception
        GROUP BY TrainingDate, Trainee) AS the2
INNER JOIN dbo.Calendar c ON the2.TrainingDate = c.CalendarDays
LEFT OUTER JOIN dbo.Training_Employees te ON the2.Trainee = te.FullName
WHERE te.Position NOT LIKE 'Manager'
    AND c.OfficeWorkdays LIKE 'Workday'
    AND the2.TotalHours <> c.emptaskhours
    AND c.CalendarDays = GETDATE() --or any other date yu wish to use.

Also, please note that -

1. Aliases should be used in queries where you are joining multiple tables for readability.
2. Aliases should be short otherwise it loses its purpose.
0
 

Author Comment

by:Bianca
ID: 39807878
It doesn't give me an error but it also doesn't give me any records where there should be at least 222 in the recordset.  Thanks!
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 39807908
remove the last filter from psreloaded's query.
AND c.CalendarDays = GETDATE() --or any other date yu wish to use.
SELECT the2.TrainingDate, 
       the2.Trainee, 
       the2.TotalHours, 
       te.SubDept AS EmpDepartment, 
       te.Position, 
       c.OfficeWorkdays, 
       c.EmpTaskHours, 
       c.CalendarDays 
  FROM (SELECT TrainingDate, 
               Trainee, 
               SUM(TotalHours) AS TotalHours 
          FROM dbo.Training_Hours_Exception 
         GROUP BY TrainingDate, 
                  Trainee) AS the2 
       INNER JOIN dbo.Calendar c 
               ON the2.TrainingDate = c.CalendarDays 
       LEFT OUTER JOIN dbo.Training_Employees te 
                    ON the2.Trainee = te.FullName 
 WHERE te.Position NOT LIKE 'Manager' 
   AND c.OfficeWorkdays LIKE 'Workday' 
   AND the2.TotalHours <> c.emptaskhours

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Backup & Restore 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.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

861 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

24 Experts available now in Live!

Get 1:1 Help Now