Referrence in SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Referrence in SQL

Author

Topic

vinoth
Starting Member

14 Posts

Posted - 2008-10-03 : 00:10:51

Hi,

I had a doubt in calculating staff salary.i have to generate salary for all staff by accessing staffid from master table,no of present from attandance table,no of working days from another table,no of days permitted from permission table. i have to calculate salary for all staff.i expecting ur answer soon.

Regards,

Vinoth

vinoth

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-03 : 00:14:02

where's salary details held? also provide some sample data and utput you expect out of them

vinoth
Starting Member

14 Posts

Posted - 2008-10-03 : 04:05:27

Hi,

Salary will be maintained in salary table.for eg.if staffid 'aaa'is present for 23 days out of 25 days,we have to calculate how many days management granted leave and then we have to calculate salary.i am new i hve lot of error in it.please provide me with queries and stored procedure

Thanks

vinoth

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-03 : 04:09:27

Unless you provide details about the data and output you expect it will be hard to give a solution. please remember that we dont have any access to your system and dont know how your data is.

vinoth
Starting Member

14 Posts

Posted - 2008-10-03 : 04:14:01

Attendance Table
totalno of days noofworkingdays noofpredays noofabsentdays staffid
30 25 24 1 2
30 24 20 4 1

Master Table
id staffid staffname handlingsub

vinoth

vinoth
Starting Member

14 Posts

Posted - 2008-10-03 : 04:17:32

Master Table
id staffid staffname handlingsub classallot sectionallot date
1 cs1 a eng bsc b 1900-01-01 00:00:00.000
2 cs1 b eng bsc a 1900-01-01 00:00:00.000

id staffid staffname salary salarytype
1 1 a 10000 consolidate
2 2 b 20000 consolidate

staffid staffname morningsession afternoonsession date dayspresent

2 kumar pre pre 2008-10-04 00:00:00.000 1
2 kumar pre abs 2008-10-05 00:00:00.000 1
2 kumar pre abs 2008-10-06 00:00:00.000 0.5

vinoth

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-03 : 04:31:46

why same field has different types of values in different tables? staffid has values like cs1 in one table whereas in other its nueric 1,2... can you confirm this is how its actually present?

vinoth
Starting Member

14 Posts

Posted - 2008-10-03 : 05:32:16

id staffid staffname handlingsub classallot sectionallot date
2 2 b cs1 eng a 1900-01-01 00:00:00.000
1 2 b cs1 eng a 1900-01-01 00:00:00.000

vinoth

vinoth
Starting Member

14 Posts

Posted - 2008-10-03 : 05:35:23

Hi visakh,

I really confused to perform these calculation.Expecting help from expert like u.waiting for ur solution

vinoth

vinoth
Starting Member

14 Posts

Posted - 2008-10-03 : 05:36:43

how can create stored procedure to perform these functions to calculate attendance and salary.

vinoth

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-03 : 06:01:02

something like below

CREATE PROC GetEmployeeDetails
@StaffName varchar(100)
AS

SELECT m.Staffname,DATENAME(mm,MonthDate) + DATENAME(yyyy,MonthDate),
a.TotalDays,s.Salary
FROM Master m
INNER JOIN (SELECT staffid,DATEADD(mm,DATEDIFF(mm,0,date),0) AS MonthDate,SUM(dayspresent) AS TotalDays
            FROM <Unknowtable>
            GROUP BY staffid,DATEADD(mm,DATEDIFF(mm,0,date),0)) a
ON a.Staffid=m.Staffid
INNER JOIN Salary s
ON s.Staffid=m.Staffid
INNER JOIN Attendence t
on t.Staffid=m.staffid
WHERE m.Staffname=@Staffname
GO

i dont know name of table so have put unknown table for it. replace it with actual name. also dont know where the info for management granted leaves are held so havent included claculation for salary based on it.

vinoth
Starting Member

14 Posts

Posted - 2008-10-03 : 06:10:58

K thanks can i give complete table information please help to forward step.shall send complete tables

vinoth

vinoth
Starting Member

14 Posts

Posted - 2008-10-03 : 06:20:16

Hi

Shall i send all the table information, can u help me to write stored procedure for calculating attendance and salary. please

vinoth

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-03 : 06:21:27

quote:
Originally posted by vinoth

K thanks can i give complete table information please help to forward step.shall send complete tables

vinoth

Nope. first try yourself taking my code as a start pointer. And post if you find any doubt or get stuck with any issues. Dont expect somebody else to provide you whole solution. Otherwise you wont learn and understand it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources