Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Create calculation and case in query with times

Posted on 2016-12-01
13
Medium Priority
?
44 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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. …
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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

688 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