?
Solved

SQL query assistance to change constant to variable

Posted on 2014-01-24
5
Medium Priority
?
206 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
[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
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 41

Accepted Solution

by:
Sharath earned 2000 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

762 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