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

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?
LVL 1
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Bhavesh ShahConnect With a Mentor Lead AnalysistCommented:
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
 
CluskittCommented:
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
 
rg20Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Bhavesh ShahLead AnalysistCommented:

u saying this

SELECT workingDate
from table
where workingDate between TrainingStartDate and TrainingEndDate
and workingdate not in(select holidaydate from holiday_Master)
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
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
 
CluskittCommented:
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
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I wasn't 100% sure this could be done in SQL. Any ideas on how I would tackle this programmatically?
0
 
CluskittCommented:
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
 
Bhavesh ShahLead AnalysistCommented:

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
 
Bhavesh ShahLead AnalysistCommented:

Just remove comma in above select statement
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Where is the field you're calling "WorkingDate"?
0
 
Bhavesh ShahLead AnalysistCommented:
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
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
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
 
CluskittCommented:
Aliases
0
All Courses

From novice to tech pro — start learning today.