CODEWITHSUNDEEP
sqldatabaset-sql
user1860688
user1860688

Reputation: 25

TSQL order data according to dates

Hi and thank you for your response in advance. I am new to TSQL and currently in an internship. I am creating a web page using VB and I have no problem displaying what is in the first table below to the user. 

----------Name--------------------Date----------------Points

1--------Mike---------------------10/01/2012----------4

2--------Mike---------------------10/05/2012----------2

3--------Mike---------------------10/03/2012----------1

4--------Mike---------------------10/17/2012----------4

5--------Mike---------------------10/24/2012----------4

6--------Simon---------------------11/05/2012--------1

7--------Simon---------------------11/11/2012--------3

8--------Simon---------------------11/20/2012--------4

9--------Simon---------------------11/22/2012--------2

10  Simon   11/27/2012  3

However, I have a drop down list with day and week which sorts the Data from the first table above on a day to day or week to week interval.

Output when user selects to display data in weekly intervals starting from the MIN(Date) to MAX(Date) for each Name in table should be like this. I use

SELECT  Name, MIN(Date) AS StartDate, DATEADD(DD, 7, MIN(Date)) AS EndDate, SUM(Points)

And I know I shouldnt set the EndDate to a fixed point like that because then the next 7 days of data will not show. 

-----Name-------StartDate-------EndDate---------Points

1----Mike-------10/01/2012------10/05/2012----------7

2----Mike-------10/17/2012------10/24/2012----------8

3----Simon-----11/05/2012-----11/11/2012----------4

4----Simon-----11/20/2012-----11/27/2012----------9

Any help will be much appreciated. I hope I was clear in my question. Thanks!

Upvotes: 0

Views: 84

Answers (1)

hrr
hrr

Reputation: 1651

I hope I got your question clear.

For getting sql records based on a range of dates you may use the BETWEEN operator

Like this:

SELECT Name, MIN(Date) AS StartDate, MAX(Date) AS EndDate, SUM(Points)
FROM people
WHERE Date between to_date ('2003/01/01', 'yyyy/mm/dd')
AND to_date ('2003/12/31', 'yyyy/mm/dd') 
GROUP BY name
ORDER BY Points;

I hope this was helpful.

Upvotes: 1

Related Questions