SQL: using DATEPART and Inner join

Question

I'm getting myself familiar with DATEPART and in this case the week part, to get results per week and so on.

I got it to work with this following query.

var querytest = "SELECT DATEPART(wk, date), sum((kg * rep * sett)) as weight, 
sum(kg / max * rep * sett) as avg, sum((rep * sett)) as reps 
FROM Test WHERE date between @0 and @1 AND exercise < 4 GROUP BY DATEPART(wk, date)";   

However that doesn't really do everything that I want, I also couldn't add date into that query as it gave me error. Like this ...sum((rep * sett)) as reps, date FROM

                                                 ^

date like the above. It gave me errors about something with the grouping at the end.

But I want to use datepart(wk) in the following query, any idea how to do this? (using SQL Server Compact!)

var querythiss = "SELECT DATEPART(wk, date), SUM(kg * rep * sett) as weight, SUM(kg / max * rep * sett) as avg, SUM(rep * sett) as reps, " +
                " t.date, pk.peak FROM Test t INNER JOIN (SELECT MAX(kg / max) as peak, date FROM Test WHERE date BETWEEN @0 AND " +
                " @1 AND exercise < 4 group by date) as pk on t.date = pk.date WHERE t.date BETWEEN @0 AND @1 AND exercise < 4 GROUP " +
                "BY t.date, pk.peak";

Or like this if this is easier to read.

SELECT DATEPART(wk, date),
    SUM(kg * rep * sett) as weight, 
    SUM(kg / max * rep * sett) as avg,   
    SUM(rep * sett) as reps,
    t.date, pk.peak 
FROM Test t INNER JOIN 
(
    SELECT MAX(kg / max) as peak, date 
    FROM Test 
    WHERE date BETWEEN @0 AND @1 
    AND exercise < 4 
    GROUP BY date
) as pk on t.date = pk.date 
WHERE t.date BETWEEN @0 AND @1 AND exercise < 4
GROUP BY t.date, pk.peak

Answer 1

should be similar to this: of course without sample data it's difficult to test, but you can tweak as required.

SELECT pk.dateWeek,
    SUM(kg * rep * sett) as weight, 
    SUM(kg / max * rep * sett) as avg,   
    SUM(rep * sett) as reps,
    pk.peak 
FROM Test t 
INNER JOIN 
(
    SELECT MAX(kg / max) as peak, DATEPART(wk, date) as dateWeek 
    FROM Test 
    WHERE date BETWEEN @0 AND @1 
    AND exercise < 4 
    GROUP BY DATEPART(wk, date)
) as pk on  DATEPART(wk, t.date) = pk.dateWeek 
WHERE date BETWEEN @0 AND @1 
AND exercise < 4
GROUP BY  pk.dateWeek, pk.peak