[RESOLVED] How to design this query for monthly report?

[IkkeEnGij]

Why are you creating a problem where there is none
both shifts start on the same day,so why not count their production as produced on that day
(what you already seem to be doing judging by your Type DailyProduce)

but if you want to hairsplit:
just have the nightshift make 2 entrys(records) in the table on the last day of the month
(1 for the last day and 1 for the first day)

[Zvoni]

Why are you creating a problem where there is none
both shifts start on the same day,so why not count their production as produced on that day
(what you already seem to be doing judging by your Type DailyProduce)

His Problem is, that he only has the "ending datetime" of the Shift. See my post about adding a column "starting datetime" for a shift.

What i don't understand: Why does the nightshift count to the day prior, when 58% of the worktime belongs to the current day (5 hours for day prior, 7 hours for current day)?
The only explanation (at least to me): The beginning of the shift determines to which day (resp. month) the produce belongs to.

[IkkeEnGij]

His Problem is, that he only has the "ending datetime" of the Shift. See my post about adding a column "starting datetime" for a shift.

What i don't understand: Why does the nightshift count to the day prior, when 58% of the worktime belongs to the current day (5 hours for day prior, 7 hours for current day)?
The only explanation (at least to me): The beginning of the shift determines to which day (resp. month) the produce belongs to.

exactly, that is why s/he is creating a problem, where there is none
it is as simple as pie:
both shifts start at the same day
so why not make that day the production day of both shifts ?
and the table only needs 3 fields: Date (not datetime),DayProduction,NightProduction

DaveDavis, is there any reason to not use such a simple system ?

[DaveDavis]

exactly, that is why s/he is creating a problem, where there is none
it is as simple as pie:
both shifts start at the same day
so why not make that day the production day of both shifts ?
and the table only needs 3 fields: Date (not datetime),DayProduction,NightProduction

DaveDavis, is there any reason to not use such a simple system ?

The data is coming from 3rd software, I can't control.

@oumba has given a good idea by minus 7 hours.

Thanks dilettante and PGBSoft for query design.