Number of days between dates in period

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

General SQL Server Forums

New to SQL Server Programming

Number of days between dates in period

Author

Topic

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2013-06-10 : 14:15:00

I'm trying to work out the number of working days (hire days) an item is on hire during a specified month. My problem is that the hire can start before the month or during the month (orig_start_rent). It can end (stop_rent) during the month, after the end of the month or be null if it is an ongoing hire.

I only want to count hire days within the specified period – if the hire date ends after that period or is null, then the last day in the specified period should be used.

So for example – if my specified period is 01/04/2013 to 30/04/2013:

Orig_start_ rent stop_rent DaysThisMonth
28/03/2013 04/04/2013 4
15/04/2013 17/04/2013 2
22/04/2013 07/05/2013 6
22/04/2013 NULL 6

Here is my code with dates in April specified. How can I make it take these factors into account, rather than just returning the total number of working days between the start and stop dates?

select item, descr, orig_start_rent, stop_rent, (DateDiff(dd,
orig_start_rent, IsNull(stop_rent, GetDate()))) - (DateDiff(wk,
orig_start_rent, IsNull(stop_rent, GetDate())) * 2) - (Case
When DateName(dw, orig_start_rent) = 'Sunday' Then 1 Else 0 End) - (Case
When DateName(dw, IsNull(stop_rent, GetDate())) = 'Saturday' Then 1 Else 0
End) As [DaysThisMonth]

from deltickitem

where orig_start_rent <= '2013-04-30' and (stop_rent > '2013-04-01' or stop_rent is null)

Many thanks
Martyn

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-10 : 14:51:18

This will be easiest if you create a calendar table. (You can do it without a calendar table, but it is a little less transparent)

CREATE TABLE #Calendar(date DATETIME, isWeekday BIT);

DECLARE @startDate DATETIME, @endDate DATETIME;
SET @startDate = '20130101'; SET @endDate = '20131231';
;WITH cte AS
(
	SELECT @startDate AS date, CASE WHEN DATEDIFF(dd,0,@startDate)%7 > 5 THEN 0 ELSE 1 END AS isWeekDay
	UNION ALL
	SELECT DATEADD(dd,1,date), CASE WHEN DATEDIFF(dd,0,date)%7 IN (4,5) THEN 0 ELSE 1 END
	FROM cte
	WHERE date < @endDate
)
INSERT INTO #Calendar SELECT * FROM cte OPTION (MAXRECURSION 0);

Now write your query against the calendar table like this:

DECLARE @periodStart DATETIME, @periodEnd DATETIME;
SET @periodStart = '20130401'; SET @periodEnd = '20130430';
SELECT 
	item, 
	descr,
	orig_start_rent,
	stop_rent,
	SUM( CASE WHEN isWeekday = 1 THEN 1 ELSE 0 END) AS DaysThisMonth
FROM
	deltickitem d
	INNER JOIN #Calendar c ON
		c.date >= orig_start_rent AND c.date <= stop_rent 
		AND c.date >= @periodStart AND c.date <= @periodEnd
GROUP BY
	item, 
	descr,
	orig_start_rent,
	stop_rent

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2013-06-10 : 15:33:10

Thanks, that looks like it might work for me. On first glance it is returning a LOT less records than my initial query but I'm guessing this is because the calendar table is set to 2013 dates only? Some of our ongoing hires started in 2011! Is it OK to specify a longer period for the calendar table?

Martyn

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2013-06-10 : 15:42:27

Actually, looking at it again, it is ignoring all rows where the stop_rent is NULL. This is a problem as if no end date has been entered for the hire, it is ongoing, and stop_rent should be treated as if it were the end of the specified period (month). Is there a way to incorporate this please?

Martyn

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-10 : 15:45:44

Yes, you can create a calendar with a range wide enough to cover the earliest start date and the latest end date (or beyond if you want to consider a few years into the future).

To account for cases where stop_rent is null, change the join conditon to:

FROM
	deltickitem d
	INNER JOIN #Calendar c ON
		c.date >= orig_start_rent AND (c.date <= stop_rent or  stop_rent is null)
		AND c.date >= @periodStart AND c.date <= @periodEnd

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2013-06-10 : 15:57:22

Doh!Silly me - I had it in my original query.

Many thanks for your help!!

Martyn

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2013-06-11 : 07:54:09

OK, now I've had a chance to try this with the live ERP system I've run into a couple of problems. While it works on the SQL server, it does not work in the ERP SQL query builder that we have as it does not allow creation and use of temporary tables. This means I cannot save it for other users who don't have direct access to the SQL Server.

I know you said there is a way of doing it without the calendar table and I think we may have to try that, could you help me out with how that might look please?

The query that works for me is below:

CREATE TABLE #Calendar(date DATETIME, isWeekday BIT);

DECLARE @startDate DATETIME, @endDate DATETIME;
SET @startDate = '20100101'; SET @endDate = '20201231';
;WITH cte AS
(
SELECT @startDate AS date, CASE WHEN DATEDIFF(dd,0,@startDate)%7 > 5 THEN 0 ELSE 1 END AS isWeekDay
UNION ALL
SELECT DATEADD(dd,1,date), CASE WHEN DATEDIFF(dd,0,date)%7 IN (4,5) THEN 0 ELSE 1 END
FROM cte
WHERE date < @endDate
)
INSERT INTO #Calendar SELECT * FROM cte OPTION (MAXRECURSION 0);

DECLARE @periodStart DATETIME, @periodEnd DATETIME;
SET @periodStart = '20130501'; SET @periodEnd = '20130531';
SELECT
item,
descr,
orig_start_rent,
stop_rent,
SUM( CASE WHEN isWeekday = 1 THEN 1 ELSE 0 END) AS DaysThisMonth
FROM
deltickitem d
INNER JOIN #Calendar c ON
c.date >= orig_start_rent AND (c.date <= stop_rent or stop_rent is null)
AND c.date >= @periodStart AND c.date <= @periodEnd
GROUP BY
item,
descr,
orig_start_rent,
stop_rent

Many thanks
Martyn

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-11 : 08:16:40

You don't have to create the temporary calendar table with each query - I was only trying to demonstrate. What you can do, if you are allowed to do so, is to create a regular base calendar table in the database (dbo.Calendar, for example), populate it and leave it there. Then write the query against that table instead of against #Calendar.

To do this without a calendar table, you will need to use the datediff function. You can use the following to calculate the number of weekdays:

datediff(dd,orig_start_rent, stop_rent)-datediff(wk,orig_start_rent, stop_rent)*2

There are couple of things you need to take care of though:

1. That works only if orig_start_rent and stop_rent are weekdays. If they are on Saturday or Sunday, you will need to do make some adjustments.
2. Since your stop_rent can be null, you will need to replace that with @periodEnd if it is null.

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2013-06-11 : 10:39:19

Unfortunately, I do not have permission to create a table in that db. I've modified my original query to include @periodEnd to take care of the null stop_rent values, how can I get it to use the @periodStart if the orig_start_rent is earlier than @periodStart as well?

Thanks for your help, nearly there.

Martyn

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-11 : 11:46:39

Try this:




DECLARE @TEST1 TABLE(ID INT, Orig_start_rent DATE, Stop_rent DATE);
INSERT INTO @Test1 VALUES
(1, '20120102', '20130312'),
(2, '20120202', '20121212'),
(3, '20120302', '20121112'),
(4, '20130402', NULL),
(5, '20130502', '20130528'),
(6, '20130602', NULL),
(7, '20120702', '20130112'),
(8, '20120802', NULL),
(9, '20130102', '20130212'),
(10, '20130102', '20130609');


DECLARE @LRange DATE = '20130531' -- '20130401' -- '20130608' 
DECLARE @URange DATE = '20130601' -- '20130430' -- '20130609' 

-- DECLARE @LRange DATE = '20120701' -- '20130401' -- '20130608' 
-- DECLARE @URange DATE = '20120812' -- '20130430' -- '20130609' 



;WITH T1 AS 
(
SELECT ID, Orig_start_rent, Stop_rent, 
       (CASE WHEN (Stop_rent < @LRange) OR (Orig_start_rent > @URange) THEN NULL
                           WHEN (Orig_start_rent <= @LRange) THEN @LRange 
                           ELSE Orig_start_rent END) AS StartDate, 
                           (CASE  WHEN (Stop_rent IS NULL) or (Stop_rent > @URange) THEN @URange
                           WHEN (Stop_rent < @LRange) OR (Orig_start_rent > @URange) THEN NULL         
                           ELSE Stop_rent END) AS EndDate
       FROM @TEST1),
T2 AS 

(SELECT ID,  Orig_start_rent, Stop_rent,  DATEDIFF(dd, StartDate, EndDate)+1 AS NumberofDays, DATEDIFF(dd,0,StartDate) as NumDaysToStart,
                           DATEDIFF(wk, StartDate, EndDate) as NumberOfWeeks, 
                           DATEDIFF(dd,0,EndDate) AS NumDaysToEnd  FROM T1)
SELECT ID, Orig_start_rent, Stop_rent, NumberofDays, NumberOfWeeks,
       (CASE WHEN (NumDaysToStart % 7 = 6) OR (NumDaysToEnd % 7 = 5) THEN       (NumberofDays - NumberOfWeeks*2) -1
             ELSE (NumberofDays - NumberOfWeeks*2) END) As NumberOfWorkingDays 
             FROM T2  WHERE NumberofDays > 0

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-11 : 12:15:16

What MuMu said is what I had in mind; I was trying to take into accounts the weekends and range using in-line expressions rather than using a CTE as MuMu did, and it was turning out to be too ugly and painful. WTG, MuMu :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources