January 31, 2015 at 7:43 pm
I need assistance with update query. Not sure where I am missing the logic as I am unable to get the desired results.
I have three tables.
EmployeeMaster
Timesheets
LeaveHoursEarned
I need to update HoursEarned in the LeaveHoursEarned Table when the LeaveType is Vacation and TimesheetType is Regular based on the length of Service. Which is HireDate minus PayDate
During payroll processing Timesheets are entered in Timesheets Table and HoursEarned needs to be updated based on the length of service. Basically if an employee has been with the company 2 years or less (based on the Hire Date in Employee Master and PayDate in Timesheets) the HoursEarned needs to be updated with a value 2. If it is between 2 and 5 years then HoursEarned should be 3 and if more then 5 years then 4.
**************************************************************************
update leavehoursearned
set HoursEarned =
(SELECT
case when DATEDIFF(year,employeemaster.HireDate,Timesheets.paydate ) <= 2 then 2
when DATEDIFF(year,employeemaster.HireDate,Timesheets.paydate ) > 2 and
DATEDIFF(year,employeemaster.HireDate,Timesheets.paydate ) <= 5 then 3
when DATEDIFF(year,employeemaster.HireDate,Timesheets.paydate ) > 5 then 4
else 0 end
from timesheets
join employeemaster on employeemaster.EmployeeID = TimeSheets.EmployeeID )
where LeaveType = 'Vacation' and TimesheetId in (select TimeSheetID from TimeSheets where TimesheetType = 'Regular')
********************************************************************
Below are the three tables with sample data. Thanks for your assistance.
CREATE TABLE [dbo].[TimeSheets](
[TimeSheetID] [int] NOT NULL,
[EmployeeID] [varchar](12) NOT NULL,
[PayDate] [date] NOT NULL,
[TimeSheetType] [char](12) NOT NULL,
CONSTRAINT [PK_TimeSheets] PRIMARY KEY CLUSTERED
(
[TimeSheetID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[LeaveHoursEarned](
[TimeSheetID] [int] NOT NULL,
[LeaveType] [char](10) NOT NULL,
[HoursEarned] [float] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[EmployeeMaster](
[EmployeeID] [varchar](12) NOT NULL,
[HireDate] [date] NOT NULL,
CONSTRAINT [PK_EmployeeMaster] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
insert into dbo.[TimeSheets] values(1,'ADAMS','1/15/2015','REGULAR')
insert into dbo.[TimeSheets] values(2,'ADAMS','1/15/2015','SPECIAL')
insert into dbo.[TimeSheets] values(3,'FLORES','1/15/2015','ONETIME')
insert into dbo.[TimeSheets] values(4,'JAMES','1/15/2015','REGULAR')
insert into dbo.[TimeSheets] values(5,'JONES','1/15/2015','REGULAR')
insert into dbo.[TimeSheets] values(6,'JONES','1/15/2015','SPECIAL')
insert into dbo.[TimeSheets] values(7,'TROY','1/15/2015','REGULAR')
insert into dbo.[TimeSheets] values(8,'MILLS','1/15/2015','REGULAR')
insert into dbo.[EMPLOYEEMASTER] values('ADAMS','5/5/2011')
insert into dbo.[EMPLOYEEMASTER] values('FLORES','7/7/2001')
insert into dbo.[EMPLOYEEMASTER] values('JAMES','2/12/2010')
insert into dbo.[EMPLOYEEMASTER] values('JONES','5/6/2014')
insert into dbo.[EMPLOYEEMASTER] values('MILLS','12/11/2008')
insert into dbo.[EMPLOYEEMASTER] values('TROY','2/3/2009')
INSERT INTO DBO.[LeaveHoursEarned] VALUES(1,'Sick',5)
INSERT INTO DBO.[LeaveHoursEarned] VALUES(1,'Vacation',0)
INSERT INTO DBO.[LeaveHoursEarned] VALUES(2,'Sick',7)
INSERT INTO DBO.[LeaveHoursEarned] VALUES(4,'Vacation',0)
INSERT INTO DBO.[LeaveHoursEarned] VALUES(4,'CompTime',4)
INSERT INTO DBO.[LeaveHoursEarned] VALUES(5,'Vacation',0)
INSERT INTO DBO.[LeaveHoursEarned] VALUES(7,'Vacation',0)
INSERT INTO DBO.[LeaveHoursEarned] VALUES(8,'Vacation',0)
February 1, 2015 at 7:46 am
Homework?
Post what you have tried for your answer and we can give some pointers. It does you (and the industry) no good at all if we do this for you, but you can learn a TON with a little guidance. But you have to do the work...
February 1, 2015 at 2:34 pm
I have added the query that I had created but it is giving me errors.
Thanks
February 1, 2015 at 3:59 pm
"giving me errors" is just means it doesn't work. How about providing the error message?
February 1, 2015 at 6:00 pm
The query errors out stating the update query generated multiple rows. I understand the update query needs to generate a single value - but not sure how to change the script.
February 1, 2015 at 7:06 pm
Sounds to me like you should do the whole thing in a stored procedure. If vacation hours is completely dependent on hours worked (so, a timesheet entry), then why not just add the logic to the stored procedure? Just update the other table when you're doing the insert.
February 1, 2015 at 7:46 pm
actually no they are not dependent on hours worked. They are dependent on how long a person has been with the company and only earned if there is a timesheet type Regular.
Thanks
February 1, 2015 at 7:55 pm
nfpacct (2/1/2015)
I have added the query that I had created but it is giving me errors.Thanks
Sorry, didn't see that part of your post.
It's complaining about your SET = having more than 1 value in the right side of the equal sign. If you run your select statement separately, you'll see that it returns 8 values. You need to make sure that only one value is set for each row.
Try using the FROM clause in the UPDATE statement instead of a select. The right side of the SET = should be your CASE statement, but you need to make the join clause filter the rows for you.
February 2, 2015 at 6:33 am
Brian Hibbert (2/1/2015)
Try using the FROM clause in the UPDATE statement instead of a select. The right side of the SET = should be your CASE statement, but you need to make the join clause filter the rows for you.
Also the sample data does not show multiple dates for employee/timesheet (regular) combinations which surely must occur in the real data.
This will have to be catered for in the query and would need a subquery or CTE unless a specific date can be used
Far away is close at hand in the images of elsewhere.
Anon.
February 2, 2015 at 6:54 am
Your query isn't properly formed, the subquery (timesheets join employeemaster) isn't correlated with the UPDATE target leavehoursearned.
Start by writing a SELECT query which correctly joins all three tables and returns the keys of all three so you can check that the joins are correct, and also the old and new values for HoursEarned from table leavehoursearned. Then convert the query to an UPDATE. If you come unstuck at this point, post back.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply