SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Number of days between dates in period
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wembleybear
Starting Member

United Kingdom
49 Posts

Posted - 06/10/2013 :  14:15:00  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3695 Posts

Posted - 06/10/2013 :  14:51:18  Show Profile  Reply with Quote
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
Go to Top of Page

wembleybear
Starting Member

United Kingdom
49 Posts

Posted - 06/10/2013 :  15:33:10  Show Profile  Reply with Quote
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

Go to Top of Page

wembleybear
Starting Member

United Kingdom
49 Posts

Posted - 06/10/2013 :  15:42:27  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3695 Posts

Posted - 06/10/2013 :  15:45:44  Show Profile  Reply with Quote
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
Go to Top of Page

wembleybear
Starting Member

United Kingdom
49 Posts

Posted - 06/10/2013 :  15:57:22  Show Profile  Reply with Quote
Doh!Silly me - I had it in my original query.

Many thanks for your help!!

Martyn
Go to Top of Page

wembleybear
Starting Member

United Kingdom
49 Posts

Posted - 06/11/2013 :  07:54:09  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3695 Posts

Posted - 06/11/2013 :  08:16:40  Show Profile  Reply with Quote
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.
Go to Top of Page

wembleybear
Starting Member

United Kingdom
49 Posts

Posted - 06/11/2013 :  10:39:19  Show Profile  Reply with Quote
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
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 06/11/2013 :  11:46:39  Show Profile  Reply with Quote
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



Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3695 Posts

Posted - 06/11/2013 :  12:15:16  Show Profile  Reply with Quote
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 :)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000