CODEWITHSUNDEEP
sqlsql-serverdatepart
Pontus Svedberg
Pontus Svedberg

Reputation: 305

SQL Datepart (week), ORDER BY year?

I use this following query here to get my data and show the value weekly, however, when there is a new year and the result will show week 50, 51, 52 of 2016 and week 1, 2, 3 of 2017 for example, it will order by week number resulting in 1,2,3,50,51,52 which means that the 2017 results appear before 2016, how do I change this?

SELECT 
    DATEPART(wk, date) AS number,
    SUM((kg * rep * sett)) AS weight,
    SUM(kg / max * rep * sett) AS avg,
    SUM((rep * sett)) AS reps,
    MAX(kg / max) AS peak
FROM
    Test
WHERE
    date BETWEEN @0 AND @1 AND exercise < 4
GROUP BY DATEPART(wk, date)

Upvotes: 0

Views: 3005

Answers (2)

3N1GM4
3N1GM4

Reputation: 3351

Perhaps not the most elegant solution, but you could just include the year in the field you group by:

SELECT 
    CONVERT(VARCHAR,DATEPART(year, date)) + '_' + CONVERT(VARCHAR,DATEPART(wk, date)) AS Year_Week,
    SUM((kg * rep * sett)) AS weight,
    SUM(kg / max * rep * sett) AS avg,
    SUM((rep * sett)) AS reps,
    MAX(kg / max) AS peak
FROM
    Test
WHERE
    date BETWEEN @0 AND @1 AND exercise < 4
GROUP BY CONVERT(VARCHAR,DATEPART(year, date)) + '_' + CONVERT(VARCHAR,DATEPART(wk, date))
ORDER BY CONVERT(VARCHAR,DATEPART(year, date)) + '_' + CONVERT(VARCHAR,DATEPART(wk, date))

or (probably better), just treat them as entirely separate fields:

SELECT 
    DATEPART(year, date) AS Year,
    DATEPART(wk, date) AS Week,
    SUM((kg * rep * sett)) AS weight,
    SUM(kg / max * rep * sett) AS avg,
    SUM((rep * sett)) AS reps,
    MAX(kg / max) AS peak
FROM
    Test
WHERE
    date BETWEEN @0 AND @1 AND exercise < 4
GROUP BY DATEPART(year, date), DATEPART(wk, date)
ORDER BY DATEPART(year, date), DATEPART(wk, date)

Upvotes: 5

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

You could add an order by on aggregate of year part:

SELECT 
    DATEPART(wk, date) AS number,
    SUM((kg * rep * sett)) AS weight,
    SUM(kg / max * rep * sett) AS avg,
    SUM((rep * sett)) AS reps,
    MAX(kg / max) AS peak
FROM
    Test
WHERE
    date BETWEEN @0 AND @1 AND exercise < 4
GROUP BY DATEPART(wk, date)
order by max(datepart(year,date)), number;

The above however aggregate together data from same week number, even if they fall in different year (Probably not what you want). If you want to keep the different years' data separate, you can for example add an year column to your output.

SELECT 
    datepart(year,date) as year,
    DATEPART(wk, date) AS number,
    SUM((kg * rep * sett)) AS weight,
    SUM(kg / max * rep * sett) AS avg,
    SUM((rep * sett)) AS reps,
    MAX(kg / max) AS peak
FROM
    Test
WHERE
    date BETWEEN @0 AND @1 AND exercise < 4
GROUP BY datepart(year,date), DATEPART(wk, date)
order by year, number;

Upvotes: 0

Related Questions