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
itdeptvnamAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
Does your code have the results you want, but you want a more concise solution?
0
itdeptvnamAuthor 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.
0
dsackerContract ERP Admin/ConsultantCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

itdeptvnamAuthor 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.
0
itdeptvnamAuthor 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
0
dsackerContract ERP Admin/ConsultantCommented:
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

0
itdeptvnamAuthor 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.
0
itdeptvnamAuthor 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.
0
dsackerContract ERP Admin/ConsultantCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dsackerContract ERP Admin/ConsultantCommented:
Actually, the two NULL records for Saturday and Sunday can simply be converted to Monday and Tuesday by changing Cnt-1 to Cnt+1. :)
0
itdeptvnamAuthor Commented:
Works perfect Thank you,
0
Scott PletcherSenior DBACommented:
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?
0
itdeptvnamAuthor Commented:
I'm stuck with whats coming in.. very poor reporting by the Payroll vendor
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.