SeaChange SeaChange - 1 month ago 6
SQL Question

Netezza Grouping by Week Start (Sunday) AND Month Start

I have a little bit of an unusual question. I'm using Python to write some data to a text file that I then use Tableau to read from and build visualizations. I'm grouping the query results by week in order to reduce the size of the output file. I think the SQL is pretty standard for that type of operation.

SELECT [Date] - EXTRACT(DOW FROM [Date]) + 1
[this gives me the Sunday of the week for any date]

However, I occasionally want to group by months rather than weeks, which is impossible with the current output. What I want is a modification to the query which will group by week EXCEPT when a week overlaps two months. If the week overlaps two months, it will split the results into the first part of the week which is in the first month, and then the second part of the week which is in the second month. That way, we could use the output to show weekly result OR monthly/quarterly/yearly results simply by grouping the dates within Tableau.

Has anyone tackled a problem like this before?

As an illustration, consider the following values.

2016-08-21 1
2016-08-22 1
2016-08-23 1
2016-08-24 1
2016-08-25 1
2016-08-26 1
2016-08-27 1
2016-08-28 1
2016-08-29 1
2016-08-30 1
2016-08-31 1
2016-09-01 1
2016-09-02 1
2016-09-03 1
2016-09-04 1
... ...

I would like the code to output the following values:

2016-08-21 7
2016-08-28 4
2016-09-01 3
2016-09-04 1...

Would really appreciate any help!


Based on googled Netzetta syntax, this could work:

min([Date]) as MinDate, count(*) as TotalDays
from YourTable
group by 
extract(month from [Date]), 
 when extract(dow from [Date]) = 1 -- dow 1 is sunday
 then extract(week from [Date]) + 1 -- week starts on monday
 else extract(week from [Date])