Need to Generate dates between Date Range

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need to Generate dates between Date Range

Author

Topic

daipayan
Posting Yak Master

181 Posts

Posted - 2011-05-05 : 04:15:07

Dear All,

I need a query to view the dates between start date and end date, for this I had following tables:

Table Name:- Calender
-----------------------
Date       | Holiday
-----------------------
2011-04-11 |  False
2011-04-12 |  False
2011-04-13 |  False
2011-04-14 |  False
2011-04-15 |  False
2011-04-16 |  False
2011-04-17 |  True
2011-04-18 |  False
2011-04-19 |  False
2011-04-20 |  False
-----------------------

Table Name:- Project
--------------------------------
PrjID | StartDate  | EndDate
--------------------------------
  1   | 2011-04-11 | 2011-04-18 
  2   | 2011-04-16 | 2011-04-19 
--------------------------------

Now I want a T-SQL which will generate following result based on above tables:

----------------------------------------------
PrjID | StartDate  | EndDate    | Dates
----------------------------------------------
  1   | 2011-04-11 | 2011-04-18 | 2011-04-11
  1   | 2011-04-11 | 2011-04-18 | 2011-04-11
  1   | 2011-04-11 | 2011-04-18 | 2011-04-11
  1   | 2011-04-11 | 2011-04-18 | 2011-04-11
  1   | 2011-04-11 | 2011-04-18 | 2011-04-11
  1   | 2011-04-11 | 2011-04-18 | 2011-04-11
  1   | 2011-04-11 | 2011-04-18 | 2011-04-18
  1   | 2011-04-11 | 2011-04-18 | 2011-04-19
  2   | 2011-04-16 | 2011-04-19 | 2011-04-16 
  2   | 2011-04-16 | 2011-04-19 | 2011-04-18
  2   | 2011-04-16 | 2011-04-19 | 2011-04-19
  2   | 2011-04-16 | 2011-04-19 | 2011-04-20
----------------------------------------------

here you can see, 2011-04-17 is not being considered as it's holiday status in Calender is TRUE. The T-SQL should only consider those dates whose holiday status is False.

Thanks in advance!

Regards,
Daipayan
Software Programmer

Application has reported a 'Not My Fault' in module KRNL.EXE in line 0200:103F

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-05 : 04:28:21

[code]
select *
from Project p
inner join Calendar c on p.StartDate <= c.Date and p.EndDate >= c.Date
where c.Holiday = 0
[/code]

KH
[spoiler]Time is always against us[/spoiler]