We help IT Professionals succeed at work.
Get Started

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

64 Views
Last Modified: 2015-04-17
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
Contract ERP Admin/Consultant
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 13 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE