>Hi all,
>
>I have the following table which holds the working hours for each worker and for each workplace:
>
>CREATE TABLE WAGES_HOURS (
>  ID LONGID NOT NULL,  -- Table's Abstract Primary Key
>  WORKERID LONGID NOT NULL, -- The FK for the Worker
>  MONTHID ID NOT NULL, -- The month: 1 - January, 2 - Feb., ..., 12 - Dec.
>  PLACEID LONGID NOT NULL, -- The FK for the Workplace
>  D01 HOURS, -- how many hours worker worked in the workplace in the Day 1. A value of 0 means no work.
>  D02 HOURS, -- the same for the Day 2  ...and so on till the last day of month (D31)
>  D03 HOURS,
…

>  D31 HOURS);

>
>From this data we want to show one or two report(s) for a concrete month (entered in GUI) which will show:
>
>- how many workers worked in each day in each workplace
>- (optional) the sum of hours for each worker in each day in each workplace
>
>I cannot figure how to do it in the best way - till now I have 31 queries which do the grouping for each day - something like this:
>
>select COUNT(workerid), placeid from wages_hours where monthid=:myMonth and d01>0 group by placeid;
>
>...but I feel tha it is possible a better way.
>
>Does someone know it?

CREATE TABLE WAGES_HOURS (

  ID LONGID NOT NULL,  -- Table's Abstract Primary Key

  WORKERID LONGID NOT NULL, -- The FK for the Worker

  DateWorked Date NOT NULL,  

  PLACEID LONGID NOT NULL, -- The FK for the Workplace

  HoursWorked HOURS);

or at least

CREATE TABLE WAGES_HOURS (

  ID LONGID NOT NULL,  -- Table's Abstract Primary Key

  WORKERID LONGID NOT NULL, -- The FK for the Worker

  MONTHID ID NOT NULL, -- The month: 1 - January, 2 - Feb., ..., 12 - Dec.

  PLACEID LONGID NOT NULL, -- The FK for the Workplace

  DayWorked SmallInt –1..31

  HoursWorked HOURS);

rather than having 31 repeating groups.

However, your query above can be replaced by:

WITH TMP (D01, D02, D03, D04, … D31, PlaceID) AS

(SELECT IIF(D01>0, WORKERID, null), IIF(D02>0, WORKERID, null), IIF(D03>0, WORKERID, null), IIF(D04>0, WORKERID, null), … , IIF(D31>0, WORKERID, null), PlaceID

FROM WAGES_HOURS)

SELECT PLACE_ID, COUNT(DISTINCT D01), COUNT(DISTINCT D02), COUNT(DISTINCT D03), COUNT(DISTINCT D04), … , ), COUNT(DISTINCT D31)

FROM TMP

GROUP BY 1

Your optional bit is an entirely different question (or me not understanding it):

SELECT PLACEID, WORKERID, SUM(D01), SUM(D02), SUM(D03), SUM(D04), …, SUM(D31)

FROM WAGES_HOURS

GROUP BY 1, 2

HTH,

Set