[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Convert days into dates excluding Sat and Sun

Posted on 2013-01-18
6
Medium Priority
?
567 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
[X]
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
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 48

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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.

650 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