Query and total for each week

Question

user, timeon, timeoff, hours 
Monday - 
1510, 2021-10-25 05:00:00, 2021-10-25 09:00:00 - 
1510, 2021-10-25 14:00:00, 2021-10-25 17:00:00 -

The total should be 4 + 3 = 7

Tuesday - 
1510, 2021-10-25 05:00:00, 2021-10-25 09:00:00 - 
1510, 2021-10-25 14:00:00, 2021-10-25 17:00:00 -

The total should be 4 + 3 = 7, then total at the end of the week

I have a user, timeon, timeoff for each day as they might be on and off more than once. I have a total at the bottom datediff(minute, timeon, timeoff). I'm using Firebird 3.

I have a virtual table

with M as
(
    select user, timeon, timeoff, hours
    from (select userid, timeon, timeoff,
    datediff(minute,timeon, timeoff)/60.00 as hours
    from sessions s
    where extract(weekday from timeon)=1
    and userID=1
    and cast(logon as date)='2021-10-28')
)
select * from M
union all
select '','','',sum(hours) from M

Now, I need to repeat the above but for a Tuesday, then Wednesday and so on. I can't start with another v table nor a union. What am I doing wrong?

Please provide a [mre], including example data and expected results for that sample data. — Mark Rotteveel, Oct 28 '21 at 18:38
Also, why can't you remove the `extract(weekday from timeon)=1` condition, so you have it for all days? — Mark Rotteveel, Oct 28 '21 at 18:40
Usual SQL solution would be to use ROLLUP or GROUPING SETS, which apparently are not available in Firebird :-( https://firebirdsql.org/refdocs/langrefupd15-select.html — Fabian Pijcke, Oct 28 '21 at 18:43
I have the extract as I need the total hours for each day of the week — L_GIB, Oct 28 '21 at 19:31
user, timeon, timeoff, hours Monday - 1510, 2021-10-25 05:00:00, 2021-10-25 09:00:00 - 1510, 2021-10-25 14:00:00, 2021-10-25 17:00:00 - the total should be 4 + 3 = 7 then Tuesday: Tuesday - 1510, 2021-10-25 05:00:00, 2021-10-25 09:00:00 - 1510, 2021-10-25 14:00:00, 2021-10-25 17:00:00 - the total should be 4 + 3 = 7 — L_GIB, Oct 28 '21 at 19:48
@FabianPijcke Firebird still doesn't have them, but you're linking to the Firebird 1.5 Language Reference Update, more up-to-date and more complete information is available in the [Firebird 3.0 Language Reference](https://www.firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref30/fblangref30-dml.html#fblangref30-dml-select) — Mark Rotteveel, Oct 29 '21 at 07:13
@LeoFazzi Please [edit] your question with details, don't add them in the comments. — Mark Rotteveel, Oct 29 '21 at 07:16

Thorsten Kettner · Accepted Answer · 2021-10-30T09:53:29.493

1

As has been mentioned in the request comments, we would usually use ROLLUP or GROUPING SETS, but Firebird doesn't support these.

Using a WITH clause instead is a good idea. Here is a query with the sub totals and a final total:

with s as
(
  select
    extract(weekday from timeon) as day,
    userid, timeon, timeoff,
    datediff(minute, timeon, timeoff) / 60.00 as hours
  from sessions
  -- where userid = 1
)
select * from s
union all
select day, userid, null, null, sum(hours) from s group by day, userid
union all
select day, null, null, null, sum(hours) from s group by day
union all
select null, null, null , null, sum(hours) from s
order by day nulls last, userid nulls last, timeon nulls last;

This is not precise, as we would count a session from 10 pm to 2 am as four hours on the starting day, rather than 2 hours the starting day and 2 hours the following day. But you did the same in your query, so I guess this is fine with you.

edited Oct 30 '21 at 09:53

answered Oct 29 '21 at 18:32

Thorsten Kettner

89,309
7
49
73

thank you. This works, although I get results at the end. - I would have love to have the total after each day and then a week total at the bottom. The way I tried was to create a virtual table for each and then a union.. but failed. I thank you for what you wrote as indeed useful . – L_GIB Oct 30 '21 at 07:32
Sorry, I forgot to put the `timeon` in the `ORDER BY` clause. Thus the users' totals were mixed in with the raw session rows. I've fixed this. The day totals though should already come after the day rows, because we order by day and then user_id putting nulls last. So all results for a day are shown in one block with the total coming last, because its user_id is null. Please check again. – Thorsten Kettner Oct 30 '21 at 09:56

Query and total for each week

1 Answers1