Employee Spend overtime increse salary double then norma sa

venkatesh duddukuri

10y1.1kSQL

Hai to All.....

Write a Stored procedure in SQL Employee working more then 8 howers. Every one hower this

Salary is double than normal howers (Daily working howers is 8 howers)

create three tables: 1.Shifts,2.employee,3.timesheet

Insert into shift values(emp_code int,shift_name varchar(),shift_time time);

Insert into employee values(emp_code int,emp_name varchar(),grade varchar(),dept varchar(), salary int);

Insert into timesheet values(emp_code int,date _in date,time_in time,time_out time,date_out date);

Using above Three table and write SP

Example:

Per day normal working howers is :8 howers

per howers salary 100 rupes

Daily salary =8*100=800

Spend extra 1 hower =1*100 (normal salary)+salary double

=1*100+100=200

Spend extra 2 howers=2*100 (normal salary)+salary double

=2*100=200+200(salary double)=400

Plz Send Stored procedure in SQL ((Using above Three Tables))

Thanks and regard's

Venkatesh D

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

venkatesh duddukuri10y ago
Good Morning Praveen sar

Please send any syntax of that problum

Thanks and regard's

Venkatesh D
0
venkatesh duddukuri10y ago
Thanks Praveen Gi
I Will try my do level best and try some one Plz checked(I use one table only)
I have no idea for using three tables
Note:write a Program In Stored procedure in SQL

create procedure empsalary
(
@empcode varchar(60)
)
as
begin
declare @working_hours int
declare @fixed_salary_hour float
declare @extra_time int
declare @extra_salary float
declare @time int
declare @salary float
select @working_hours=DATEDIFF(hour,timein,timeout) from timesheet where
empcode=@empcode
if @working_hours<=8
begin
select @salary=@working_hours*30
print @salary
end
else if @working_hours>8
begin
select @extra_time=@working_hours-8
select @extra_salary=(@extra_time*60)
print @extra_salary
end
end

0
Praveen Kumar10y ago
Read this and many more article like this.

http://www.c-sharpcorner.com/blogs/sql-for-beginners-joins
0
Praveen Kumar10y ago
You wrote "i can't understand how to write SP."

Try to learn. IF somebody tell you the solution then how do you learn. Learn about different types of joins to know how to extract data from many tables.

Try to improve your skill to have a better future. I think you are student now. Practice hard to develop logics which will help you when you look for jobs.
0
venkatesh duddukuri10y ago
Thanks so much Praveen gi

But i know only basic of SP in SQL and i will try my self but using three tables i can't under stand how to write SP.

Thanks and regard's
Venkatesh D
0
Praveen Kumar10y ago
Try it yourself first. Improve skill by solving problems yourself.

And after that if you are stuck anywhere you can ask members.
+1