Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Convert days into dates excluding Sat and Sun

Posted on 2013-01-18
6
559 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

856 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