display data between two dates week by week

Question

Post reply

display data between two dates week by week

bmr270

Default port

Points: 1477
More actions
April 18, 2009 at 8:54 am

#133188

I have to display data between two dates week by week. It should display Monday to Sunday.

Example

TableA

Sno Sname Date

1 A 04/01/09

2 B 04/02/09

3 C 04/03/09

4 D 04/04/09

5 E 04/05/09

6 F 04/06/09

7 G 04/07/09

8 H 04/08/09

9 I 04/09/09

10 J 04/010/09

11 K 04/011/09

I have to pass two variables to stored procedure. They are @startdate and @enddate.

Exec Stprocedure ‘04/01/09’ ‘04/11/09’

It should display

1st week Details

Sno Sname Date

1 A 04/01/09

2 B 04/02/09

3 C 04/03/09

4 D 04/04/09

5 E 04/05/09

2nd week Details

Sno Sname Date

6 F 04/06/09

7 G 04/07/09

8 H 04/08/09

9 I 04/09/09

10 J 04/010/09

11 K 04/011/09

Because this month started with Wednesday, that’s why it should display wed to sun for 1st week and Monday to Sunday for 2nd week.

It should display for all the weeks between two dates.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37299 More actions · Answer 1

Hi

You can use DATEPART to get the weekday and the week:



DECLARE @t TABLE (Sno INT, Sname VARCHAR(10), Date DATETIME)

INSERT INTO @T

             SELECT '1', 'A', '04/01/09'

   UNION ALL SELECT '2', 'B', '04/02/09'

   UNION ALL SELECT '3', 'C', '04/03/09'

   UNION ALL SELECT '4', 'D', '04/04/09'

   UNION ALL SELECT '5', 'E', '04/05/09'

   UNION ALL SELECT '6', 'F', '04/06/09'

   UNION ALL SELECT '7', 'G', '04/07/09'

   UNION ALL SELECT '8', 'H', '04/08/09'

   UNION ALL SELECT '9', 'I', '04/09/09'

   UNION ALL SELECT '10', 'J', '04/010/09'

   UNION ALL SELECT '11', 'K', '04/011/09'

DECLARE @from DATETIME

DECLARE @to DATETIME

SELECT @from = '04/01/09', @to = '04/11/09'

SELECT

      'Week: ' +

      CASE

         WHEN DATEPART(WEEKDAY, Date) = 1

            THEN CONVERT(VARCHAR(10), DATEPART(WEEK, Date) - 1)

         ELSE CONVERT(VARCHAR(10), DATEPART(WEEK, Date))

      END WeekNo,

      *

   FROM @t

   ORDER BY WeekNo, Date

Greets

Flo

The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems

Paul White SSC Guru Points: 150457 More actions · Answer 2

Just a quick addition:

If you use DATEPART with weekday, make sure DATEFIRST is SET appropriately.

It is possible to write the comparison generically for any DATEFIRST by using @@DATEFIRST, but I can't immediately find the code for it - it involves some work with modulo though, if that helps...

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi