Search code examples
pythonsqlsqlitedate

Use sqlite3 to count how many rows exist within each week

I have recently found out my job will pay for car parking as a bonus if you complete 20 jobs in a week. I asked my boss, and he says he will cover that cost retroactively if I can give him a report of each week I did 20 or more jobs. Ive completed over 800 jobs over the course of a year and a half so would very much like to automate this. snapshot of database Included is an image of the SQL database I put together, but I now realise I have no idea where to go from here. Any help greatly appreciated!!!

I initially tried creating a calendar in python, but I also wasn't able to get that to work. Ive been at work for 12 hours today and frankly want to get some sleep, so if there are any kind hearted people able to offer some guidance or recommend a swanky library, that would be a great help.

Solution

  • You could use STRFTIME() function to group by week....

    WITH    --  S a m p l e    D a t a :
  jobs as 
( Select  1 as id, 'Juul' as client, '2024-03-01' as date_completed Union All
  Select  2, 'Tesco', '2024-03-01' Union All
  Select  3, 'One Stop', '2024-03-01' Union All
  Select  4, 'Tesco', '2024-03-03' Union All
  Select  5, 'Juul', '2024-03-04' Union All
  Select  6, 'Tesco', '2024-03-04' Union All
  Select  7, 'One Stop', '2024-03-05' Union All
  Select  8, 'Tesco', '2024-03-06' Union All
  Select  9, 'Juul', '2024-03-06' Union All
  Select 10, 'One Stop', '2024-03-06' Union All
  Select 11, 'Tesco', '2024-03-06' Union All
  Select 12, 'One Stop', '2024-03-07' Union All
  Select 13, 'Tesco', '2024-03-08' Union All
  Select 14, 'Juul', '2024-03-09' Union All
  Select 15, 'One Stop', '2024-03-09' Union All
  Select 16, 'Tesco', '2024-03-10' Union All
  Select 17, 'Juul', '2024-03-11' Union All
  Select 18, 'Tesco', '2024-03-11' Union All
  Select 19, 'One Stop', '2024-03-12' Union All
  Select 20, 'Tesco', '2024-03-13' Union All
  Select 21, 'One Stop', '2024-03-14' Union All
  Select 22, 'Tesco', '2024-03-14' )

    --    S Q L : 
Select   Count(*) as cnt, STRFTIME('%Y-%W', date_completed) as week
From     jobs
Group By week
Having   Count(*) >= 5    -- put 20 instead of 5 here

    /*    R e s u l t :
cnt week
--- --------
 12 2024-10
  6 2024-11    */

    See the fiddle here.