Solved

Convert days into dates excluding Sat and Sun

Posted on 2013-01-18
6
557 Views
Last Modified: 2013-01-18
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
Comment
Question by:countrymeister
6 Comments
 
LVL 7

Expert Comment

by:Ross Turner
ID: 38792974
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
 
LVL 32

Accepted Solution

by:
ewangoya earned 500 total points
ID: 38793038
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
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 38793040
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38793335
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
 
LVL 1

Author Comment

by:countrymeister
ID: 38793710
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38793742
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

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question