Return Days of the Week

[JohnEOasis]

I have a start date i.e. 8/3/2008 and an end date 8/9/2009 and I need to return all the days of the week and date between these dates. ie Sunday 8/3/2009, Monday 8/4/2009, Tuesday 8/5/2009 etc…. I am a real newbie with SQL and have no clue where to start; any help would be greatly appreciated.

 

[gmmastros]

I would approach this using a numbers table, which I have in every database I use. A Numbers table can be used for many different queries, allowing the code to be more efficient.

I'll assume you don't have a numbers table.

First, you need to understand some concepts that may not be obvious to a beginner. When you create a variable in T-SQL code, it always starts with an @ symbol. Variables must be declared with a data type. You can create a variable that is a table (a table variable). This allows you to have a single variable with multiple rows and multiple columns.

Without a numbers table, you will also need to know how to perform loops in your SQL code. There are a couple ways to do it, but I prefer the WHILE method. Generally speaking, loops are slow to execute in SQL (much slower than other languages like VB, C#, etc....) Since loops are slow, it's usually best to avoid them whenever possible.

Finally, there are some interesting things you can do with a DateTime value, like adding an integer to it to increment the day.

Anyway, take a look at the following code to see how you can put all these concepts together to achieve your desired results.

[green]-- Declare some variables[/green]
Declare @Start DateTime
Declare @End DateTime

[green]-- Set some values[/green]
Set @Start = '20080803'
Set @End = '20080809'

[green]-- Create a table variable to store intermediate results[/green]
Declare @Temp Table(TheDate DateTime)

[green]-- Loop[/green]
While @Start <= @End
  Begin
    [green]-- Insert value to table variable.[/green]
    Insert Into @Temp Values(@Start)

    [green]-- Increment loop counter[/green]
    Set @Start = @Start + 1
  End

[green]-- Select results from table variable.[/green]
Select TheDate, DateName(Weekday, TheDate) 
From @Temp
Order By TheDate

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JohnEOasis]

Thank you! The code works wonderfuly! I however do not! I am a real newbie to sql and do not understand how to create this functon and pass the start date and end date. I tried

Declare @Start DateTime
Declare @End DateTime
Set @Start = (SELECT scheduledstart, FROM dbo.event)
Set @End = (SELECT scheduledend, FROM dbo.event)
Declare @Temp Table(TheDate DateTime)
While @Start <= @End
Begin
Insert Into @Temp Values(@Start)
Set @Start = @Start + 1
End
Select TheDate, DateName(Weekday, TheDate)
From @Temp
Order By TheDate

and as you may have guessed SQL just laughed and pointed at me....

 

[gmmastros]

well......

I'm not sure how to answer this. The code you show makes it seem like there is only 1 row in your event table. I somehow doubt this is the case. In fact, I would assume that you want to somehow filter the event table to return the ScheduledStart and ScheduledEnd for a particular row.

Again, without know the structure of that table, or even the desired results, it's hard to say. Perhaps you want something like this...

Declare @Start DateTime
Declare @End DateTime

[blue]Select @Start = ScheduledStart,
       @End = ScheduledEnd
From   dbo.event
Where  [!]EventID = 7[/!][/blue]

Declare @Temp Table(TheDate DateTime)
While @Start <= @End
  Begin
    Insert Into @Temp Values(@Start)
    Set @Start = @Start + 1
  End
Select TheDate, DateName(Weekday, TheDate) 
From @Temp
Order By TheDate

Of course, I show EventId = 7 as an example. There should be something in that table that allows you to identify the row you want to use.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JohnEOasis]

Thank you for your help. I was able to manualy add an event id and got the results I wanted. I still am having problems passing the qurried result as the event id and I am not sure how to set this up so I can get related data from other tables.

 

[gmmastros]

Show some sample data and expected results. This will make it easier to help you.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JohnEOasis]

Thank you for your help.
These are the results I am look for:


Weekday ReserveDate EventName RoomNumber
--------------------------------------------------------------
Monday 8/25/2008 10:30:00 AM Staff Meeting 1A
Tuesday 8/26/2008 10:30:00 AM Staff Meeting 1A
Wednesday 8/27/2008 10:30:00 AM Staff Meeting 1A
Thursday 8/28/2008 10:30:00 AM Staff Meeting 1A
Friday 8/29/2008 10:30:00 AM Staff Meeting 1A
Saturday 8/30/2008 10:30:00 AM Staff Meeting 1A
Sunday 8/31/2008 10:30:00 AM Staff Meeting 1A


what I am trying to do is pass the event id to this querry i.e. WHERE (eventid = @TheEventID)
so it can be updated based on ther current row in a RDL report. Then I need other fields from the event table like eventname; then something like roomnumber from another table.All the records are related by eventid.

 

[gmmastros]

Then I need other fields from the event table like eventname; then something like roomnumber from another table.All the records are related by eventid.

Can you show sample data from the Event table and the other table?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

You know.... there usually more than 1 way to do things. What I am about to suggest may sound strange, but bear with me.

I suggest you add a Numbers table to your database. With a numbers table, the query becomes much easier to write and allows you more flexibility to create other, more interesting queries. I have a numbers table in every database I work with.

This numbers table should have 2 columns and 1 million rows. It should have a NUM column and a CalendarDate column. You might think.... 1 million rows.... that's huge. You would be wrong. Think of it this way. An int column takes 4 bytes, a DateTime column takes 8 bytes. So, we have 12 bytes per row. We are also going to create a couple indexes on this table, which take up more room.... let's say, another 12 bytes per row, for a total of 24 bytes. Multiply that by 1 million, and the estimated storage space for this table will be 24 megabytes. Pretty modest by today's standards.

By having this table, you will be able to write better queries that execute MUCH faster. This table only needs to be created once, here's how...

Create Table Numbers(Num Int Identity(1,1) Primary Key Clustered, CalendarDate DateTime)

Declare @i Int
Set @i = 1

While @i <= 1000000
  Begin
	Insert Into Numbers Default Values
	Set @i = @i + 1
  End

Update Numbers Set CalendarDate = Num

Create Index Numbers_CalendarDate On Numbers(CalendarDate)

Select * from Numbers

You should expect this to take several minutes to run. This only needs to be done once.

Now that you have this table in your database, you can write some interesting queries, like this....

Select	DateName(Weekday, DateAdd(Day, Num, 0)) As [Weekday],
        DateAdd(Day, Num, 0) + Convert(Float, ScheduledStart) - Convert(Int, ScheduledStart) As ReserveDateStart, 
        DateAdd(Day, Num, 0) + Convert(Float, ScheduledEnd) - Convert(Int, ScheduledEnd) As ReserveDateStart, 
        EventName
From    Event
        Inner Join Numbers
          On Numbers.CalendarDate >= Event.ScheduledStart -1
          And Numbers.CalendarDate <= Event.ScheduledEnd
Where   [!]EventId = 7[/!]

The query above would return multiple rows for each event (if it spans multiple days).

Or, you could write a query that shows all the events for a single day, like this...

Select	DateName(Weekday, DateAdd(Day, Num, 0)) As [Weekday],
        DateAdd(Day, Num, 0) + Convert(Float, ScheduledStart) - Convert(Int, ScheduledStart) As ReserveDateStart, 
        DateAdd(Day, Num, 0) + Convert(Float, ScheduledEnd) - Convert(Int, ScheduledEnd) As ReserveDateStart, 
        EventName
From    Event
        Inner Join Numbers
          On Numbers.CalendarDate >= Event.ScheduledStart -1
          And Numbers.CalendarDate <= Event.ScheduledEnd
Where   [!]Numbers.CalendarDate = '20080827'[/!]

Or for the whole month...

Select	DateName(Weekday, DateAdd(Day, Num, 0)) As [Weekday],
        DateAdd(Day, Num, 0) + Convert(Float, ScheduledStart) - Convert(Int, ScheduledStart) As ReserveDateStart, 
        DateAdd(Day, Num, 0) + Convert(Float, ScheduledEnd) - Convert(Int, ScheduledEnd) As ReserveDateStart, 
        EventName
From    Event
        Inner Join Numbers
          On Numbers.CalendarDate >= Event.ScheduledStart -1
          And Numbers.CalendarDate <= Event.ScheduledEnd
Where   [!]Numbers.CalendarDate <= '20080801'
        And Numbers.CalendarDate < '20080901'[/!]

Or the whole year. The whole week. Whatever. By using a numbers table, there's no looping, so you can show multiple days, multiple events, or both.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JohnEOasis]

Thank you. This makes since to me. I am trying to avoid creating a table, but if this is the way it needs to be done I will. Once again thank you very much.