1

I am currently using the query below to collect a total of the ER_read table in between 2 dates. This works fine but I need to split these results into weekly segments e.g. the first week of october = 40 (i think). Is there a function in MsSQL or PHP to do this, and how would I use it?

  SELECT SUM(ER_read) as total
    FROM tblEread
   WHERE ER_ID='$sensor'
     AND MONTH(ER_date) >= '$firstMonth'
     AND MONTH(ER_date) <= '$lastMonth'
     AND YEAR(ER_date) = '$year'
GROUP BY ER_ID;

Thanks for any help

SW4
  • 69,876
  • 20
  • 132
  • 137
Daniel Hutton
  • 1,455
  • 1
  • 17
  • 33

1 Answers1

1

Following tagging from mySQL to MSSQL, see here:

Getting week number off a date in MS SQL Server 2005?

https://stackoverflow.com/search?q=week+number+sql+server

select (datepart(dd,'2008-10-08')-1) / 7 + 1

Community
  • 1
  • 1
SW4
  • 69,876
  • 20
  • 132
  • 137
  • 1
    Thanks for your comment. I've tried using WEEK() and WEEKOFYEAR() but I just get a message saying that those functions don't exist..are they only in MySQL? After some googling I've found a function DATEPART which seems to work so far – Daniel Hutton Oct 10 '11 at 15:28
  • See here, if you havent already: http://stackoverflow.com/questions/348880/getting-week-number-off-a-date-in-ms-sql-server-2005 – SW4 Oct 10 '11 at 15:29
  • Apparently they are. Please tag your question **only** with the database language you are using instead of trying to drum up support for your question with irrelevant tags. Then you won't have this problem. – mellamokb Oct 10 '11 at 15:29