Solved

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

Posted on 2010-08-25
14
279 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

710 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