Solved

SQL query assistance to change constant to variable

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Copy Database Wizard 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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

789 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