0

I have found that I have to use DATEPARTif I want to view data in a weekly manner, I'm just not sure how to put my SQL query together.

So far I,ve got this, which is the general idea.

var querythis = "SELECT DATEPART(wk, (kg * sett * rep)) as weeklyvol FROM Test GROUP BY date, kg, sett, rep";

So, kg, sett and rep are 3 of my database columns, also have date which of course is datetime, those 3 are all integers so I want to multiply them to a weekly total.

For example:

24 Jan 2017 - 100 x 10 x 3 = 3000
25 Jan 2017 - 100 x 5 x 5 = 2500
26 Jan 2017 - 150 x 3 x 3 = 1350

Which would result in Week 4 = 6850

GROUP BY date, kg, sett, rep";

Im not actually sure what the above row^ part of the code does, I had to include all of them or I'd get errors.

Currently its error free when I write it out like the following but it does not give me the result I want, I dont even know what the numbers come from, but at least not correct.

foreach (var c in db.Query(querythis))
{
    <a>@c.weeklyvol</a><br />
}

Anyone know how to do this?

I'm using SQL Server Compact if that makes a difference.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Pontus Svedberg
  • 305
  • 1
  • 7
  • 24

1 Answers1

1

You're looking for something like this:

SELECT 
  DATEPART(wk, date),
  sum(kg * sett * rep) as weeklyvol 
FROM 
  Test 
GROUP BY 
  DATEPART(wk, date)

This will take the week from your date, and group by with it and also show the volume sum as the second column. You might want to use isowk instead of wk in datepart.

If you need also the year, using datepart(year... does not work properly, see for example this link. Without it dates on week 52, 53 and 1 can end up having wrong year number.

Community
  • 1
  • 1
James Z
  • 12,209
  • 10
  • 24
  • 44
  • Cool, that definitly did the trick! However, I see that when it changes year it only orders by week number and not date, is this something I can solve? i.e. going from week 52 2016 to week 1 2017 it puts the week 1 result before week 52! – Pontus Svedberg Jan 29 '17 at 18:30
  • and yes, `isowk` seems to be recommended in my country! – Pontus Svedberg Jan 29 '17 at 18:33
  • You can add another column for the year, and add it to the group by. The biggest problem is that there is no functionality for fetching the correct year for iso weeks, because year isn't always correct for week 52/53/1. There are [examples](http://stackoverflow.com/questions/22829604/what-is-iso-year-in-sql-server) how to create function for that. – James Z Jan 29 '17 at 18:35
  • Alright! I'll look into that! Thank you for your help! – Pontus Svedberg Jan 29 '17 at 18:38