Solved

Create calculation and case in query with times

Posted on 2016-12-01
13
4 Views
Last Modified: 2016-12-01
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
Comment Utility
<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
Comment Utility
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
Comment Utility
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
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
We're not to shabby when we're sober.  Thanks for the grade, good luck with your project.  -Jim
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:earwig75
Comment Utility
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
Comment Utility
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
Comment Utility
I think I just have to change the sign from greater than, to less than. :)
0
 

Author Comment

by:earwig75
Comment Utility
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
Comment Utility
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
Comment Utility
This solution is good, but the sign must be changed from greater than, to less than.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Save sql query result in sql server 15 22
Help in Understanding a SQL Query 7 24
Sql query for filter 12 18
Sql query 34 14
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

763 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

6 Experts available now in Live!

Get 1:1 Help Now