• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 580
  • Last Modified:

Convert days into dates excluding Sat and Sun

Have a table Products which has four columns. Product Code is the unique key
Based on the current date , I would like to convert the days for delivery into dates,
excluding Sat and Sun into another temp table.
The date is calculated as Current date plus the value in the days column
The null value indicates a default of 3 days to deliver


ProductCode, Region1DeliveryDays,Region2DeliveryDays,Region3DeliveryDays
COT         , NULL                , NULL                ,NULL              
ABC         , 3                      , 7                ,2      
PQR         , 5                      , 1                ,2      

on 01/18/2013 the data in temp table would be

COT         , 01/23/2013                , 01/23/2013          ,01/23/2013                   
ABC         , 01/23/2013            , 01/29/2013          ,01/22/2013      
PQR         , 01/25/2013            , 01/21/2013          ,01/22/2013      


on 01/21/2013 the data in temp table would be

COT         , 01/24/2013                , 01/24/2013          ,01/24/2013                   
ABC         , 01/24/2013            , 01/30/2013          ,01/23/2013      
PQR         , 01/28/2013            , 01/22/2013          ,01/23/2013
0
countrymeister
Asked:
countrymeister
1 Solution
 
Ross TurnerManagement Information Support AnalystCommented:
Create a Dates table and join it on and exclude Sat / Sun

http://www.techrepublic.com/blog/datacenter/simplify-sql-server-2005-queries-with-a-dates-table/326

Maybe somthing like this for grabbing the data (Untested!)

select 
ProductCode,
dateadd(day,(isnull(Region1DeliveryDays,3),getdate()) as [Region1DeliveryDays],
dateadd(day,(isnull(Region2DeliveryDays,3),getdate()) as [Region2DeliveryDays],
dateadd(day,(isnull(Region3DeliveryDays,3),getdate()) as [Region3DeliveryDays]
from Table

Open in new window

0
 
Ephraim WangoyaCommented:
Try this query
--ProductCode, Region1DeliveryDays,Region2DeliveryDays,Region3DeliveryDays
--COT         , NULL                , NULL                ,NULL               
--ABC         , 3                      , 7                ,2      
--PQR         , 5                      , 1                ,2  

select 
	case 
		when DATEPART(weekday, DATEADD(DD, IsNull(Region1DeliveryDays, 3), GETDATE())) IN (2,3) then 	
			DATEADD(DD, IsNull(Region1DeliveryDays, 3), GETDATE())
		else 
			DATEADD(DD, IsNull(Region1DeliveryDays, 3) + 2, GETDATE())
		end,
	case 
		when DATEPART(weekday, DATEADD(DD, IsNull(Region2DeliveryDays, 3), GETDATE())) IN (2,3) then 	
			DATEADD(DD, IsNull(Region2DeliveryDays, 3), GETDATE())
		else 
			DATEADD(DD, IsNull(Region2DeliveryDays, 3) + 2, GETDATE())
		end,
	case 
		when DATEPART(weekday, DATEADD(DD, IsNull(Region3DeliveryDays, 3), GETDATE())) IN (2,3) then 	
			DATEADD(DD, IsNull(Region3DeliveryDays, 3), GETDATE())
		else 
			DATEADD(DD, IsNull(Region3DeliveryDays, 3) + 2, GETDATE())
		end

Open in new window

0
 
Barry CunneyCommented:
CREATE FUNCTION dbo.fn_NonWorkDays
--Presets
--Define the input parameters (OK if reversed by mistake).
(
    @StartDate DATETIME,
    @EndDate   DATETIME = NULL --@EndDate replaced by @StartDate when DEFAULTed
)

--Define the output data type.
RETURNS INT

AS
--Calculate the RETURN of the function.
BEGIN
    --Declare local variables
    --Temporarily holds @EndDate during date reversal.
    DECLARE @Swap DATETIME

    --If the Start Date is null, return a NULL and exit.
    IF @StartDate IS NULL
        RETURN NULL

    --If the End Date is null, populate with Start Date value so will have two dates (required by DATEDIFF below).
     IF @EndDate IS NULL
        SELECT @EndDate = @StartDate

    --Strip the time element from both dates (just to be safe) by converting to whole days and back to a date.
    --Usually faster than CONVERT.
    --0 is a date (01/01/1900 00:00:00.000)
     SELECT @StartDate = DATEADD(dd,DATEDIFF(dd,0,@StartDate), 0),
            @EndDate   = DATEADD(dd,DATEDIFF(dd,0,@EndDate)  , 0)

    --If the inputs are in the wrong order, reverse them.
     IF @StartDate > @EndDate
        SELECT @Swap      = @EndDate,
               @EndDate   = @StartDate,
               @StartDate = @Swap

    --Calculate and return the number of workdays using the input parameters.
    --This is the meat of the function.
    --This is really just one formula with a couple of parts that are listed on separate lines for documentation purposes.
     RETURN
     (
        SELECT
        --Subtact 2 days for each full weekend
        (
        (DATEDIFF(wk,@StartDate, @EndDate)*2)
        --If StartDate is a Sunday, Subtract 1
        +(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday'
            THEN 1
            ELSE 0
        END)
        --If EndDate is a Saturday, Subtract 1
        +(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday'
            THEN 1
            ELSE 0
        END)
        )
           
      )        
    END


INSERT TempTable
SELECT ProductCode
,DATEADD(DAY,dbo.fn_NonWorkDays(GETDATE(),DATEADD(DAY,ISNULL([Region1DeliveryDays],3),GETDATE())) + ISNULL([Region1DeliveryDays],3),GETDATE())
,DATEADD(DAY,dbo.fn_NonWorkDays(GETDATE(),DATEADD(DAY,ISNULL([Region2DeliveryDays],3),GETDATE())) + ISNULL([Region2DeliveryDays],3),GETDATE())
,DATEADD(DAY,dbo.fn_NonWorkDays(GETDATE(),DATEADD(DAY,ISNULL([Region3DeliveryDays],3),GETDATE())) + ISNULL([Region3DeliveryDays],3),GETDATE())
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Dale FyeCommented:
It would be much simpler if would normalize that table to:

Product_Code    Region    Days
ABC                          1            3
ABC                         2             7
ABC                         3            2
PQR                        1              5

You get the idea.  Then you could simply join this table on Prod_Code and Region to get the Days and use the DateAdd function with that.
0
 
countrymeisterAuthor Commented:
by: ewangoya

I would like to know why we are checking for datepart of the weekday to be in 2,3, that would only test for Mon, Tues
0
 
Scott PletcherSenior DBACommented:
The core computation for each delivery date is:

    DATEADD(DAY, ISNULL(Region1DeliveryDays, 3) +
        ISNULL(Region1DeliveryDays, 3) / 5 * 2 +
        CASE WHEN DATEDIFF(DAY, 6, @date) % 7 + ISNULL(Region1DeliveryDays % 5, 3) >= 6 THEN 2
             ELSE 0 END, @date) AS Region1DeliveryDate,

[Note: this works for any/all date and language settings.]



For example:

DECLARE @date date
SET @date = GETDATE() --+ 3

SELECT
    ProductCode,
    DATEADD(DAY, ISNULL(Region1DeliveryDays, 3) +
        ISNULL(Region1DeliveryDays, 3) / 5 * 2 +
        CASE WHEN DATEDIFF(DAY, 6, @date) % 7 + ISNULL(Region1DeliveryDays % 5, 3) >= 6 THEN 2
             ELSE 0 END, @date) AS Region1DeliveryDate,
    DATEADD(DAY, ISNULL(Region2DeliveryDays, 3) +
        ISNULL(Region2DeliveryDays, 3) / 5 * 2 +
        CASE WHEN DATEDIFF(DAY, 6, @date) % 7 + ISNULL(Region2DeliveryDays % 5, 3) >= 6 THEN 2
             ELSE 0 END, @date) AS Region2DeliveryDate,
    DATEADD(DAY, ISNULL(Region3DeliveryDays, 3) +
        ISNULL(Region3DeliveryDays, 3) / 5 * 2 +
        CASE WHEN DATEDIFF(DAY, 6, @date) % 7 + ISNULL(Region3DeliveryDays % 5, 3) >= 6 THEN 2
             ELSE 0 END, @date) AS Region3DeliveryDate
FROM (
    SELECT 'COT' AS ProductCode, CAST(NULL AS int) AS Region1DeliveryDays, CAST(NULL AS int) AS Region2DeliveryDays,
        CAST(NULL AS int) AS Region3DeliveryDays  UNION ALL
    SELECT 'ABC'         , 3                      , 7                ,2      UNION ALL
    SELECT 'PQR'         , 5                      , 1                ,2
) AS test_data
ORDER BY ProductCode
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now