Link to home
Create AccountLog in
Avatar of thenrich
thenrich

asked on

sql question

Ok I have the following SQL:

“Select * lc_lt_transaction”

In my dataset I have 3 fields: ‘startime’,’endtime’ and hrs.

Most records have a value in each field but there are some cases where ‘endtime’ and ‘hrs’ are null. I need to construct an SQL where my return dataset puts a calculated value in ‘hrs’ based on the ‘starttime’ and ‘endtime’ without actually populating the database just the resultset.
ASKER CERTIFIED SOLUTION
Avatar of knightEknight
knightEknight
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
select startime,endtime, DATEDIFF(HOUR,startime,Endtime) as  hrs.
Avatar of thenrich
thenrich

ASKER

I guess I'm nor understanding how to get the current database time as the endtime?
need to know the compleate logic

if you have a starttime and an endtime in the database you can use the datediff function to get your hours

If you have an hours value do you want to use that?

if the endtime is null what time do you use?

can you give a more detailed sample  

I've got this and it appears to work:

SELECT     start_time, end_time, hrs, ISNULL(end_time, CURRENT_TIMESTAMP) - start_time AS hours
FROM         lc_lt_transaction

All I need to do now is convert 'hours' to 3 decimal places vs a datetime stamp.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
>> All I need to do now is convert 'hours' to 3 decimal places vs a datetime stamp.
How do you want to display the Hours column?
ok I'm using this:

SELECT     start_time, end_time, hrs, DATEDIFF(hour, ISNULL(end_time, CURRENT_TIMESTAMP), start_time) AS hours
FROM         lc_lt_transaction

hours should be the same format as hrs (see picture) User generated image
got it!

SELECT     start_time, end_time, hrs, round(datediff(ms,ISNULL(end_time, CURRENT_TIMESTAMP) , start_time )/(1000.0*60*60),3)  AS hours
FROM         lc_lt_transaction
you guys got me there in a roundabout way.

thx