Hi,

I have a table like this:

Field1: integer
Field2: integer
MyDate: integer;

Since they don't work on all days of the year, I have to find out (as
fast as possible) the number of days which has entries in this table.
Same thing after that with months.

Till now I did it like this:

  select distinct MyDate from MyTable where MyYear = 2008;

and used "recordcount" of this query to find the number of days.

Is there a faster and more elegant way to accomplish what I want?

Thanks Uli

Uli

>I have a table like this:
>
>Field1: integer
>Field2: integer
>MyDate: integer;
>
>Since they don't work on all days of the year, I have to find out (as
>fast as possible) the number of days which has entries in this table.
>Same thing after that with months.
>
>Till now I did it like this:
>
> select distinct MyDate from MyTable where MyYear = 2008;
>
>and used "recordcount" of this query to find the number of days.
>
>Is there a faster and more elegant way to accomplish what I want?

Apart from I can't see MyYear in the table you could try something like

SELECT COUNT(1) AS DaysInYear FROM MyTable GROUP BY MyYear

That will give you a count for all years

If you want more please give us the full table structure.

Roy Lambert [Team Elevate]

Roy

> SELECT COUNT(1) AS DaysInYear FROM MyTable GROUP BY MyYear
>
> That will give you a count for all years

I didn't describe it well, sorry.

What I want to know is how many different dates have entries in one year.
A typical result would be 250 in one year.

Uli

based off your select statement, could something like this work?

select distinct count(MyDate) from MyTable group by MyDate having MyYear =
2008;

It should show the # of entries for the year.



jr


"Ulrich Becker" <test@test.com> wrote in message
news:49BC1E40.7010500@test.com...
> Roy
>
>> SELECT COUNT(1) AS DaysInYear FROM MyTable GROUP BY MyYear
>>
>> That will give you a count for all years
>
> I didn't describe it well, sorry.
>
> What I want to know is how many different dates have entries in one year.
> A typical result would be 250 in one year.
>
> Uli

James,

thank you.

> select distinct count(MyDate) from MyTable group by MyDate having MyYear =
> 2008;

Actually your query shows how many entries per day have been made in one
year.
What I want is just the number of days in one year, where entries have
been made. Your query results in 256 records e.g.. That's the number I
need for each year.

Uli

Ulrich


In that case you need someone a lot better at sql than me. Anything I can figure out is a two stage process much like the one you're currently using.

Roy Lambert

Roy,

> In that case you need someone a lot better at sql than me. Anything I can figure out is a two stage process much like the one you're currently using.

Yes, I think so.

BTW: I know you are quite good at SQL

Uli Becker

Uli

> Field1: integer
> Field2: integer
> MyDate: integer;
>
> Since they don't work on all days of the year, I have to find out (as
> fast as possible) the number of days which has entries in this table.
> Same thing after that with months.
>
> Till now I did it like this:
>
>    select distinct MyDate from MyTable where MyYear = 2008;
>
> and used "recordcount" of this query to find the number of days.
>
> Is there a faster and more elegant way to accomplish what I want?
>

I don't know about faster but what about

SELECT COUNT(*) FROM
(SELECT DISTINCT MyDate FROM MyTable WHERE MyYear=2008) t1

If you want totals for individual months (and MyDate is a date field) you
could use something like

SELECT EXTRACT(YEAR FROM MyDate) AS Yearnum,
EXTRACT(MONTH FROM MyDate) AS Monthnum,
COUNT(*) FROM
(SELECT DISTINCT MyDate FROM MyTable WHERE MyDate between :StartDate and
:EndDate) t1
GROUP BY Yearnum,Monthnum
ORDER BY Yearnum,Monthnum

John

Try changing your SQL line from:
>select distinct MyDate from MyTable where MyYear = 2008;

to 'select distinct count(MyDate) from MyTable where MyYear = 2008;  '

I deal with this type of thing all the time but never with ElevateDB. I'm
assuming it can be done in 1 statement here too. Could you post a few MyDate
values so I have something to play around with?


jr




"Uli Becker" <test@test.com> wrote in message
news:49BCCCDD.5000303@test.com...
> James,
>
> thank you.
>
>> select distinct count(MyDate) from MyTable group by MyDate having MyYear
>> = 2008;
>
> Actually your query shows how many entries per day have been made in one
> year.
> What I want is just the number of days in one year, where entries have
> been made. Your query results in 256 records e.g.. That's the number I
> need for each year.
>
> Uli

John,

> I don't know about faster but what about
>
> SELECT COUNT(*) FROM
> (SELECT DISTINCT MyDate FROM MyTable WHERE MyYear=2008) t1

It's not exactly what I want (select some values from MyTable and the
number of day with eintries in one year in ONE statement), but actually
your solution with a derived table is very fast! It helped.

Thank you!.

Uli