title: SQL how to select data in terms of weeks
body: <p>Is it in any way possible to select data from the database in spans of weeks? 
  I use cshtml (not MVC) and webmatrix if that makes any difference. </p>
  
  <pre><code>var dbQueryAllVariants = "SELECT * FROM Test WHERE exercise = " + exercise + " AND date &gt;= '" + fromDate + "' AND date &lt;= '" + toDate + "'";
  </code></pre>
  
  <p>So right now I'm using this, I put in a start date (ex. 2016-11-01) and end date (ex. 2016-11-30) (yyyy-mm-dd cuz north europe). This displays all the data in the database between those dates but since all rows in the database only have a day as date, the result to be viewed would be in day form, I'd like if it can do weeks, in this case, from first to last november as an example would be aprox 4 weeks, is this possible? Also, the data in the database is int values so I would like to be able to add those up to display a total of the week that gets displayed if that makes sense. </p>
  
  <p>For example.</p>
  
  <pre><code>column 1  column 2 column 3
  
  5 . . . . 6 . .    2016-11-13
  
  8 . . . . 10 . .   2016-11-15
  
  6 . . . . 3 . .    2016-11-17
  </code></pre>
  
  <p>So as of right now it would display 3 days with a sum of 11 for day 1, 18 for day 2 and 9 for day 3, but while displayed in terms of weeks it would be 11+18+9=38, as for an example. This might not even be possible to begin with but I'd like to know how to do this if possible! </p>
  
  <p>If this isn't a possible solution, is there a way to like select all the data in day form, put it in a array or whatever, and from there send it grouped as a weekly total based on the weeks of the year (ex. november contains week 44-48) something like that? What I'm trying to say is that if the end result is what I want, it doesn't matter how its done.</p>
  
  <pre><code>@{
  //Calls for my website layout.
  Layout = "~/_SiteLayout.cshtml";
  //Browser title of the specific page.
  Page.Title = "TEST";
  
  //Opens database.
  var db = Database.Open("SmallBakery");
  
  //Variables.
  var exercise = Request.Form["Exercise"];
  var fromDate = Request.Form["fromDate"];
  var toDate = Request.Form["toDate"];
  var exerVariName = "";
  var exerVariNameS = "";
  var exerVariNameB = "";
  var exerVariNameD = "";
  //Defaults to show data between these 
  //dates if user dont choose any dates.
  var noStartDate = "1970-01/01";
  var noEndDate = "2099-12/31";
  
  
  //If user does not choose eiter/any start/end date
  //this will end up showing all results possible. 
  if (fromDate == "") {
      fromDate = noStartDate;
  }
  if (toDate == "") {
      toDate = noEndDate;
  }
  
  //Takes exerVariName from different dropdowns
  //depending on which exercise is selected due to
  //the fact that only one dropdown is visible at any time.
  if (exercise == "1") {
      exerVariName = Request.Form["exerVariNameS"];
  } else if (exercise == "2") {
      exerVariName = Request.Form["exerVariNameB"];
  } else {
      exerVariName = Request.Form["exerVariNameD"];
  }
  
  //Gets exercise variants to the dropdown menu.
  var getSVariName = "SELECT * FROM exerciseVariants WHERE exerVariNameID = 1 ORDER BY exerVariName";
  var getBVariName = "SELECT * FROM exerciseVariants WHERE exerVariNameID = 2 ORDER BY exerVariName";
  var getDVariName = "SELECT * FROM exerciseVariants WHERE exerVariNameID = 3 ORDER BY exerVariName";
  var getData = "SELECT * FROM Test";
  
  //Gets the date. 
  var getDate = "SELECT date FROM Test";
  }
  &lt;!DOCTYPE html&gt;
  &lt;html lang="en"&gt;
  &lt;head&gt;
      &lt;meta charset="utf-8" /&gt;
      &lt;title&gt;&lt;/title&gt;
      &lt;link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css"&gt;
      &lt;script src="https://code.jquery.com/jquery-1.12.4.js"&gt;&lt;/script&gt;
      &lt;script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"&gt;&lt;/script&gt;
  &lt;/head&gt;
  &lt;body&gt;
      &lt;!-- Form for posting. --&gt;
      &lt;form method="post" action=""&gt;
          &lt;!-- Radio buttons to select which data to show. --&gt;
          &lt;div&gt;
              &lt;label&gt;Squat&lt;/label&gt;
              &lt;input type="radio" name="Exercise" id="hej1" value="1" /&gt;
          &lt;/div&gt;
          &lt;div&gt;
              &lt;label&gt;Benchpress&lt;/label&gt;
              &lt;input type="radio" name="Exercise" id="hej2" value="2" /&gt;
          &lt;/div&gt;
          &lt;div&gt;
              &lt;label&gt;Deadlift&lt;/label&gt;
              &lt;input type="radio" name="Exercise" id="hej3" value="3" /&gt;
          &lt;/div&gt;
          &lt;div&gt;
              &lt;!-- Dropdown menu with squat-variant-names. --&gt;
              &lt;select id="exerVariNameS" name="exerVariNameS"&gt;
                  &lt;option value="all"&gt;All&lt;/option&gt;
                  &lt;option value="Comp"&gt;Competition&lt;/option&gt;
                  @foreach (var get in db.Query(getSVariName)) {
                      //Gets the exercise variation names from 
                      //the database and puts them in a dropdown.
                      &lt;option value="@get.exerVariName"&gt;@get.exerVariName&lt;/option&gt; 
                  }
              &lt;/select&gt;
              &lt;!-- Dropdown menu with bench-variant-names. --&gt;
              &lt;select id="exerVariNameB" name="exerVariNameB"&gt;
                  &lt;option value="all"&gt;All&lt;/option&gt;
                  &lt;option value="Comp"&gt;Competition&lt;/option&gt;
                  @foreach (var get in db.Query(getBVariName)) {
                      //Gets the exercise variation names from 
                      //the database and puts them in a dropdown.
                      &lt;option value="@get.exerVariName"&gt;@get.exerVariName&lt;/option&gt; 
                  }
              &lt;/select&gt; 
              &lt;!-- Dropdown menu with deadlift-variant-names. --&gt;               
              &lt;select id="exerVariNameD" name="exerVariNameD"&gt;
                  &lt;option value="all"&gt;All&lt;/option&gt;
                  &lt;option value="Comp"&gt;Competition&lt;/option&gt;
                  @foreach (var get in db.Query(getDVariName)) {
                      //Gets the exercise variation names from 
                      //the database and puts them in a dropdown.
                      &lt;option value="@get.exerVariName"&gt;@get.exerVariName&lt;/option&gt; 
                  }
              &lt;/select&gt;
          &lt;/div&gt;
          &lt;div&gt;
              &lt;!-- Date calendar. --&gt;
              &lt;input placeholder="From date..." type="text" class="datepicker" name="fromDate" value="@fromDate"&gt;
          &lt;/div&gt;
          &lt;div&gt;
              &lt;!-- Date calendar. --&gt;
              &lt;input placeholder="To date..." type="text" class="datepicker" name="toDate" value="@toDate"&gt;
          &lt;/div&gt;
          &lt;!-- The submit button. --&gt;
          &lt;input type="submit" value="Show" class="submit" /&gt;
      &lt;/form&gt;
  
      &lt;!-- Displays database value on submit click based on choosen radiobutton from form-post above. --&gt;
      @if (IsPost) {  
          //When I select ALL in the dropdown it runs 
          //this line because there is no filter for 'exerVariName'.
     //     var dbQueryAllVariants = "SELECT * FROM Test WHERE exercise = " + exercise + " AND date &gt;= '" + fromDate + "' AND date &lt;= '" + toDate + "'";
          //When I select a specific exercise variation.
          var dbQuerySingleVariant = "SELECT * FROM Test WHERE exercise = " + exercise + " AND exerVariName = '" + exerVariName + "' AND date &gt;= '" + fromDate + "' AND date &lt;= '" + toDate + "'";
  
  
          //This is what the problem is....
          var dbQueryAllVariants = "SELECT DATEPART(week, date) AS weekNumber, sum(kg)+sum(sett) AS grandTotalPerWeek FROM Test WHERE Exercise = " + exercise + " AND DATEPART(week, date) BETWEEN DATEPART(week, " + fromDate + ") AND DATEPART(week, " + toDate + ") GROUP BY DATEPART(week, date)";
  
  
          var dbQuery = "";
          //If dropdown = select all, it does, else, it show the one I pick. 
          if  (exerVariName == "all") {
              dbQuery = dbQueryAllVariants;
          } else {
              dbQuery = dbQuerySingleVariant;
          }
          //Foreach to write out all the data from db.
          var sumTotalWeight = 0;
          foreach (var get in db.Query(dbQuery)) {
              &lt;a&gt;Weight: &lt;/a&gt;
              &lt;a&gt;@get.Kg kg&lt;/a&gt; 
              &lt;a&gt; Sets: &lt;/a&gt;
              &lt;a&gt;@get.Sett&lt;/a&gt;
              &lt;a&gt; Reps: &lt;/a&gt; 
              &lt;a&gt;@get.Rep&lt;/a&gt;
              &lt;a&gt; Total reps: &lt;/a&gt;@(get.Sett * get.Rep)
              &lt;a&gt;    @get.date&lt;/a&gt;
  
              var totalWeight = @get.Kg * @get.Sett * @get.Rep;
              sumTotalWeight += totalWeight;
              &lt;a&gt; @totalWeight&lt;/a&gt;
              &lt;br&gt;
          }
        @sumTotalWeight                         
      }
  &lt;/body&gt;
  </code></pre>
  
  <p></p>