Link to home
Start Free TrialLog in
Avatar of itdeptvnam
itdeptvnamFlag for United States of America

asked on

Assign workdays to Date field from a payroll imported file with a date range including weekend days

I need to place the actual date taken for vacation into the field Dateoff in my table from an imported file. I don't want weekend dates assigned to the records.

Sample of what I'm working on: ,

CREATE TABLE ##PDOImport
(
      CompID                  Varchar(5),
      ID                  Varchar(6),
      FNam                  Varchar(50),
      LNam                  Varchar(50),
      ReqTyp                  Varchar(5),
      ReqHrs                  decimal(12,2),
      StartDat                  Date,
      EndDat                  Date,
      Sts                  Varchar(50)

)

INSERT INTO ##PDOImport (CompID, ID, FNam, LNam, ReqTyp, ReqHrs, StartDat, EndDat, Sts)
VALUES('N1234','10290','John','Doe','PDO',8.00,'04/08/2015','04/14/2015','Taken')
INSERT INTO ##PDOImport (CompID, ID, FNam, LNam, ReqTyp, ReqHrs, StartDat, EndDat, Sts)
VALUES('N1234','10290','John','Doe','PDO',8.00,'04/08/2015','04/14/2015','Taken')
INSERT INTO ##PDOImport (CompID, ID, FNam, LNam, ReqTyp, ReqHrs, StartDat, EndDat, Sts)
VALUES('N1234','10290','John','Doe','PDO',8.00,'04/08/2015','04/14/2015','Taken')
INSERT INTO ##PDOImport (CompID, ID, FNam, LNam, ReqTyp, ReqHrs, StartDat, EndDat, Sts)
VALUES('N1234','10290','John','Doe','PDO',8.00,'04/08/2015','04/14/2015','Taken')
INSERT INTO ##PDOImport (CompID, ID, FNam, LNam, ReqTyp, ReqHrs, StartDat, EndDat, Sts)
VALUES('N1234','10290','John','Doe','PDO',8.00,'04/08/2015','04/14/2015','Taken')
INSERT INTO ##PDOImport (CompID, ID, FNam, LNam, ReqTyp, ReqHrs, StartDat, EndDat, Sts)
VALUES('N1234','10295','Mary','Test','PDO',8.00,'04/07/2015','04/10/2015','Taken')
INSERT INTO ##PDOImport (CompID, ID, FNam, LNam, ReqTyp, ReqHrs, StartDat, EndDat, Sts)
VALUES('N1234','10295','Mary','Test','PDO',8.00,'04/07/2015','04/10/2015','Taken')
INSERT INTO ##PDOImport (CompID, ID, FNam, LNam, ReqTyp, ReqHrs, StartDat, EndDat, Sts)
VALUES('N1234','10295','Mary','Test','PDO',8.00,'04/07/2015','04/10/2015','Taken')
INSERT INTO ##PDOImport (CompID, ID, FNam, LNam, ReqTyp, ReqHrs, StartDat, EndDat, Sts)
VALUES('N1234','10295','Mary','Test','PDO',8.00,'04/07/2015','04/10/2015','Taken')
INSERT INTO ##PDOImport (CompID, ID, FNam, LNam, ReqTyp, ReqHrs, StartDat, EndDat, Sts)
VALUES('N1234','10299','Joe','Test','PDO',8.00,'04/06/2015','04/17/2015','Taken')
INSERT INTO ##PDOImport (CompID, ID, FNam, LNam, ReqTyp, ReqHrs, StartDat, EndDat, Sts)
VALUES('N1234','10299','Joe','Test','PDO',8.00,'04/06/2015','04/17/2015','Taken')
INSERT INTO ##PDOImport (CompID, ID, FNam, LNam, ReqTyp, ReqHrs, StartDat, EndDat, Sts)
VALUES('N1234','10299','Joe','Test','PDO',8.00,'04/06/2015','04/17/2015','Taken')
INSERT INTO ##PDOImport (CompID, ID, FNam, LNam, ReqTyp, ReqHrs, StartDat, EndDat, Sts)
VALUES('N1234','10299','Joe','Test','PDO',8.00,'04/06/2015','04/17/2015','Taken')
INSERT INTO ##PDOImport (CompID, ID, FNam, LNam, ReqTyp, ReqHrs, StartDat, EndDat, Sts)
VALUES('N1234','10299','Joe','Test','PDO',8.00,'04/06/2015','04/17/2015','Taken')
INSERT INTO ##PDOImport (CompID, ID, FNam, LNam, ReqTyp, ReqHrs, StartDat, EndDat, Sts)
VALUES('N1234','10299','Joe','Test','PDO',8.00,'04/06/2015','04/17/2015','Taken')
INSERT INTO ##PDOImport (CompID, ID, FNam, LNam, ReqTyp, ReqHrs, StartDat, EndDat, Sts)
VALUES('N1234','10299','Joe','Test','PDO',8.00,'04/06/2015','04/17/2015','Taken')
INSERT INTO ##PDOImport (CompID, ID, FNam, LNam, ReqTyp, ReqHrs, StartDat, EndDat, Sts)
VALUES('N1234','10299','Joe','Test','PDO',8.00,'04/06/2015','04/17/2015','Taken')
INSERT INTO ##PDOImport (CompID, ID, FNam, LNam, ReqTyp, ReqHrs, StartDat, EndDat, Sts)
VALUES('N1234','10299','Joe','Test','PDO',8.00,'04/06/2015','04/17/2015','Taken')
INSERT INTO ##PDOImport (CompID, ID, FNam, LNam, ReqTyp, ReqHrs, StartDat, EndDat, Sts)
VALUES('N1234','10299','Joe','Test','PDO',8.00,'04/06/2015','04/17/2015','Taken')

CREATE TABLE ##Pay_PDO
(
      CompID nvarchar(10),
      ID nvarchar(12),
      FNam nvarchar(50),
      LNam nvarchar(50),
      ReqTyp nvarchar(5),
      ReqHrs Decimal(12,2),
      StartDat date,
      EndDat date,
      Sts nvarchar(40),
      DateOff date,
      Cnt int,
      WkDay nvarchar(20)
)

Insert ##Pay_PDO(CompID, ID, Fnam, Lnam, ReqTyp, ReqHrs, StartDat, EndDat, Sts, Cnt)
      Select CompId, ID, Fnam, Lnam, Reqtyp, ReqHrs, StartDat, EndDat, Sts,
      Row_Number() Over(Partition by Id, StartDat Order by CompID, ID, StartDat) as Cnt  from ##PDOImport where Sts in ('Approved', 'Submitted', 'Taken')
      Order by LNam, Fnam


Update ##Pay_PDO set DateOff = Startdat, WkDay = DATENAME(dw,StartDat) where Cnt = 1
Update ##Pay_PDO Set DateOff = DateAdd(dd,+(Cnt-1),StartDat), WkDay = DATENAME(dw,(DateAdd(dd,+(Cnt-1),StartDat))) where Cnt = 2 and (DATENAME(dw,(DateAdd(dd,+(Cnt-1),StartDat))) not in ('Saturday', 'Sunday'))  and WKDay is null
Update ##Pay_PDO Set DateOff = DateAdd(dd,+(Cnt-1),StartDat), WkDay = DATENAME(dw,(DateAdd(dd,+(Cnt-1),StartDat))) where Cnt = 3 and (DATENAME(dw,(DateAdd(dd,+(Cnt-1),StartDat))) not in ('Saturday', 'Sunday'))  and WKDay is null
Update ##Pay_PDO Set DateOff = DateAdd(dd,+(Cnt-1),StartDat), WkDay = DATENAME(dw,(DateAdd(dd,+(Cnt-1),StartDat))) where Cnt = 4 and (DATENAME(dw,(DateAdd(dd,+(Cnt-1),StartDat))) not in ('Saturday', 'Sunday'))  and WKDay is null
Update ##Pay_PDO Set DateOff = DateAdd(dd,+(Cnt-1),StartDat), WkDay = DATENAME(dw,(DateAdd(dd,+(Cnt-1),StartDat))) where Cnt = 5 and (DATENAME(dw,(DateAdd(dd,+(Cnt-1),StartDat))) not in ('Saturday', 'Sunday'))  and WKDay is null
Update ##Pay_PDO Set DateOff = DateAdd(dd,+(Cnt-1),StartDat), WkDay = DATENAME(dw,(DateAdd(dd,+(Cnt-1),StartDat))) where Cnt = 6 and (DATENAME(dw,(DateAdd(dd,+(Cnt-1),StartDat))) not in ('Saturday', 'Sunday'))  and WKDay is null
Update ##Pay_PDO Set DateOff = DateAdd(dd,+(Cnt-1),StartDat), WkDay = DATENAME(dw,(DateAdd(dd,+(Cnt-1),StartDat))) where Cnt = 7 and (DATENAME(dw,(DateAdd(dd,+(Cnt-1),StartDat))) not in ('Saturday', 'Sunday'))  and WKDay is null

Select * from ##Pay_PDO
Avatar of dsacker
dsacker
Flag of United States of America image

Does your code have the results you want, but you want a more concise solution?
Avatar of itdeptvnam

ASKER

If you run the code it's skipping the weekend but not placing the next weeks dates in place.
Example Joe Test took off 10 days my code places the monday through Friday in week 1 but not in week 2 in the Field DateOff.

I'm sure their is a better way to do this I just can't think of a way right now. I was hoping for some help on this.
In your data I did not see any dates for next week. However, I did write something a bit concise, although it achieves the same as yours. Are you wanting to "fudge" some new records for the next week, as part of your objective?

Here's a bit of concise-ness:
DECLARE @PDOImport TABLE (
      CompID     varchar(5),
      ID         varchar(6),
      FNam       varchar(50),
      LNam       varchar(50),
      ReqTyp     varchar(5),
      ReqHrs     decimal(12,2),
      StartDat   date,
      EndDat     date,
      Sts        varchar(50) )

SET NOCOUNT ON
INSERT INTO @PDOImport VALUES('N1234','10290','John','Doe','PDO',8.00,'04/08/2015','04/14/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10290','John','Doe','PDO',8.00,'04/08/2015','04/14/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10290','John','Doe','PDO',8.00,'04/08/2015','04/14/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10290','John','Doe','PDO',8.00,'04/08/2015','04/14/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10290','John','Doe','PDO',8.00,'04/08/2015','04/14/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10295','Mary','Test','PDO',8.00,'04/07/2015','04/10/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10295','Mary','Test','PDO',8.00,'04/07/2015','04/10/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10295','Mary','Test','PDO',8.00,'04/07/2015','04/10/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10295','Mary','Test','PDO',8.00,'04/07/2015','04/10/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10299','Joe','Test','PDO',8.00,'04/06/2015','04/17/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10299','Joe','Test','PDO',8.00,'04/06/2015','04/17/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10299','Joe','Test','PDO',8.00,'04/06/2015','04/17/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10299','Joe','Test','PDO',8.00,'04/06/2015','04/17/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10299','Joe','Test','PDO',8.00,'04/06/2015','04/17/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10299','Joe','Test','PDO',8.00,'04/06/2015','04/17/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10299','Joe','Test','PDO',8.00,'04/06/2015','04/17/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10299','Joe','Test','PDO',8.00,'04/06/2015','04/17/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10299','Joe','Test','PDO',8.00,'04/06/2015','04/17/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10299','Joe','Test','PDO',8.00,'04/06/2015','04/17/2015','Taken')

DECLARE @Pay_PDO TABLE  (
      CompID    varchar(10),
      ID        varchar(12),
      FNam      varchar(50),
      LNam      varchar(50),
      ReqTyp    varchar(5),
      ReqHrs    decimal(12,2),
      StartDat  date,
      EndDat    date,
      Sts       varchar(40),
      Cnt       int,
      DateOff   date,
      WkDay     nvarchar(20) ) 

;WITH myCTE AS
(
    SELECT CompId, ID, Fnam, Lnam, Reqtyp, ReqHrs, StartDat, EndDat, Sts,
           
           ROW_NUMBER() OVER (PARTITION BY Id, StartDat ORDER BY CompID, ID, StartDat) AS Cnt
    FROM   @PDOImport
    WHERE  Sts in ('Approved', 'Submitted', 'Taken')
)
INSERT  INTO @Pay_PDO
SELECT  CompId, ID, Fnam, Lnam, Reqtyp, ReqHrs, StartDat, EndDat, Sts, Cnt,
        CASE
            WHEN Cnt = 1 THEN StartDat
            WHEN DATENAME(dw,(DateAdd(dd,+(Cnt-1),StartDat))) IN ('Saturday', 'Sunday') THEN NULL
            ELSE DATEADD(dd,+(Cnt-1),StartDat)
        END     AS DateOff,
        CASE
            WHEN Cnt = 1 THEN DATENAME(dw, StartDat)
            WHEN DATENAME(dw,(DateAdd(dd,+(Cnt-1),StartDat))) IN ('Saturday', 'Sunday') THEN NULL
            ELSE DATENAME(dw,(DateAdd(dd,+(Cnt-1),StartDat)))
        END     AS WkDay
FROM    myCTE

SELECT * FROM @Pay_PDO

Open in new window

Also, I'm playing with this by using @TempTables rather than ##TempTables. ##TempTables are actually global temp tables, which can hang around awhile, if you're not careful.
Thanks for the @TempTables help I'm the only one in the database I usually use # tableName
I've been down that road with another set of code I wrote the problem is its leaving the Dateoff field blank when I really want the empty dates to be Monday, Tuesday etc.
This what I want the end result to be..

CompID      ID      FNam      LNam      ReqTyp      ReqHrs      StartDat      EndDat      Sts      Cnt      DateOff      WkDay
N1234      10290      John      Doe      PDO      8      04/08/2015      04/14/2015      Taken      1      04/08/2015      Wednesday
N1234      10290      John      Doe      PDO      8      04/08/2015      04/14/2015      Taken      2      04/09/2015      Thursday
N1234      10290      John      Doe      PDO      8      04/08/2015      04/14/2015      Taken      3      04/10/2015      Friday
N1234      10290      John      Doe      PDO      8      04/08/2015      04/14/2015      Taken      4      04/13/2015      Monday
N1234      10290      John      Doe      PDO      8      04/08/2015      04/14/2015      Taken      5      04/14/2015      Tuesday
N1234      10295      Mary      Test      PDO      8      04/07/2015      04/10/2015      Taken      1      04/07/2015      Tuesday
N1234      10295      Mary      Test      PDO      8      04/07/2015      04/10/2015      Taken      2      04/08/2015      Wednesday
N1234      10295      Mary      Test      PDO      8      04/07/2015      04/10/2015      Taken      3      04/09/2015      Thursday
N1234      10295      Mary      Test      PDO      8      04/07/2015      04/10/2015      Taken      4      04/10/2015      Friday
N1234      10299      Joe      Test      PDO      8      04/06/2015      04/17/2015      Taken      1      04/06/2015      Monday
N1234      10299      Joe      Test      PDO      8      04/06/2015      04/17/2015      Taken      2      04/07/2015      Tuesday
N1234      10299      Joe      Test      PDO      8      04/06/2015      04/17/2015      Taken      3      04/08/2015      Wednesday
N1234      10299      Joe      Test      PDO      8      04/06/2015      04/17/2015      Taken      4      04/09/2015      Thursday
N1234      10299      Joe      Test      PDO      8      04/06/2015      04/17/2015      Taken      5      04/10/2015      Friday
N1234      10299      Joe      Test      PDO      8      04/06/2015      04/17/2015      Taken      6      04/13/2015      Monday
N1234      10299      Joe      Test      PDO      8      04/06/2015      04/17/2015      Taken      7      04/14/2015      Tuesday
N1234      10299      Joe      Test      PDO      8      04/06/2015      04/17/2015      Taken      8      04/15/2015      Wednesday
N1234      10299      Joe      Test      PDO      8      04/06/2015      04/17/2015      Taken      9      04/16/2015      Thursday
N1234      10299      Joe      Test      PDO      8      04/06/2015      04/17/2015      Taken      10      04/17/2015      Friday
I think this will do it:
DECLARE @PDOImport TABLE (
      CompID     varchar(5),
      ID         varchar(6),
      FNam       varchar(50),
      LNam       varchar(50),
      ReqTyp     varchar(5),
      ReqHrs     decimal(12,2),
      StartDat   date,
      EndDat     date,
      Sts        varchar(50) )

SET NOCOUNT ON
INSERT INTO @PDOImport VALUES('N1234','10290','John','Doe','PDO',8.00,'04/08/2015','04/14/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10290','John','Doe','PDO',8.00,'04/08/2015','04/14/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10290','John','Doe','PDO',8.00,'04/08/2015','04/14/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10290','John','Doe','PDO',8.00,'04/08/2015','04/14/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10290','John','Doe','PDO',8.00,'04/08/2015','04/14/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10295','Mary','Test','PDO',8.00,'04/07/2015','04/10/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10295','Mary','Test','PDO',8.00,'04/07/2015','04/10/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10295','Mary','Test','PDO',8.00,'04/07/2015','04/10/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10295','Mary','Test','PDO',8.00,'04/07/2015','04/10/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10299','Joe','Test','PDO',8.00,'04/06/2015','04/17/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10299','Joe','Test','PDO',8.00,'04/06/2015','04/17/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10299','Joe','Test','PDO',8.00,'04/06/2015','04/17/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10299','Joe','Test','PDO',8.00,'04/06/2015','04/17/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10299','Joe','Test','PDO',8.00,'04/06/2015','04/17/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10299','Joe','Test','PDO',8.00,'04/06/2015','04/17/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10299','Joe','Test','PDO',8.00,'04/06/2015','04/17/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10299','Joe','Test','PDO',8.00,'04/06/2015','04/17/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10299','Joe','Test','PDO',8.00,'04/06/2015','04/17/2015','Taken')
INSERT INTO @PDOImport VALUES('N1234','10299','Joe','Test','PDO',8.00,'04/06/2015','04/17/2015','Taken')

DECLARE @Pay_PDO TABLE  (
      CompID    varchar(10),
      ID        varchar(12),
      FNam      varchar(50),
      LNam      varchar(50),
      ReqTyp    varchar(5),
      ReqHrs    decimal(12,2),
      StartDat  date,
      EndDat    date,
      Sts       varchar(40),
      Cnt       int,
      DateOff   date,
      WkDay     nvarchar(20) ) 

;WITH myCTE AS
(
    SELECT CompId, ID, Fnam, Lnam, Reqtyp, ReqHrs, StartDat, EndDat, Sts,
           
           ROW_NUMBER() OVER (PARTITION BY Id, StartDat ORDER BY CompID, ID, StartDat) AS Cnt
    FROM   @PDOImport
    WHERE  Sts in ('Approved', 'Submitted', 'Taken')
)
INSERT  INTO @Pay_PDO
SELECT  CompId, ID, Fnam, Lnam, Reqtyp, ReqHrs, StartDat, EndDat, Sts, Cnt,
        CASE
            WHEN Cnt = 1 THEN StartDat
            ELSE DATEADD(dd,+(Cnt-1),StartDat)
        END     AS DateOff,
        CASE
            WHEN Cnt = 1 THEN DATENAME(dw, StartDat)
            ELSE DATENAME(dw,(DATEADD(dd,+(Cnt-1),StartDat)))
        END     AS WkDay
FROM    myCTE
WHERE   DATENAME(dw,(DATEADD(dd,+(Cnt-1),StartDat))) NOT IN ('Saturday', 'Sunday')

SELECT * FROM @Pay_PDO

Open in new window

It looks good but it's deleting the records for Saturday and Sunday I just need it to stop on the Saturday and Sunday and insert Monday's date and go on from there, I would be like shifting the dates up by 2 places when it hits the weekend.
I'm just thinking if I did a count on the day's in the date range if there was a weekend within that range then i could insert 2 extra records and your code would work perfectly.
ASKER CERTIFIED SOLUTION
Avatar of dsacker
dsacker
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Actually, the two NULL records for Saturday and Sunday can simply be converted to Monday and Tuesday by changing Cnt-1 to Cnt+1. :)
Works perfect Thank you,
The multiple input rows coming in actually makes this harder to do.  Are you purposely generating those rows in your processing or are you stuck with that coming in?
I'm stuck with whats coming in.. very poor reporting by the Payroll vendor