Date period spread among seasons

Question

In my MySQL database table I have the following date periods (seasons):

sstart,send,sname
2013-05-01,2013-05-31,'season1'
2013-06-01,2013-06-30,'season2'

I'd like to create SQL query which will for given start and end date split this period among seasons with info about duration of each part of period:

For example for the following period (qstart,qend) 2013-04-20,2013-06-10 this query should return:

2013-04-20,2013-04-30,'',11           <-- this is important - I need it
2013-05-01,2013-05-31,'season1',31    <-- 31 days of my period is in season1
2013-06-01,2013-06-10,'season2',10    <-- 10 days of my period is in season2

I have the following SQL query:

SELECT sid,sname,DATEDIFF(IF(send>@qend,@qend,send),IF(sstart<@qstart,@qstart,sstart))+1
               FROM seasons WHERE sstart<=@qend AND send>=@qstart

My problem is when my period (qstart,qend) starts before first season or ends after the last season in database, this query does not return these tails which I need.

score 0 · Accepted Answer · answered Jun 12 '13 at 13:53

0

I don't have mySQL, but this should hopefully point you in the right direction:

Instead of the Seasons table in your query, using the following:

   FROM 
   ( 
     select * from seasons 
     union
     select '' as [sname],'1/1/1980' as [sStart],DateAdd(dd,-1,MIN([sStart])) as [send]
     from Seasons
     union
     select '' as [sname],DateAdd(dd,+1,MAX([send])) as [sstart],'12/31/2021' as [Send]
     from Seasons
    ) Seasons

Basically, adding two "dummy rows" to represent seasons before the first date and after the last date...

answered Jun 12 '13 at 13:53

Sparky

14,967
2
31
45

Thanks man for such a quick response. I read about this solution with adding dummy rows (and union them) somewhere but thought this was dirty solution. If nobody suggest anything better I'll have to take it as a only solution. – sbrbot Jun 12 '13 at 14:20
Why a dirty solution? Sure, you could add the dummy rows to your table if you'll call this a lot of times, but I imagine your seasons table is very small, so the union statement I've illustrated would not take long to run at all.... You should also use UNION ALL instead of UNION since the new rows will never duplicate existing rows... – Sparky Jun 12 '13 at 14:25
Thanks Sparky, your suggestion was helpful. – sbrbot Jun 12 '13 at 14:40

sbrbot · Answer 2 · 2013-06-12T15:53:27.757

For others, according to Sparky's suggestion here is my query:

SELECT sid,sstart,send,sname,DATEDIFF(IF(send>@lDay,@lDay,send),IF(sstart<@fDay,@fDay,sstart))+1 AS duration
FROM
(
  SELECT 0 AS sid,'undefined' AS sname,'1980-01-01' AS sstart,SUBDATE(MIN(sstart),1) AS send FROM seasons
    UNION
  SELECT sid,sname,sstart,send FROM seasons
    UNION
  SELECT 0 AS sid,'undefined' AS sname,ADDDATE(MAX(send),1) AS sstart,'2021-12-31' AS send FROM seasons
) AS seasons
WHERE sstart<=@lDay AND send>=@fDay
ORDER BY sstart

Date period spread among seasons

2 Answers2