Uli,

> 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)

Just as a matter of interest did you get what you want in one statement
(with the derived table) ?  If not, and you would still like to try, can you
post  a small example of data and result set you are after (I think I'm
being a bit dumb, but I still can't work out what you want .

John

John,

thanks for your kind offer. This is what I am doing now:

PREPARE Stmt FROM
    'Insert into Statistik1 select PraxisID, BereicheID, Jahr,0 as
Arbeitstage, count(*)as Alle,
    sum(cast(privat as integer)) as Privat, count(*) - sum(cast(privat
as integer)) as Kasse from untersuchungen ' + SQLCondition + '
    group by jahr, bereicheID, PraxisID';
  Execute stmt;

  BEGIN
    Execute Immediate 'Drop Table TempArbeitstage';
  EXCEPTION
  END;
  Execute Immediate 'Create Temporary Table TempArbeitstage as
    select count(*) as Arbeitstage, Jahr from
   (select DISTINCT Datum,Jahr  FROM Untersuchungen ' + + SQLCondition
+ ' ) t1 group by Jahr with data';
  Execute Immediate 'update Statistik1 s set Arbeitstage = (select
Arbeitstage from TempArbeitstage a
    where a.jahr = s.jahr)' + SQLCondition;

This is the (simplified) table structure:

PraxisID: integer;
BereicheID: integer;
Datum: date;
Jahr: integer;
Privat: boolean;

Now I have to get statistics for the number of entries with

privat = true
totalcount
average of entries with privat = true

grouped by the year (Jahr), PraxisID and BereichID.

Since I have to get the average of entries per (working-)day I need the
number of days within a year that have entries. Later the same on
month-level.

The code above works fine and very fast - no need to change it. I am not
sure whether this could be done in ONE statement.

Regards Uli

Uli

What about

select PraxisID, BereicheID, Jahr,t2.Arbeitstage, count(*)as Alle,
sum(cast(privat as integer)) as Privat, count(*) -
sum(cast(privat as integer)) as Kasse from Untersuchungen
left outer join
(select count(*) as Arbeitstage, Jahr from
(select DISTINCT Datum,Jahr FROM Untersuchungen) t1 group by Jahr) t2
on t2.jahr=Untersuchungen.jahr
group by jahr, bereicheID, PraxisID

John

John
> select PraxisID, BereicheID, Jahr,t2.Arbeitstage, count(*)as Alle,
> sum(cast(privat as integer)) as Privat, count(*) -
> sum(cast(privat as integer)) as Kasse from Untersuchungen
> left outer join
> (select count(*) as Arbeitstage, Jahr from
> (select DISTINCT Datum,Jahr FROM Untersuchungen) t1 group by Jahr) t2
> on t2.jahr=Untersuchungen.jahr
> group by jahr, bereicheID, PraxisID

Wow! I am really impressed. That's exactly what I was looking for in
just one statement.
Thanks a lot and my compliments.

Regards Uli

"John Hay" <j.haywithoutdot@crbsolutionsremoveallthis.co.uk> wrote in
message news:2921854A-8D82-464B-9FC5-52890BB6B094@news.elevatesoft.com...

Nice very nice.
Rita

John,

<< What about

select PraxisID, BereicheID, Jahr,t2.Arbeitstage, count(*)as Alle,
sum(cast(privat as integer)) as Privat, count(*) -
sum(cast(privat as integer)) as Kasse from Untersuchungen
left outer join
(select count(*) as Arbeitstage, Jahr from
(select DISTINCT Datum,Jahr FROM Untersuchungen) t1 group by Jahr) t2
on t2.jahr=Untersuchungen.jahr
group by jahr, bereicheID, PraxisID >>

I'll have to chime in here also - that is one award-winning SQL statement.


--
Tim Young
Elevate Software
www.elevatesoft.com

James,

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

The result of this will always be 1.

> 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?

It's quite simpe, there are days with >1 entries and day without
entries. You can take any.

Meanwhile we have a very elegant solution by Fred. But thanks a lot for
your efforts.

Regards Uli

John,

great statement, no doubt. But a tiny objection: it is 30% slower than
the 3-step-solution in the  stored procedure I posted.

I know: it is just theoretical, but it would be interesing to know why.

Best regards Uli

Tim


>I'll have to chime in here also - that is one award-winning SQL statement.
>

I have to agree. I hadn't realised just what you could do with the new sub select syntax - probably still haven't but at least my eyes are starting to open.

Roy Lambert

Uli
>...  it is 30% slower than
> the 3-step-solution in the  stored procedure I posted.
>
> I know: it is just theoretical, but it would be interesing to know why.

I agree it is interesting to see why what looks like a very similar
operation produces a large difference in performance.

What are the actual times taken?
What is the rowcount of the resultset?
If you change the left outer join to just join does it make any difference?

Otherwise it is over to Tim

Just as an aside, and it will probably run slower than the first query, you
can use Tim's very excellent correlated subquery to get the result without
an explicit join

select PraxisID, BereicheID, Jahr,
(select count(*) as Arbeitstage from
(select DISTINCT Datum,Jahr FROM Untersuchungen) t1
where Untersuchungen.jahr=jahr
group by Jahr) as Arbeitstage,
count(*)as Alle,
sum(cast(privat as integer)) as Privat, count(*) - sum(cast(privat
as integer)) as Kasse
from Untersuchungen
group by jahr, bereicheID, PraxisID

John