Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 289
  • Last Modified:

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?
0
Lawrence Salvucci
Asked:
Lawrence Salvucci
  • 5
  • 4
  • 4
  • +1
1 Solution
 
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
 
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
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
 
Bhavesh ShahLead 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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now