Solved

Create calculation and case in query with times

Posted on 2016-12-01
13
29 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

832 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