Hi all ,
I have a table with "Entery_Date" as a date column and I want to group by entries by week number so that I can get how many entries where there in a particular week.
Define "week_number", ISO or other?
Depending on your TD release you might use an existing UDF or create a SQL-UDF.
Or simply join to a calendar table.
What definition of week do you need? Does it start on sunday or monday?
Do you need the actaul week number or just the date of the first day of a week? What rules do you have regarding the starting week number within year?
e.g. this retuns the first day of any week:
datecol - ((datecol - DATE '0001-01-01') MOD 7) -- week starts on monday
datecol - ((datecol - DATE '0001-01-07') MOD 7) -- week starts on sunday
Every calendar includes a week, so just join to it and GROUP BY calendar.week_of_calendar