0

I need a query that will return the date of every Monday between two dates. It is intended to be in the basis of another query that counts weekly transactions (Monday till Sunday), and it should be able to represent weeks without any transaction.

This means it can't rely on the existing data in the transactions table, because there is no representation there to weeks without any transactions.

For example: for the 3 weeks starting on Monday - July 21, 2014 - I need a query that returns the following:

2014-07-21

2014-07-28

2014-08-04

Assuming my transactions were logged on the following dates:

2014-07-22

2014-07-23

2014-07-25

2014-08-05

I will want to write a query that returns the aggregated number of transaction per week:

2014-07-21 => 3

2014-07-28 => 0

2014-08-04 => 1

And that's why I can't rely on the data itself, and need a query to generate every Monday between two given dates. Any suggestions?

Community
  • 1
  • 1
Aamit
  • 181
  • 4
  • 16
  • 1
    I'm having trouble wrapping my head around what you are trying to do. (If I understand correctly it seems like something you should do outside of MySQL, with whatever program you are using) – serakfalcon Aug 07 '14 at 15:59
  • Of course, if one wanted to do this MySQL, the overhead of constructing a table with all probable dates would be modest. – Strawberry Aug 07 '14 at 16:07
  • It needs to be done on MySQL, I want to achieve that without constructing such a table... – Aamit Aug 07 '14 at 16:16
  • well, to be honest - because I'm sure it can be done and I'm too curious to find out how. Can't be there's no way to do that. Also, where I work I have no easy access to influencing the DB, only querying it... – Aamit Aug 07 '14 at 16:24
  • 2
    MySQL is for the storage and retrieval of data. You cannot (easily) retrieve what isn't there. You can fake a series using tricks like utility tables and/or UNIONs but these *are* all tricks. – Strawberry Aug 07 '14 at 16:26

4 Answers4

0

So, I got part of this to work.. its not populating an empty week. but this gets the week that each occurrence happens to group by. I would not recommend you do all of this in MySQL.. you should do it in another programming language

SETUP:

create table time_date (id int, date_part date);
insert into time_date values
(1, '2014-07-22'),
(2, '2014-07-23'),
(3, '2014-07-25'),
(4, '2014-08-05');

QUERY:

SELECT 
    FROM_DAYS(TO_DAYS(date_part) - MOD(TO_DAYS(date_part) -2, 7)) as 'Week'
  , COUNT(*) as 'Num Per Week' 
FROM time_date
GROUP BY FROM_DAYS(TO_DAYS(date_part) - MOD(TO_DAYS(date_part) -2, 7)) ;

OUTPUT:

   Week     Num Per Week
2014-07-21       3
2014-08-04       1

from here you should do the rest in another programming language. build a list of weeks... and then compare.. put in a 0 and the week if its not returned from this query

DEMO

Community
  • 1
  • 1
John Ruddell
  • 25,283
  • 6
  • 57
  • 86
0

OK so, if you only need the next 3 weeks, and we don't worry about what day of the week it is exactly, and you don't want to make another table, this query will solve your problem.

SET @startdate = '2014-07-21';
SET @seconddate = DATE_ADD(@startdate,INTERVAL 7 DAY);
SET @thirddate = DATE_ADD(@seconddate,INTERVAL 7 DAY);

SELECT count(id),@startdate as week FROM transactions 
WHERE transdate BETWEEN @startdate AND @seconddate
UNION 
SELECT count(id),@seconddate as week from transactions
WHERE transdate BETWEEN @seconddate AND @thirddate
UNION
SELECT count(id),@thirddate as week FROM transactions
WHERE transdate BETWEEN @thirddate AND DATE_ADD(@thirddate,INTERVAL 7 DAY)

IF you use another table, you can get it to work with this query (which also has the added advantage of being more adjustable and probably faster)

SELECT count(transactions.id), weekstarts.start
FROM weekstarts LEFT JOIN transactions ON
transactions.transdate 
BETWEEN weekstarts.start AND DATE_ADD(weekstarts.start,INTERVAL 7 DAY)
WHERE weekstarts.start BETWEEN '2014-07-21' AND DATE_ADD('2014-07-21',INTERVAL 14 DAY)
GROUP BY weekstarts.start
serakfalcon
  • 3,501
  • 1
  • 22
  • 33
0

Found it! Inspired by a reply on this thread - The following query returns (out of thin air!) every Monday between 2 dates:

select  * 
  from (
    select  date_add('2010-01-01', INTERVAL n4.num*1000+n3.num*100+n2.num*10+n1.num DAY ) as date 
      from  (select 0 as num
               union all select 1
               union all select 2
               union all select 3
               union all select 4
               union all select 5
               union all select 6
               union all select 7
               union all select 8
               union all select 9) n1,
            (select 0 as num
               union all select 1
               union all select 2
               union all select 3
               union all select 4
               union all select 5
               union all select 6
               union all select 7
               union all select 8
               union all select 9) n2,
            (select 0 as num
               union all select 1
               union all select 2
               union all select 3
               union all select 4
               union all select 5
               union all select 6
               union all select 7
               union all select 8
               union all select 9) n3,
            (select 0 as num
               union all select 1
               union all select 2
               union all select 3
               union all select 4
               union all select 5
               union all select 6
               union all select 7
               union all select 8
               union all select 9) n4
            ) a
where date >= '2014-07-21' and date < NOW()
  and weekday(date) = 0
order by date
Community
  • 1
  • 1
Aamit
  • 181
  • 4
  • 16
-1

Below query gives you the answer -

create table transactions
 ( id integer,
 trans_date date
 );

select DATE_ADD(trans_date, INTERVAL(-WEEKDAY(trans_date)) DAY) 
Week_start_date,count(*) total_transactions from transactions
group by 1;
Karthiknarayanan
  • 24
  • 2