CODEWITHSUNDEEP
mysqlsqlgroup-byreport
codemonkey777
codemonkey777

Reputation: 63

SQL for group by week starting Wednesday

I have a report I want to produce which displays the count of jobs for each week of each year, with dates going back to the early 90s. I have run into a problem I can't seem to solve though as my SQL knowledge isn't that great.

I understand mysql limits you to starting the week dates as either Sunday or Monday. I would like to group them from Wednesday to Tuesday, so when I do the GROUP BY YEAR(JOBID), WEEK(JOBID) it only displays them grouped into weeks starting on Sunday.

I found this on here

SELECT
    count(jobs) as count,
    CASE WHEN (weekday(datetime)<=3) THEN date(datetime + INTERVAL (3-weekday(datetime)) DAY)
        ELSE date(datetime + INTERVAL (3+7-weekday(datetime)) DAY)
    END as dt
FROM values

but it gives me the same result set as 

SELECT 
    COUNT(jobs), 
    Week(datetime) as Week, 
    Year(datetime) as Year 
FROM jobs 
GROUP BY Year(datetime), Week(datetime)

Can someone please point out what I'm doing wrong?

Upvotes: 3

Views: 4218

Answers (2)

Mac
Mac

Reputation: 1201

Your first query is incomplete and doesn't include a "GROUP BY" clause. Be sure to "GROUP BY" the same "CASE" expression that defines your "dt" column.

Your CASE statement is formulated correctly and should return the different result set that you are seeking, as long as your "GROUP BY" clause is written correctly.

I've reformulated your queries as SQL Server queries and confirmed on my own data that they do return different result sets.

Here is a working SQL Server version of your desired query:

SELECT
COUNT([jobs]) AS Count,
CASE
WHEN (DATEPART(dw, [datetime])<=3) THEN Convert(date, DATEADD(day, 3-DATEPART(dw, [datetime]), [datetime]))
ELSE Convert(date, DATEADD(day, 3+7-DATEPART(dw, [datetime]), [datetime]))
END AS WeekLabel
FROM JobsTable
GROUP BY
CASE
WHEN (DATEPART(dw, [datetime])<=3) THEN Convert(date, DATEADD(day, 3-DATEPART(dw, [datetime]), [datetime]))
ELSE Convert(date, DATEADD(day, 3+7-DATEPART(dw, [datetime]), [datetime]))
END
ORDER BY
CASE
WHEN (DATEPART(dw, [datetime])<=3) THEN Convert(date, DATEADD(day, 3-DATEPART(dw, [datetime]), [datetime]))
ELSE Convert(date, DATEADD(day, 3+7-DATEPART(dw, [datetime]), [datetime]))
END DESC

And the SQL Server version of your other comparison query:

SELECT COUNT([jobs]),
DATEPART(week, [datetime]) AS WeekLabel,
DATEPART(year, [datetime]) AS YearLabel
FROM JobsTable
GROUP BY DATEPART(week, [datetime]), DATEPART(year, [datetime])
ORDER BY DATEPART(year, [datetime]) DESC, DATEPART(week, [datetime]) DESC

Upvotes: 1

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

Simplest solution is to use a calendar table. You could easily customize the week usign Wednesday as a start date, and 10 years of data is only approximately 3650 rows.

http://weblogs.sqlteam.com/dang/archive/2010/07/19/calendar-table-and-datetime-functions.aspx

http://web.archive.org/web/20070611150639/http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

Should get you started.

Upvotes: 1

Related Questions