sql group by season

Question

I`m having a problem using 'group by' and 'select-case-when' in SQL.

I have this table: Promotion (Name, CodProd, CodProdSimilar, StartDate, EndDate, Discount) and what I need to do is: know in what seasons is a promotion active.

For example:

<table style="width:100%">
  <tr>
    <td>Name</td>
    <td>StartDate(dd/mm/yyyy)</td> 
    <td>EndDate(dd/mm/yyyy)</td>
    <td>Season (southern hemisphere)</td>
  </tr>
  <tr>
    <td>Prom1 </td>
    <td>02/01/2015 </td> 
    <td>09/01/2015</td>
    <td>Summer</td>
  </tr>
    <tr>
    <td>Prom2 </td>
    <td>02/01/2015 </td> 
    <td>09/04/2015</td>
    <td>Summer</td>
  </tr>
    <tr>
    <td>Prom2 </td>
    <td>02/01/2015 </td> 
    <td>09/04/2015</td>
    <td>Autumn</td>
  </tr>
</table>

this is easy to solve when a promotion is in only one season, but I cant do it yet for the case of Prom2.

My code:

select 
    Name, StartDate, EndDate,
    CASE
       WHEN EXTRACT(MONTH from StartDate) < 3 THEN 'Summer'
       WHEN EXTRACT(MONTH from StartDate) = 3 THEN
            CASE 
               WHEN EXTRACT(DAY from StartDate) <= 20 THEN 'Summer' 
               ELSE 'Autumn' 
            END
       WHEN EXTRACT(MONTH from StartDate) < 6 THEN 'Autumn'
       WHEN EXTRACT(MONTH from StartDate) = 6 THEN
            CASE 
               WHEN EXTRACT(DAY from StartDate) <= 20 THEN 'Autumn' 
               ELSE 'Winter' 
            END
       WHEN EXTRACT(MONTH from StartDate) < 9 THEN 'Winter'
       WHEN EXTRACT(MONTH from StartDate) = 9 THEN
            CASE 
               WHEN EXTRACT(DAY from StartDate) <= 20 THEN 'Winter' 
               ELSE 'Spring' 
            END
       WHEN EXTRACT(MONTH from StartDate)< 12 THEN 'Spring'
       WHEN EXTRACT(MONTH from StartDate) = 12 THEN
            CASE 
               WHEN EXTRACT(DAY from StartDate) <= 20 THEN 'Spring' 
               ELSE 'Summer' 
            END
    END as season
from 
    promotion

Any idea?

Thanks a lot for your time!

Which RDBMS is this for? Please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. — marc_s, Nov 02 '15 at 18:21

score 0 · Accepted Answer · edited May 23 '17 at 12:06

For your case , you can convert column into string, using month & date we can determine season as you mentioned.

select Name,StartDate,EndDate,
case 
            when to_char(StartDate,'MMDD') between '0321' and '0620' then 'spring'
            when to_char(StartDate,'MMDD') between '0621' and '0922' then 'summer'
            when to_char(StartDate,'MMDD') between '0923' and '1220' then 'fall'
            else                                                        'winter'
        end as season
FROM promotion

Please refer this , you will get more information

https://wiki.postgresql.org/wiki/Date_and_Time_dimensions

PostgreSQL group by season (northern and southern hemisphere)

sql group by season

1 Answers1