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.
CodePudding user response:
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
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
CodePudding user response:
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