September 26, 2006 at 9:48 am
I need to know the number of days that two date ranges have in common (in a view definition).
These are the situation cases;
// |-------------- total work days ----------|
// |-- mon (a)--| |-- mon (c)--| |-- mon (e)--|
// |-- mon (b)--| |-- mon (d)--|
// |------------------------------- mon (f)---------------------|
I have the long IF THEN logic but it seems like there should be a better way.
Thanks for any feedback !
September 26, 2006 at 10:00 am
Can you give us some input data and the required output (please try to cover all cases so we can formulate a complete solution).
September 26, 2006 at 10:18 am
I need to know the number of a days for a given week a task is secheduled.
A 5 day task starts on Friday.
Given a week period, the task start and total days.
week one days = 2
week two days = 3
week three days = 0
Thanks for the help.
September 26, 2006 at 10:43 am
Why 0 for week 3?
September 26, 2006 at 10:46 am
Bill,
To help us help u we need more information than what u had given. Can you give us some specific examples and what u except to see?
Thanks
September 26, 2006 at 1:15 pm
When given date range A as 9/1/2006 - 9/30/2006
and date range B as 8/20/06 - 9/5/06
The result would be 5 : days 9/1, 9/2, 9/3, 9/4 and 9/5 exist in both date ranges
September 26, 2006 at 2:27 pm
Bill,
I hope this answers ur questions.
1.Create a populate Date table.(Lookup table)
Create table Dates(
dt_Date datetime,
WeekOf datetime)
Declare @dt_Date datetime
set @dt_Date ='2-Jan-2006'
set DateFirst 1
While (@dt_Date <= '2-Jan-2011')
Begin
Insert into Dates(dt_Date,WeekOf)
select @dt_Date,DateAdd(d, -1 *DatePart(dw,@dt_Date)+1,@dt_Date)
set @dt_Date = @dt_Date + 1
End
set DateFirst 7
2.Query to get range. You can modify the query to be included in ur view.
Declare @dt_Range1From Datetime,
@dt_Range1To Datetime,
@dt_Range2From Datetime,
@dt_Range2To Datetime
set @dt_Range1From = '9/1/2006'
set @dt_Range1To ='9/30/2006'
set @dt_Range2From = '8/20/06'
set @dt_Range2To ='9/5/06'
select * from Dates where dt_Date between @dt_Range1From and @dt_Range1To
and dt_Date between @dt_Range2From and @dt_Range2To
Thanks
Sreejith
September 26, 2006 at 2:41 pm
Great solution Sreejith. I was just going to post a very similar example using a dates table. The main difference would be the ANSI join syntax in my version. My example uses a table variable for the dates table, but having a static dates table would be my preference.
DECLARE @Dates table (Dates datetime, DayNum int, MonthNum int, YearNum int PRIMARY KEY (Dates))
declare @startDate datetime,
@dates_to_insert int,
@count int
set nocount on
SELECT @startDate = '01/01/01', @dates_to_insert = 10000, @count = 0
WHILE @count < @dates_to_insert
BEGIN
INSERT INTO @Dates
SELECT DATEADD(dd,@count,@startDate),
DAY(DATEADD(dd,@count,@startDate)),
MONTH(DATEADD(dd,@count,@startDate)),
YEAR(DATEADD(dd,@count,@startDate))
SET @count = @count + 1
END
DECLARE @StartDate1 datetime,
@StartDate2 datetime,
@EndDate1 datetime,
@EndDate2 datetime
SELECT @StartDate1 = '9/1/2006',
@EndDate1 = '9/30/2006',
@StartDate2 = '9/20/06',
@EndDate2 = '9/30/06'
SELECT COUNT(*)
FROM (
SELECT dates
FROM @Dates
WHERE Dates BETWEEN @StartDate1 and @EndDate1
) d1
INNER JOIN (
SELECT dates
FROM @Dates
WHERE Dates BETWEEN @StartDate2 and @EndDate2
) d2
ON d1.Dates = d2.Dates
September 26, 2006 at 2:51 pm
Thank you all for the solution. It was just what I needed!!
Thanks, again,
Bill
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy