Solved

Creating a query to list all dates between a starting and ending date

Posted on 2010-08-25
14
269 Views
Last Modified: 2013-11-27
I need some help creating a query to list all the workdays between 2 dates. I have a table called "tblTrainingSchedule" and there are 2 fields in that table called "TrainingStartDate" and "TrainingEndDate". I want to be able to run a query and have it list all the workdays between these 2 dates. I do have a table where I store my holidays so I don't list these in this query. Any ideas how I would write this query?
0
Comment
Question by:Lawrence Salvucci
  • 5
  • 4
  • 4
  • +1
14 Comments
 
LVL 18

Expert Comment

by:Cluskitt
ID: 33522668
What do you mean by workdays? Are they another field in the table? Or in another table? Or do you want to know how many weeks passed?
0
 
LVL 9

Expert Comment

by:rg20
ID: 33522696
Select * from tblTrainingSchedule ts left Join tblHolidays th on ts.Workdays <> th.holidays where ts.Workdays between ts.TrainingStartDate and ts.TrainingEndDate

Little rusty sorry if there small errors
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33522719

u saying this

SELECT workingDate
from table
where workingDate between TrainingStartDate and TrainingEndDate
and workingdate not in(select holidaydate from holiday_Master)
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 33522776
What I mean by workdays are weekdays less any holidays. The table is structured like this:

TrainingID - PK
TrainingDesc
TrainingStartDate
TrainingEndDate

What I'm trying to do is populate my calendar to show when the training is going to take place. But in order to do that I need to create a query that will show all the days (including the starting and ending dates) so I can append these into my calendar.

Example:
TrainingID - 3
TrainingStartDate - 08/25/10
TrainingEndDate - 09/07/10

So the list would look like this (excluding weekends and Labor day since Labor day is in my tblHolidays)

08/25/10
08/26/10
08/27/10
08/30/10
08/31/10
09/01/10
09/02/10
09/03/10
09/07/10
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 33522817
I don't think there's any function in SQL that will make a distinction between weekdays and weekends. The only way would be to have all weekdays in a table (or weekends, which I guess would be smaller), but this isn't a very efficient way to do it.
The best way would be programatically in the front-end. SQL just isn't designed for these kinds of operations.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 33522870
I wasn't 100% sure this could be done in SQL. Any ideas on how I would tackle this programmatically?
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 33522931
I found this, which may be what you want, once you adapt it to your needs:

declare @start datetime,
@end datetime

set @start = '2006-07-19'
set @end = '2006-08-01'

declare @no_of_Days int
set @no_of_days = datediff(dd,@start,@end) + 1
set rowcount @no_of_days

select identity(int,0,1) as dy into #temp from sysobjects a, sysobjects b

set rowcount 0

select sum(case when datepart(dw,@start + 1+ dy) in (1,7) then 0 else 1 end
)from #temp
where @start + dy <= @end

drop table #temp
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33522971

It is possible in simple query too.. :-)
SELECT workingDate,

from table 

where workingDate between TrainingStartDate and TrainingEndDate

and workingdate not in(select holidaydate from holiday_Master)

AND DATENAME(DW,workingDate) NOT IN ('SATURDAY','SUNDAY')

Open in new window

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33522993

Just remove comma in above select statement
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 33522997
Where is the field you're calling "WorkingDate"?
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33523127
Hi,

Sory for that.
Pls find attached query.
somehow i'm not able to test as have to leave the office but you can surely get idea.
SELECT workingDate,*

from (

		(SELECT *,DATEADD(DAY,ROWNO,TrainingStartDate)workingDate 

			FROM (SELECT *,ROW_NUMBER() OVER(ORDER BY order_id DESC)ROWNO FROM TABLE) A

		)

	  ) B

where workingDate between TrainingStartDate and TrainingEndDate  

and workingdate not in(select holidaydate from holiday_Master)  

AND DATENAME(DW,workingDate) NOT IN ('SATURDAY','SUNDAY')

Open in new window

0
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 500 total points
ID: 33523176
ONE CHANGE
SELECT workingDate,*  

from (  

                (SELECT *,DATEADD(DAY,ROWNO,TrainingStartDate)workingDate   

                        FROM (SELECT *,ROW_NUMBER() OVER(ORDER BY Trainingid)ROWNO FROM TABLE) A  

                )  

          ) B  

where workingDate between TrainingStartDate and TrainingEndDate    

and workingdate not in(select holidaydate from holiday_Master)    

AND DATENAME(DW,workingDate) NOT IN ('SATURDAY','SUNDAY')

Open in new window

0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 33523430
Can't seem to get it to work. I keep getting a syntax in the FROM clause. What is the "A" & "B" you have in the FROM clause?
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 33523460
Aliases
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now