Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2010-08-25
14
Medium Priority
?
281 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
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 2000 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

Vim Reference Guide

Vim is a powerful text editor favored by many sysadmins and developers - here are some commands that you'll want to keep in your back pocket!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

721 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