SQL query for various time periods

Question

enter image description here

I have a table that contains Following entries:

completed_time||   BOOK_CNT
*********************************************
2013-07-23    | 2
2013-07-22    | 1
2013-07-19    | 3
2013-07 16    |5
2013-07-12    |4
2013-07-11    |2
2013-07-02    |9
2013-06-30    |5

Now, I want to use above entries for data analysis.

Lets say DAYS_FROM, DAYS_TO and PERIOD are three variables.

I need to fire following sort of queries:

"Total book from DAYS_FROM to DAYS_TO in interval of PERIOD."

DAYS_FROM is a date in format YYYY-MM-DD

,DAYS_TO is a date in format YYYY-MM-DD

PERIOD is {1W,2W,1M,2M,1Y} where W,M,Y represents WEEK,MONTH and YEAR.

Example: The queries DAYS_FROM=2013-07-23 , DAYS_TO=2013-07-03 and PERIOD=1W should return:

ith week - total
1 -        3
2-         8
3-         6 
4-         14

Explanation:

1-3 means (The total book from 2013-07-21(sun) to 2013-07-23(tue) is 3 )
2-8 means (The total book from 2013-07-14(sun) to 2013-07-21(sun) is 8 )
3-16 means (The total book from 2013-07-07(sun) to 2013-07-14(sun) is 6 )
4-14 means (The total book from 2013-07-03(wed) to 2013-07-07(sun) is 14 )

Please refer the calendar image for better understanding.

How to fire such query?

What I tried?

 SELECT DAY(completed_time), COUNT(total) AS Total
  FROM my_tab
 WHERE completed_time BETWEEN '2013-07-23' - INTERVAL 1 WEEK AND '2013-07-03'
 GROUP BY DAY(completed_time);

The above queries subtracted 7 days from 2013-07-23 and thus considered 2013-07-16 to 2013-07-23 as first week, 2013-07-09 to 2013-07-16 as second week and so on.

possible duplicate of [How to group by week in MySQL?](http://stackoverflow.com/questions/1736010/how-to-group-by-week-in-mysql) — Barmar, Jul 24 '13 at 09:20
It would be better to store the date as timestamp. So you can calculate better with it. — yunzen, Jul 24 '13 at 09:26

Joachim Isaksson · Accepted Answer · 2013-07-24T09:47:32.927

A simple starting point would be something like below, of course you may want to adjust the ith value to suit your needs;

SET @period='1M';

SELECT CASE WHEN @period='1Y' THEN YEAR(completed_time) 
            WHEN @period='1M' THEN YEAR(completed_time)*100+MONTH(completed_time)
            WHEN @period='2M' THEN FLOOR((YEAR(completed_time)*100+MONTH(completed_time))/2)*2
            WHEN @period='1W' THEN YEARWEEK(completed_time)
            WHEN @period='2W' THEN FLOOR(YEARWEEK(completed_time)/2)*2
       END ith,
       SUM(BOOK_CNT) Total
FROM my_tab
GROUP BY ith
ORDER BY ith DESC;

An SQLfiddle to test with.

Thanks a lot!! That's exactly what I wanted. :) – Ritesh Kumar Gupta Jul 24 '13 at 16:48 — Ritesh Kumar Gupta, Jul 24 '13 at 16:48

SQL query for various time periods

1 Answers1