Solved

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

Posted on 2010-08-25
14
277 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
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…
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…

808 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