Solved

SQL query assistance to change constant to variable

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

726 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