0

I want to fetch some data from DB by giving multiple date ranges. Example,in February I want to get weekly report from a table in this order Feb 01 to 07, Feb 07 to 14, Feb 14 to 21, Feb 21 to 28 and Feb 28 to Mar 01. In DB the records are stored in a daily wise not in weekly wise. I want to cluster it as weekly wise and calculate sum then show the result. Please help me if you know this case.

For clear cut view, consider 3 tables & its columns. 

Table A:id,timestamp (comment-data is inserted daily)   
Table B:id,fruits  
Table C:id,fruits_type

Result: 

fruits_type count(id)   timestamp 
apple   3   01-02-2016 to 07-02-2016 
orange  5   01-02-2016 to 07-02-2016 
pineapple   8   01-02-2016 to 07-02-2016 
apple   4   07-02-2016 to 14-02-2016 
orange  5   07-02-2016 to 14-02-2016

Conditions:id should match among 3 tables;fetch data by providing group by fruits_type and timestamp should be in weekly wise.

Please help if you know this

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
radhik
  • 51
  • 1
  • 7
  • Please edit your question with the relevant table(s) structure, some sample data and desired result. – Zohar Peled Mar 03 '16 at 15:57
  • Are you interested in a general solution for a weekly calculation or just for the specific dates? – tobypls Mar 03 '16 at 16:06
  • For clear cut view, consider 3 tables & its columns. Table A:id,timestamp (comment-data is inserted daily) Table B:id,fruits Table C:id,fruits_type Result: fruits_type count(id) timestamp apple 3 01-02-2016 to 07-02-2016 orange 5 01-02-2016 to 07-02-2016 pineapple 8 01-02-2016 to 07-02-2016 apple 4 07-02-2016 to 14-02-2016 orange 5 07-02-2016 to 14-02-2016 Conditions:id should match among 3 tables;fetch data by providing group by fruits_type and timestamp should be in weekly wise. Please help if you know this. – radhik Mar 03 '16 at 19:01

1 Answers1

0

To get the sum of all values between two dates you would do it like this:

SELECT SUM(Column1)
FROM Table1
WHERE Date1 BETWEEN '2/1/2016' AND Date1 <'2/7/2016'

If you want to make it more flexible and have the query get the last week's sum you can use the DATEADD function to lag by one week:

SELECT SUM(Column1)
FROM Table1
WHERE Date1 BETWEEN DATEADD(week, -1, GETDATE()) AND Date1 < GETDATE()

If you want the result set to include a row for each week, you can use UNION to merge the queries.

GVIrish
  • 361
  • 1
  • 6