How to retrieve data from previous 4 weeks (mySQL)

user_18286807 0
Viewed 59

I am trying to write a query to get the last 4 weeks (Mon-Sun) of data. I want every week of data to be stored with an individual and shared table. every week data store based on name if same name repeated on single week amt should sum and if multiple name it should be show data individual, To see an example of what I am looking for, I have included the desired input and output below.

this is my table

date amt name
2022-04-29 5 a
2022-04-28 10 b
2022-04-25 11 a
2022-04-23 15 b
2022-04-21 20 b
2022-04-16 20 a
2022-04-11 10 a
2022-04-10 5 b
2022-04-05 5 b

i want output like this

date sum(amt) name
2022-04-25 to 2020-04-29 16 a
2022-04-25 to 2020-04-29 10 b
2022-04-18 to 2022-04-24 35 b
2022-04-11 to 2022-04-17 30 a
2022-04-04 to 2022-04-10 10 b

I would appreciate any pointers or 'best-practises' which I should employ to achieve this task.

mysql sql
2 Answers

You can try to use DATE_ADD with WEEKDAY get week first day and end day.

SELECT 
    CASE WHEN 
         weekofyear(`date`) = weekofyear(NOW())
    THEN 'current week'
    ELSE 
        CONCAT(date_format(DATE_ADD(`date`, interval - WEEKDAY(`date`) day), '%Y-%m-%d'),' to ',date_format(DATE_ADD(DATE_ADD(`date`, interval  -WEEKDAY(`date`) day), interval 6 day), '%Y-%m-%d'))
    END 'date',
    SUM(amt)
FROM T
GROUP BY 
    CASE WHEN 
          weekofyear(`date`) = weekofyear(NOW())
    THEN 'current week'
    ELSE 
        CONCAT(date_format(DATE_ADD(`date`, interval - WEEKDAY(`date`) day), '%Y-%m-%d'),' to ',date_format(DATE_ADD(DATE_ADD(`date`, interval  -WEEKDAY(`date`) day), interval 6 day), '%Y-%m-%d'))
    END

sqlfiddle

EDIT

I saw you edit your question, you can just add name in group by

SELECT 
    CONCAT(date_format(DATE_ADD(`date`, interval - WEEKDAY(`date`) day), '%Y-%m-%d'),' to ',date_format(DATE_ADD(DATE_ADD(`date`, interval  -WEEKDAY(`date`) day), interval 6 day), '%Y-%m-%d')) 'date',
    SUM(amt),
    name
FROM T
GROUP BY 
  CONCAT(date_format(DATE_ADD(`date`, interval - WEEKDAY(`date`) day), '%Y-%m-%d'),' to ',date_format(DATE_ADD(DATE_ADD(`date`, interval  -WEEKDAY(`date`) day), interval 6 day), '%Y-%m-%d')),
  name
ORDER BY 1 desc

sqlfiddle

user_51760710

This is in SQL Server, and just a mess about. Hopefully it can be of some help.

with cteWeekStarts
as
(
    select 
    n,dateadd(week,-n,DATEADD(week, DATEDIFF(week,  -1, getdate()), -1)) as START_DATE
    from
    (values (1),(2),(3),(4)) as t(n)
), cteStartDatesAndEndDates
as
(
    select *,dateadd(day,-1,lead(c.start_date) over (order by c.n desc)) as END_DATE
    from cteWeekStarts as c
) 
,cteSalesSumByDate
as
(
    select s.SalesDate,sum(s.salesvalue) as sum_amt from 
    tblSales as s
    group by s.SalesDate
)
select c3.n as WeekNum,c3.START_DATE,isnull(c3.END_DATE,
dateadd(day,6,c3.start_date)) as END_DATE,
(select sum(c2.sum_amt) from cteSalesSumByDate as c2 where c2.SalesDate 
between c3.START_DATE and c3.END_DATE) as AMT
from cteStartDatesAndEndDates as c3
order by c3.n desc
user_54147980
Related