Solved

Create calculation and case in query with times

Posted on 2016-12-01
13
38 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
[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
  • 6
  • 6
13 Comments
 
LVL 66

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 66

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 66

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 66

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 66

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 66

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

617 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