Solved

Create calculation and case in query with times

Posted on 2016-12-01
13
30 Views
Last Modified: 2016-12-27
Anyone up for a bit of a challenge? I have a query like the one below.

SELECT id, timeIn, timeOut, carType, limitTimeToCheckIn
FROM         masterCarTable LEFT OUTER JOIN
                      carTypesTable ON masterCarTable.carType = carTypesTable.carType

Open in new window

timeIn is a datetime column
timeOut is a datetime column
carType is a string
limitTimeToCheckIn is a number of minutes, for example "15"
newColumn1 is timeIn plus minutes in limitTimeToCheckIn
newColumn2 this should contain a 1 or null - see below.

I would like to add 2 columns to check Select statement. First, I'd like a column that takes the timeIn time, and adds the number of minutes to it from the LimitTimeToCheckIn column. Next, if the total time in the new column is later than the timeOut column, I'd like to have a "1" appear in the 2nd new column. If timeOut is null, it should use "getdate()"

Is this possible? Thank you!
0
Comment
Question by:earwig75
  • 6
  • 6
13 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41909307
<Total Air Code.  I do my own stunts too >
SELECT a.*, 
   CASE name_me_1 WHEN name_me_1 > TIMEOUT THEN CAST(1 AS varchar(25) ELSE CAST(GETDATE() AS VARCHAR(25)) AS name_me_2
FROM (
   SELECT id, timeIn, timeOut, carType, limitTimeToCheckIn, DATEADD(mi, limitTimeToCheckIn, timeIn) AS name_me_1
   FROM masterCarTable 
      LEFT JOIN carTypesTable ON masterCarTable.carType = carTypesTable.carType) a

Open in new window


>if ... have a "1" appear ... If ...  "getdate()"
You'll want to rethink that, as 1 is a number and getdate() returns a date, so the only way to get both values in the same column would be to declare it as a varchar and specifically CAST both values.
0
 

Author Comment

by:earwig75
ID: 41909315
I think you may have misunderstood.

A 1 should only appear in the column "newColumn2". I want to use getdate() instead of "timeOut" if timeOut is null.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41909318
Okay.. Try this
SELECT 
   id, 
   timeIn, 
   CASE WHEN timeOut IS NULL THEN GETDATE() ELSE timeOut END AS TimeOut, -- or  ISNULL(timeOut, GETDATE()) AS timeOut, 
   carType, 
   limitTimeToCheckIn,
   CASE name_me_1 WHEN name_me_1 > TIMEOUT THEN CAST(1 AS varchar(25) ELSE CAST(GETDATE() AS VARCHAR(25)) AS name_me_2
FROM (
   SELECT id, timeIn, timeOut, carType, limitTimeToCheckIn, DATEADD(mi, limitTimeToCheckIn, timeIn) AS name_me_1
   FROM masterCarTable 
      LEFT JOIN carTypesTable ON masterCarTable.carType = carTypesTable.carType) a

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 41909322
correction

SELECT 
   id, 
   timeIn, 
   CASE WHEN timeOut IS NULL THEN GETDATE() ELSE timeOut END AS TimeOut, -- or  ISNULL(timeOut, GETDATE()) AS timeOut, 
   carType, 
   limitTimeToCheckIn,
   CASE WHEN name_me_1 > timeOut THEN 1 ELSE NULL END AS name_me_2
FROM (
   SELECT id, timeIn, timeOut, carType, limitTimeToCheckIn, DATEADD(mi, limitTimeToCheckIn, timeIn) AS name_me_1
   FROM masterCarTable 
      LEFT JOIN carTypesTable ON masterCarTable.carType = carTypesTable.carType) a

Open in new window

0
 

Author Comment

by:earwig75
ID: 41909336
Actually, something is wrong. I have some examples where the time difference is greater than 20 minutes between the two times, and the new column is still showing NULL, instead of 1... I don't know how to re-open this topic.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41909337
We're not to shabby when we're sober.  Thanks for the grade, good luck with your project.  -Jim
0
 

Author Comment

by:earwig75
ID: 41909338
Hi Jim, something appears to be wrong. If I have to, I'll re-post this question to open a new one.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41909340
You'll have to provide some sample data of what you're looking at for us to troubleshoot.

>DATEADD(mi, limitTimeToCheckIn, timeIn) AS name_me_1
One possibility is if either limitTimeToCheckIn or timeIn IS NULL then the result of the DATEADD will be NULL.  Please verify.
0
 

Author Comment

by:earwig75
ID: 41909341
I think I just have to change the sign from greater than, to less than. :)
0
 

Author Comment

by:earwig75
ID: 41909349
If the total time (timeIn plus limitTimeToCheckIn) is later than the timeOut column, I'd like to have a "1" appear. Should changing the sign from greater than, to less than fix this? It appears so, but your code seems to read correctly, so I am confused, sorry.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41909363
At first glance my T-SQL looks correct, but experts here cannot connect to your data source and run queries, so we can only guess based on what question askers type, which is why experts ask for sample data all the time.  

You might just have to play with it a little to see what's going on.
0
 

Author Closing Comment

by:earwig75
ID: 41909405
This solution is good, but the sign must be changed from greater than, to less than.
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

828 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