asked on

Business Days

Hi all

i have posted this before but i am still not getting the correct results.
We need to measure our deliverys to customers, we have two columns that we use in our table to measure this "Customer_Date" and Delivery_Date" we are allowed to deliver 5 days early but no earlier than that and we are not allowed to be late, i have created case staements in my code using dateadd to determine weather it is a pass or fail.

However because the two dates are taking into account weekends and bank holidays the results are not true.
i was shown an articvle on the MSDN site that shows how to create a fuction that calculates business days between two dates and also checks againgst a table called holidays that will cater for bank holidays this is the link http://msdn.microsoft.com/en-us/library/ms189794(v=sql.90).aspx

i have created the function and it works but i need help merging the function in with my other calculation where i use the dateadd to see weather or not it was delivered 5 days early or not

below is my current code that i have which at present does not include the function because that is the bit i need help with

SELECT TOP (100) PERCENT CAST(dbo.itran.it_exdate AS datetime) AS Month, dbo.ihead.ih_sorder AS Sales_Order, dbo.ihead.ih_doc, dbo.ihead.ih_sprojid AS Opportunity_No,
dbo.ihead.ih_custref AS Customer_Reference, dbo.itran.it_stock AS Stock_Reference, dbo.itran.it_quan AS Quantity, dbo.ihead.ih_account AS Account,
dbo.ihead.ih_name AS Name, dbo.itran.it_exdate AS Customer_Date, CAST(dbo.itran.it_due AS datetime) AS Our_Date, CAST(DATEDIFF(dd, dbo.itran.it_exdate,
dbo.itran.it_due) AS varchar(20)) AS Date_Difference, CAST(DATEDIFF(dd, dbo.itran.it_exdate, { fn NOW() }) AS varchar(20)) AS Past_Due,
dbo.itran.it_doc AS [Document], dbo.ihead.ih_quotat AS Quotation, dbo.ihead.ih_proform AS Proforma, dbo.itran.it_anal AS Anaylysis_Code,
CAST(dbo.ihead.ih_orddate AS datetime) AS Order_Date, CASE WHEN dbo.itran.it_dtedelv = '1899-12-30' THEN NULL ELSE dbo.itran.it_dtedelv END AS Delivery_Date,
CASE WHEN dbo.itran.it_dtedelv BETWEEN DATEADD(DD, - 5, it_exdate) AND it_exdate THEN 'Pass' ELSE NULL END AS Passed_Delivery,
CASE WHEN dbo.itran.it_dtedelv = '1899-12-30' OR
dbo.itran.it_dtedelv BETWEEN DATEADD(DD, - 5, it_exdate) AND it_exdate THEN NULLIF (' ', ' ') ELSE 'Fail' END AS Failed_Delivery, DATEPART(mm,
dbo.itran.it_exdate) AS Month_No
FROM dbo.ihead LEFT OUTER JOIN
dbo.itran ON dbo.ihead.ih_doc = dbo.itran.it_doc
WHERE (dbo.itran.it_status = 'A') AND (dbo.ihead.ih_orddate >= '2009-01-01') AND (dbo.itran.it_anal NOT LIKE '%zz%') AND (dbo.itran.it_anal NOT LIKE '%LIN%') AND
(DATEPART(yyyy, dbo.itran.it_exdate) = DATEPART(yyyy, GETDATE()))
ORDER BY month_no

Paul_Harris_Fusion

Assuming your function is called GetWorkingDays and takes 2 dates as paremeters and returns an integer then I think you need to replace calls like:

CASE WHEN dbo.itran.it_dtedelv BETWEEN DATEADD(DD, - 5, it_exdate) AND it_exdate then 'Pass' ELSE 'FAIL' END

with
CASE WHEN GetWorkingDays(dbo.itran.it_dtedelv, it_exdate) BETWEEN 0 and 5 THEN 'Pass' ELSE 'FAIL' END

I may have the signs wrong i.e. it may need to be BETWEEN -5 AND 0 but you will figure that out

pepps11976

ASKER

Hi Paul this is my function

USE [DB_Opera_Copy]
GO
/****** Object: UserDefinedFunction [dbo].[fnGetBusinessDays] Script Date: 08/05/2011 11:49:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnGetBusinessDays]
(
@startdate datetime,
@enddate datetime
)
RETURNS integer
AS
BEGIN
DECLARE @days integer

SELECT @days =
DATEDIFF(d,@startdate,@enddate)
- DATEDIFF(wk,@startdate,@enddate) * 2
- CASE
WHEN DATENAME(dw, @startdate) <> 'Saturday' AND DATENAME(dw, @enddate) = 'Saturday' THEN 1
WHEN DATENAME(dw, @startdate) = 'Saturday' AND DATENAME(dw, @enddate) <> 'Saturday' THEN -1
ELSE 0
END
- (SELECT COUNT(*) FROM holidays WHERE bankholidays BETWEEN @startdate AND @enddate AND DATENAME(dw, bankholidays) <> 'Saturday' AND DATENAME(dw, bankholidays) <> 'Sunday')

RETURN (@days)

END

so are you saying i do not need to merger what i already have into the function if i just ammend like you said that should work?

Paul_Harris_Fusion

Your function looks exactly as I would have expected.

So you can use it in your sql now just like any built-in SQL Server function.

So my previous post should work (adjusting for the different function name).

pepps11976

ASKER

hi paul i tried this

SELECT TOP (100) PERCENT CAST(dbo.itran.it_exdate AS datetime) AS Month, dbo.ihead.ih_sorder AS Sales_Order, dbo.ihead.ih_doc, dbo.ihead.ih_sprojid AS Opportunity_No,
dbo.ihead.ih_custref AS Customer_Reference, dbo.itran.it_stock AS Stock_Reference, dbo.itran.it_quan AS Quantity, dbo.ihead.ih_account AS Account,
dbo.ihead.ih_name AS Name, dbo.itran.it_exdate AS Customer_Date, CAST(dbo.itran.it_due AS datetime) AS Our_Date, CAST(DATEDIFF(dd, dbo.itran.it_exdate,
dbo.itran.it_due) AS varchar(20)) AS Date_Difference, CAST(DATEDIFF(dd, dbo.itran.it_exdate, { fn NOW() }) AS varchar(20)) AS Past_Due,
dbo.itran.it_doc AS [Document], dbo.ihead.ih_quotat AS Quotation, dbo.ihead.ih_proform AS Proforma, dbo.itran.it_anal AS Anaylysis_Code,
CAST(dbo.ihead.ih_orddate AS datetime) AS Order_Date, CASE WHEN dbo.itran.it_dtedelv = '1899-12-30' THEN NULL ELSE dbo.itran.it_dtedelv END AS Delivery_Date,

CASE WHEN GetbusinessDays(dbo.itran.it_dtedelv, it_exdate) BETWEEN 0 and 5 THEN 'Pass' ELSE null END
END AS Passed_Delivery,
CASE WHEN dbo.itran.it_dtedelv = '1899-12-30' OR
dbo.itran.it_dtedelv BETWEEN DATEADD(DD, - 5, it_exdate) AND it_exdate THEN NULLIF (' ', ' ') ELSE 'Fail' END AS Failed_Delivery, DATEPART(mm,
dbo.itran.it_exdate) AS Month_No
FROM dbo.ihead LEFT OUTER JOIN
dbo.itran ON dbo.ihead.ih_doc = dbo.itran.it_doc
WHERE (dbo.itran.it_status = 'A') AND (dbo.ihead.ih_orddate >= '2009-01-01') AND (dbo.itran.it_anal NOT LIKE '%zz%') AND (dbo.itran.it_anal NOT LIKE '%LIN%') AND
(DATEPART(yyyy, dbo.itran.it_exdate) = DATEPART(yyyy, GETDATE()))
ORDER BY month_no
and recieve the error getbusinessdays is not a recognised built in function name

Paul_Harris_Fusion

From your previous posting, your function is called fnGetBusinessDays

As a test, try the following in Query Analyzer

Select fnGetBusinessDays(GetDate(), GetDate() + 15)

If you can get this to work, you should be able to use the function in your own script.

Paul_Harris_Fusion

As a note, I would personally not use prefixes such as fn for function names since it makes the code less readable (though it does highlight the fact that your function is user-defined).

pepps11976

ASKER

Hi Paul could you possible help me abit more when you say replace the code with what you gave

does that mean i delete this part which was in my original code

CASE WHEN dbo.itran.it_dtedelv = '1899-12-30' OR
dbo.itran.it_dtedelv BETWEEN DATEADD(DD, - 5, it_exdate) AND it_exdate THEN NULLIF (' ', ' ') ELSE 'Fail' END AS Failed_Delivery,

or does that stay the same

ASKER CERTIFIED SOLUTION

Paul_Harris_Fusion

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pepps11976

ASKER

Hi Paul im going to test data today and i will let you know how it goes

pepps11976

ASKER

Hi Paul ok the code runs finebut the criteria for a pass or fail does not seem to be working so good i am getting passes where thisnd are being delivered more than 5 days early can you see any issue with the code

john

pepps11976

ASKER

do not know if this will help or not but this code returns all the correct pass, fail values, but this was before i included the function get business days

SELECT TOP (100) PERCENT CAST(dbo.itran.it_exdate AS datetime) AS Month, dbo.ihead.ih_sorder AS Sales_Order, dbo.ihead.ih_doc, dbo.ihead.ih_sprojid AS Opportunity_No,
dbo.ihead.ih_custref AS Customer_Reference, dbo.itran.it_stock AS Stock_Reference, dbo.itran.it_quan AS Quantity, dbo.ihead.ih_account AS Account,
dbo.ihead.ih_name AS Name, CAST(dbo.itran.it_exdate AS datetime) AS Customer_Date, CAST(dbo.itran.it_due AS datetime) AS Our_Date, CAST(DATEDIFF(dd,
dbo.itran.it_exdate, dbo.itran.it_due) AS varchar(20)) AS Date_Difference, CAST(DATEDIFF(dd, dbo.itran.it_exdate, { fn NOW() }) AS varchar(20)) AS Past_Due,
dbo.itran.it_doc AS [Document], dbo.ihead.ih_quotat AS Quotation, dbo.ihead.ih_proform AS Proforma, dbo.itran.it_anal AS Anaylysis_Code,
CAST(dbo.ihead.ih_orddate AS datetime) AS Order_Date, CASE WHEN dbo.itran.it_dtedelv = '1899-12-30' THEN NULL ELSE CAST(dbo.itran.it_dtedelv AS datetime)
END AS Delivery_Date, CASE WHEN dbo.itran.it_dtedelv BETWEEN DATEADD(DD, - 5, it_exdate) AND it_exdate THEN 'Pass' ELSE NULL END AS Passed_Delivery,
CASE WHEN dbo.itran.it_dtedelv = '1899-12-30' OR
dbo.itran.it_dtedelv BETWEEN DATEADD(DD, - 5, it_exdate) AND it_exdate THEN NULLIF (' ', ' ') ELSE 'Fail' END AS Failed_Delivery, DATEPART(mm,
dbo.itran.it_exdate) AS Month_No
FROM dbo.ihead LEFT OUTER JOIN
dbo.itran ON dbo.ihead.ih_doc = dbo.itran.it_doc
WHERE (dbo.itran.it_status = 'A') AND (dbo.ihead.ih_orddate >= '2009-01-01') AND (dbo.itran.it_anal NOT LIKE '%zz%') AND (dbo.itran.it_anal NOT LIKE '%LIN%') AND
(DATEPART(yyyy, dbo.itran.it_exdate) = DATEPART(yyyy, GETDATE()))
ORDER BY sales_order

Paul_Harris_Fusion

Then it sounds as if the issue is with the functon or correctly interpreting the results from the function.
Like I said in my original post, I may have the bounds of the comparison wrong or got the 0 and 5 the wrong way around

I suggest you play around with some test data using the query shown below to build your understanding of your function.

1. Add your own rows of test data into the TestData query below by copying the last line (the one beginning with UNION)
2. Play around with the expression for CalculatedResults until you are getting what you want.
3. Update your real query based on what you learn doing this.

Select
Date1
, Date2
, ExpectedResult
, dbo.fnGetBusinessDays(Date1, Date2) as BusinessDays
, CalculatedResult = CASE WHEN dbo.fnGetBusinessDays(Date1, Date2) BETWEEN 0 and 5 THEN 'Pass' ELSE 'Fail' END
From
(
Select GetDate() as Date1, GetDate()-5 as Date2 , 'Pass' as ExpectedResult
UNION Select '2011-08-08', '2011-08-3', 'Fail'
) TestData

pepps11976

ASKER

Hi Paul

Update

You were right everything was fine its just i was not taking into account the bank holidays, the original code you gave was spot on.

Thanks for your help on this really appreciate it.