?
Solved

Convert days into dates excluding Sat and Sun

Posted on 2013-01-18
6
Medium Priority
?
569 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:
Ephraim Wangoya earned 2000 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 49

Expert Comment

by:Dale Fye
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 70

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

850 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