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