AboutPricingCommunityTeamsStart Free TrialLog in
Avatar of itdeptvnam
itdeptvnam
Flag 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
Microsoft SQL ServerMicrosoft SQL Server 2005
Avatar of undefined
Last Comment
itdeptvnam
8/22/2022 - Mon
dsacker
Does your code have the results you want, but you want a more concise solution?
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.
dsacker
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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
itdeptvnam
ASKER
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.
itdeptvnam
ASKER
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
dsacker
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

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
itdeptvnam
ASKER
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.
itdeptvnam
ASKER
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
dsacker
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
dsacker
Actually, the two NULL records for Saturday and Sunday can simply be converted to Monday and Tuesday by changing Cnt-1 to Cnt+1. :)
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
itdeptvnam
ASKER
Works perfect Thank you,
Scott Pletcher
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?
itdeptvnam
ASKER
I'm stuck with whats coming in.. very poor reporting by the Payroll vendor
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Plans and Pricing
Resources
Product
Podcasts
Careers
Contact Us
Teams
Certified Expert Program
Credly Partnership
Udemy Partnership
Privacy Policy
Terms of Use

© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent