Solved

SQL query assistance to change constant to variable

Posted on 2014-01-24
5
199 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
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…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

760 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