We help IT Professionals succeed at work.

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
Comment
Watch Question

dsackerContract ERP Admin/Consultant

Commented:
Does your code have the results you want, but you want a more concise solution?

Author

Commented:
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.
dsackerContract ERP Admin/Consultant

Commented:
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.

Author

Commented:
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.

Author

Commented:
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
dsackerContract ERP Admin/Consultant

Commented:
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

Author

Commented:
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.

Author

Commented:
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.
Contract ERP Admin/Consultant
Commented:
Something like this? :)
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 DATEADD(dd, Cnt+1, StartDat)
            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 DATENAME(dw,(DATEADD(dd,Cnt+1,StartDat)))
            ELSE DATENAME(dw,(DATEADD(dd,+(Cnt-1),StartDat)))
        END     AS WkDay
FROM    myCTE

SELECT * FROM @Pay_PDO

Open in new window

dsackerContract ERP Admin/Consultant

Commented:
Actually, the two NULL records for Saturday and Sunday can simply be converted to Monday and Tuesday by changing Cnt-1 to Cnt+1. :)

Author

Commented:
Works perfect Thank you,
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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?

Author

Commented:
I'm stuck with whats coming in.. very poor reporting by the Payroll vendor