Business Days

pepps11976
pepps11976 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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






Author

Commented:
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?
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).
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
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
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.

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).    

Author

Commented:
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
Assuming you have succesfully tested the fnGetBusinessDays function with the sql I posted above,
I think the query you now need is:
I have annotated the lines I have changed.  

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,   -- removed  - this appeared to be an invalid SQL fragment */
CASE WHEN dbo.itran.it_dtedelv = '1899-12-30' OR
/* dbo.itran.it_dtedelv BETWEEN DATEADD(DD, - 5, it_exdate) AND it_exdate  -- removed and replaced with following line */
fnGetBusinessDays(dbo.itran.it_dtedelv, it_exdate) BETWEEN 0 and 5    -- inserted
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

Author

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

Author

Commented:
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

Author

Commented:
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
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

Author

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial