>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?
First, I think your table design could be improved. I think it ought to have been 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
