variance in mins

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

vchu - 17 Apr 2008 17:56 GMT

i have to date fields in informix

anyone knows how to get the variance in mins or second pls?

select (Act_DT - Appt_DT) from contract

will return somelike like 0 01:21 -- h mm:ss

i like to get 81 mins for this case (1*60 + 21) = 81 mins

Thanks
Victor

Reply to this Message

Mike Aubury - 17 Apr 2008 18:16 GMT

dependant on version - something like :

select (Act_DT - Appt_DT)::interval minute(5) to minute
from contract

?

> i have to date fields in informix
>
[quoted text clipped - 8 lines]
> Thanks
> Victor

Reply to this Message

vchu - 17 Apr 2008 19:26 GMT

I tried this , it works

select interval(0) minute(9) to minute + (extend(s.act_arrival_dt, year to
minute) - extend(s.appt_nlt_date, year to minute)) as VarianceInMins,

case
when VarianceInMins<=0 then "On Time/Early"
when VarianceinMins>60 then "Late > 1 hr"
when VarianceinMins>=15 and VarianceinMins<=60 then "Late
15mins - 1 hr"
when VarianceinMins>0 and VarianceinMins<15 then "Late 15mins -
1 hr"
when VarianceinMins<=0 then "On time < 15mins"
else ""
end AS Performance,

but it is not working on the case, it is looking for the field
"VarianceInMins"

any idea??

>dependant on version - something like :
>
[quoted text clipped - 8 lines]
>> Thanks
>> Victor

Reply to this Message

Mike Aubury - 17 Apr 2008 20:15 GMT

I dont think you can use a column alias like that unless you put it into a
temp table first..

Also - there seems to be a lot of casting/arithmetic in - so shouldnt that
just be:

    case
        when s.act_arrival_dt-s.appt_nlt_date<=0 THEN "On time early"
        when s.act_arrival_dt-s.appt_nlt_date>60 units minutes THEN "Over an hour"
..
etc

(assuming act_arrival_dt and appt_nlt_date are both datetimes)

> I tried this , it works
>
[quoted text clipped - 30 lines]
> >> Thanks
> >> Victor

Reply to this Message

vchu - 17 Apr 2008 20:47 GMT

appt_nlt_date is date field only :(

>I dont think you can use a column alias like that unless you put it into a
>temp table first..
[quoted text clipped - 15 lines]
>> >> Thanks
>> >> Victor

Reply to this Message

Mike Aubury - 17 Apr 2008 20:51 GMT

So - just cast (or extend) it - and it'll be a datetime :-)

I'm not sure you're maths makes much sense though if you're worried about
hours and minutes - when one of your operands is measured in days!

> appt_nlt_date is date field only :(
>
[quoted text clipped - 18 lines]
> >> >> Thanks
> >> >> Victor

Reply to this Message

vchu - 17 Apr 2008 21:12 GMT

Actually I need to

select s.act_arrival_dt - s.appt_nlt_date + s.appt_nlt_time from contract

s.act_arrival_dt is datetime field
s.appt_nlt_date is date only
s.appt_nlt_time is time only

any advice?

>So - just cast (or extend) it - and it'll be a datetime :-)
>
[quoted text clipped - 6 lines]
>> >> >> Thanks
>> >> >> Victor

Reply to this Message

Carsten Haese - 18 Apr 2008 00:08 GMT

> Actually I need to
>
[quoted text clipped - 5 lines]
>
> any advice?

1) Shoot the person that designed the table structure.

2) Use the following function to combine separate date and time fields
to a single datetime field:

create function combine_date_time(d date, t datetime hour to second)
returning datetime year to second;

return (extend(d, year to second) + (t-"00:00:00"));
end function;

HTH,

--
Carsten Haese
http://informixdb.sourceforge.net

Reply to this Message

vchu - 18 Apr 2008 17:14 GMT

I tried this, but :( am I missing something?
sorry I am new in informix

select appt_nlt_date, appt_nlt_time, (extend(appt_nlt_date, year to second) +
(appt_nlt_time - "00:00:00")) from contract

>> Actually I need to
>>
[quoted text clipped - 18 lines]
>Carsten Haese
>http://informixdb.sourceforge.net

Reply to this Message

Carsten Haese - 18 Apr 2008 17:55 GMT

> I tried this, but :( am I missing something?
>
> sorry I am new in informix
>
> select appt_nlt_date, appt_nlt_time, (extend(appt_nlt_date, year to second) +
> (appt_nlt_time - "00:00:00")) from contract

Please feel free to elaborate on what ":(" means.

As a general guideline, when you need help with a problem, you should
always provide at least the following information:

1) What did you do?
2) What outcome did you expect?
3) What happened instead?

(Note that "It doesn't work" doesn't qualify as an appropriate answer to
question 3.)

You answered 1, but not 2 and 3.

--
Carsten Haese
http://informixdb.sourceforge.net

Reply to this Message

vchu - 18 Apr 2008 21:02 GMT

sorry for the confusion.

I ran this sql, but it said "extra charactors at the end of the datetime or
interval"

select appt_nlt_date, appt_nlt_time, (extend(appt_nlt_date, year to second) +
(appt_nlt_time - "00:00:00")) from contract

I am trying to combine appt_nlt_date and appt_nlt_time as a datetime field,
so that I can you for subtraction of another datetime field later.

>> I tried this, but :( am I missing something?
> >
[quoted text clipped - 20 lines]
>Carsten Haese
>http://informixdb.sourceforge.net

Reply to this Message

Carsten Haese - 18 Apr 2008 22:10 GMT

> sorry for the confusion.
>
> I ran this sql, but it said "extra charactors at the end of the datetime or
> interval"

That means that your appt_nlt_time is not a "datetime hour to second."
It's probably a "datetime hour to minute". (Of course, if you had *told*
us what it is, I wouldn't have to guess!)

Try replacing "second" with "minute" and "00:00:00" with "00:00".

HTH,

--
Carsten Haese
http://informixdb.sourceforge.net

Reply to this Message

vchu - 21 Apr 2008 15:59 GMT

It works now.

Thanks so much.. I really appreciated your help. :)

>> sorry for the confusion.
>>
[quoted text clipped - 12 lines]
>Carsten Haese
>http://informixdb.sourceforge.net

Reply to this Message