I have a complex database, we do repairs like phones laptops etc., and I want to see how many devices does each technician fix a day.
It is usual that more technicians repair one device (someone analyzes it, another one will do the repair, maybe one will do soldering on the logic board etc.). So I have a table "Worksheets" and a related table "Worksheets_Fix", these are related by 'WorksheetNumber' and 'fk_WorksheetNumber'.
The table "Worksheets_Fix" has the following important fields:
Worksheet no. (from the Worksheet)
Timestamp (the current timestamp when someone adds a repairing process)
Type (there are four types: Fixed, Analyzed, Soldered, Other)
Technician (who added the repairing process)
Fixing (this one is '1' for all records)
When someone does a repairing process, it will be shown in a portal on the layout "Worksheets" for each worksheet.
I have a chart with X-axis (it shows distinct dates from table Worksheets within a month which is dynamically sat) and Y-axis with the following calculation:
Let ( [_Row = 1 ;
_Item = GetValue ( Summarytable::GB_Technicians ; _Row ) ] ;
If ( not IsEmpty ( _Item ) ;
" SELECT SUM ( \"Fixing\" )
WHERE \"Date_Yearmonth\" = ? AND \"Technician\" = ?
GROUP BY \"Dátum_Dátum\"
; "" ; "" ; Summarytable::GB_Yearmonth ; _Item )
If technicians would work everyday this could be fine, but there are two days a week they do not work. The result of my SQL statement shows only those dates when there is an existing record with the name of the technician. It would be good to list the other days also with the sum result "0", then my chart would place the data points to the right position.
I attach a photo about my problem. Here I sat the Month to 2017 April, so my chart shows records only from this month. This technician ("A.Zoli") did not work on 2017.04.01., on 2017.04.02., on 2017.04.08 and on 2017.04.09., not as the chart shows.
My other issue is that I do not want to count one device (so one worksheet number) as 2, 3 or more repairing process. (For example if someone analyzes and then repairs the same device under the same worksheet number, it should counted as one fixing not two.)
Thanks for any help.