LS

having records with 'datum' (date), 'aanvang' and 'einde' (time), where I
can have a couple of records of the same day I want to calculate the begin
and endtime.

select id_medewerker, datum,
min(aanvang) as begin, max(einde) as einde,
(max(einde) - min(aanvang)) as werktijd
from registraties
where id_medewerker = '20005'
and datum between date'2004-07-01' and date'2005-04-01'
group by id_medewerker, datum

How do I format the output of werktijd like this '2 hours 55 minutes' in SQL

Regards, Harry

(cast(max(einde) - min(aanvang) as varchar(25))) as werktijd

comes pretty close.

Regards, Harry

"Harry de Boer" <harry@staaf.nl> schreef in bericht
news:D3662F08-6B67-42C4-8E73-0F7A16D74C74@news.elevatesoft.com...
> LS
>
> having records with 'datum' (date), 'aanvang' and 'einde' (time), where I
> can have a couple of records of the same day I want to calculate the begin
> and endtime.
>
> select id_medewerker, datum,
> min(aanvang) as begin, max(einde) as einde,
> (max(einde) - min(aanvang)) as werktijd
> from registraties
> where id_medewerker = '20005'
> and datum between date'2004-07-01' and date'2005-04-01'
> group by id_medewerker, datum
>
> How do I format the output of werktijd like this '2 hours 55 minutes' in
SQL
>
> Regards, Harry
>
>

Harry,

<<  How do I format the output of werktijd like this '2 hours 55 minutes' in
SQL >>

Use this:

select id_medewerker, datum,
min(aanvang) as begin, max(einde) as einde,
CAST((max(einde) - min(aanvang)) AS VARCHAR(20)) as werktijd
from registraties
where id_medewerker = '20005'
and datum between date'2004-07-01' and date'2005-04-01'
group by id_medewerker, datum

to get 2:55 (example) as the result.  If you want to get the full "x hours x
minutes" text, then you'll need to do some further formatting with some
SUBSTRING() calls or use a UDF like this:

CREATE FUNCTION FormatHourMinuteInterval(IN Value INTERVAL HOUR TO MINUTE)
RETURNS VARCHAR
BEGIN
  DECLARE IntervalString VARCHAR DEFAULT '';
  DECLARE SeparatorPos INTEGER DEFAULT 0;
  DECLARE Hours VARCHAR DEFAULT '';
  DECLARE Minutes VARCHAR DEFAULT '';

  SET IntervalString = CAST(Value AS VARCHAR);
  SET SeparatorPos = POS(':' IN IntervalString);
  SET Hours = LEFT(IntervalString FOR SeparatorPos-1);
  SET Minutes = SUBSTRING(IntervalString FROM SeparatorPos+1 FOR
LENGTH(IntervalString));

  RETURN Hours+' hours '+Minutes+' minutes';
END;


--
Tim Young
Elevate Software
www.elevatesoft.com

And I almost forgot, you're going to want to use this instead for the
substraction:

(max(einde) - min(aanvang)) HOUR TO MINUTE

To make sure that the resulting interval is in hours/minutes, and not the
default of HOUR TO MSECOND, or hours/mseconds.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

> HOUR TO MINUTE to make sure that the resulting interval is in
hours/minutes, and not the
> default of HOUR TO MSECOND, or hours/mseconds.

I tried it (see statement below), but it seems that the result (for
werktijd1 and werktijd2) is the same.

select id_medewerker, datum,
min(aanvang) as begin, max(einde) as einde,
((max(einde) - min(aanvang)) hour to minute) as werktijd1,
(max(einde) - min(aanvang)) as werktijd2
from registraties
where id_medewerker = '20003'
and datum between date'2004-07-01' and date'2005-04-01'
group by id_medewerker, datum

Regards, Harry


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schreef in bericht
news:AA9082FB-9C68-4B58-A19D-7D773A335880@news.elevatesoft.com...
> And I almost forgot, you're going to want to use this instead for the
> substraction:
>
> (max(einde) - min(aanvang)) HOUR TO MINUTE
>
> To make sure that the resulting interval is in hours/minutes, and not the
> default of HOUR TO MSECOND, or hours/mseconds.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

and....

CAST((max(einde) - min(aanvang)) AS VARCHAR(20)) as werktijd
gives the same result as

CAST((max(einde) - min(aanvang)) AS VARCHAR(25)) as werktijd

Regards, Harry

"Harry de Boer" <harry@staaf.nl> schreef in bericht
news:D7AEEA1E-80CA-4B73-9917-FC307801DDFF@news.elevatesoft.com...
> Tim,
>
> > HOUR TO MINUTE to make sure that the resulting interval is in
> hours/minutes, and not the
> > default of HOUR TO MSECOND, or hours/mseconds.
>
> I tried it (see statement below), but it seems that the result (for
> werktijd1 and werktijd2) is the same.
>
> select id_medewerker, datum,
> min(aanvang) as begin, max(einde) as einde,
> ((max(einde) - min(aanvang)) hour to minute) as werktijd1,
> (max(einde) - min(aanvang)) as werktijd2
> from registraties
> where id_medewerker = '20003'
> and datum between date'2004-07-01' and date'2005-04-01'
> group by id_medewerker, datum
>
> Regards, Harry
>
>
> "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schreef in
bericht
> news:AA9082FB-9C68-4B58-A19D-7D773A335880@news.elevatesoft.com...
> > And I almost forgot, you're going to want to use this instead for the
> > substraction:
> >
> > (max(einde) - min(aanvang)) HOUR TO MINUTE
> >
> > To make sure that the resulting interval is in hours/minutes, and not
the
> > default of HOUR TO MSECOND, or hours/mseconds.
> >
> > --
> > Tim Young
> > Elevate Software
> > www.elevatesoft.com
> >
> >
>
>

Harry,

<< I tried it (see statement below), but it seems that the result (for
werktijd1 and werktijd2) is the same. >>

It will give you the same value without the CAST to a string representation.
What I was referring to was using the UDF that I posted, which requires a
HOUR TO MINUTE interval as the parameter.

--
Tim Young
Elevate Software
www.elevatesoft.com

Harry,

<< and....

CAST((max(einde) - min(aanvang)) AS VARCHAR(20)) as werktijd
gives the same result as

CAST((max(einde) - min(aanvang)) AS VARCHAR(25)) as werktijd >>

Correct, the length of the VARCHAR won't matter in this case.   What you
want is this:

CAST((max(einde) - min(aanvang)) HOUR TO MINUTE AS VARCHAR(20)) as werktijd

--
Tim Young
Elevate Software
www.elevatesoft.com

Harry


Somewhat off thread but whereabouts in the manual is the need to stick DATE in front of the date constant (eg DATE 2005-04-01')? And do you know of any more like this eg Timestamp?

Roy Lambert

Roy,

<< Somewhat off thread but whereabouts in the manual is the need to stick
DATE in front of the date constant (eg DATE 2005-04-01')? And do you know of
any more like this eg Timestamp? >>

All of the date/time constants require it:

http://www.elevatesoft.com/edb1sql_date_time_types.htm

--
Tim Young
Elevate Software
www.elevatesoft.com