SQLTeam.com | Weblogs | Forums

Slab System


#1

Hi,

I have slab system. for period of one year.

1- 0 to 14 .................. 0 percent ded
2- 15 to 29 .................. 25 percent ded
3- 30 to 44 .................. 50 percent ded
4- 45 to 59 .................. 75 percent ded
5- 60 to 74 .................. 75 percent ded

if employee absent more than 14 days then start deduction.

for example if employee absent 30 days

then apply slab. for 1st 14 days is no deduction, next 14 days 25 percent deduction and next 2 days 50 percent deduction.

below is example.

1- 0 to 14 (0%) .................. no deduction
2- 15 to 29 (25%) .................. 14 days
3- 30 to 44 (50%) .................. 14 days
4- 45 to 59 (75%) .................. 2 days
5- 60 to 74 (100%) .................. 0

i was thinking having a table whatever deduction then add in the tables.

Absent_No - numeric
Absent_EmployeeFromDate --date
Absent_EmployeeToDate --date
Absent_DeductionDays - numeric
Absent_SlabNo - numeric
Absent_EmployeeNo -numeric

but in next time if employee absent once again absent 15 days.

if i search the date in attendance then i will get total 30 before absent and now 15 days. (total 45 days)

then slab will apply below.

1- 0 to 14 (0%) .................. 0 percent ded
2- 15 to 29 (25%) .................. 14 days
3- 30 to 44 (50%) .................. 14 days
4- 45 to 59 (75%) .................. 2 already deduc + now 12 days deduction as 75%
5- 60 to 74 (100%) .................. 3 deduction as 100% Days

how to do this, please help on this.

also note that the no. of absent will come from Attenance Table.

Thanks
Basit


#2

Hi

One idea is to do normalisation

2 to 3 tables

Slab as one table
From date to date another table
Etc

Please let me know if this helps
:blush::blush:


#3

Thanks for the reply.
how how can i do this.

i already have two table structure which i mention in above.

One Slab Table and one is below

Absent_No - numeric
Absent_EmployeeFromDate --date
Absent_EmployeeToDate --date
Absent_DeductionDays - numeric
Absent_SlabNo - numeric --- reference key from Slab Master.
Absent_EmployeeNo -numeric

Thanks
Basit.


#4

Hi

Concept is to be able to identify each row
Uniquely. That is primary key for one table and all the other columns into another table with foreign key ..join primary key and foreign key

Slab table table 1

For example table 2
Absent no column primary key
If it can uniquely identify each row
From date column
To date column

Table 3
Absent no ,,,,,again ,,,foreign key
Absent_DeductionDays - numeric
Absent_SlabNo - numeric --- reference key from Slab Master.
Absent_EmployeeNo -numeric

Idea something like this

I hope it is useful to you


#5

The way you do the tables etc

Depends on what you want to do


#6

Many Many Thanks for your reply.

Yes in Table each row is having unique key. Absent_No.

I was trying through vb.net code.
but no success.

Private Sub Frm_Slab(ByVal LdbM_TotalDays As Double, ByVal LdbM_AlreadyDedDays As Double)

    Dim LdbM_14to29 As Double
    Dim LdbM_30to44 As Double
    Dim LdbM_45to59 As Double
    Dim LdbM_60to74 As Double


    Select Case LdbM_TotalDays  'example days = 44

        'Note that each slab have 14 days different

        Case 0 To 14 ' no deduction

        Case 15 To 29  '25%  14 days dedcuct here

            'This value will save in database

        Case 30 To 44  '50%  14 day ded here

            'This value will save in database

        Case 45 To 59 '75% 2 days ded here

            'This value will save in database

        Case 60 To 74  '100%

            'This value will save in database

    End Select

End Sub

Thanks
Basit.


#7

hi basit

do you want me to work on this ??? :slight_smile:
please let me know

I can try !!!


#8

Many Thanks. I solved the problem.

Once again thanks for your help.

Thanks
Basit.