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
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
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.
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
The way you do the tables etc
Depends on what you want to do
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.
hi basit
do you want me to work on this ??? 
please let me know
I can try !!!
1 Like
Many Thanks. I solved the problem.
Once again thanks for your help.
Thanks
Basit.
hi basit0079
this topic is old and you have solved it
just for practice I have taken it up
please review if you are interested
thanks
:-
i have created three tables
-
attendance
-
deductions.. main table ...not populated..only wrote sql to populate
-
slab
drop create data
use tempdb
go
drop table slab
go
create table slab
(
rowid int ,
from1 int ,
to1 int ,
slab_percent int
)
go
insert into slab select 1,0 ,14 , 0
insert into slab select 2,15, 29, 25
insert into slab select 3,30, 44, 50
insert into slab select 4,45, 59, 75
insert into slab select 5,60, 74, 75
go
drop table deductions
go
create table deductions
(
Absent_No int ,
Absent_EmployeeFromDate date ,
Absent_EmployeeToDate date ,
Absent_DeductionDays int ,
Absent_SlabNo int ,
Absent_EmployeeNo int
)
go
drop table attendance
go
create table attendance
(
emp_no int ,
date_attendance date
)
go
insert into attendance select 1,'2018-09-14'
insert into attendance select 1,'2018-09-15'
insert into attendance select 1,'2018-09-16'
insert into attendance select 1,'2018-09-17'
insert into attendance select 1,'2018-10-14'
insert into attendance select 1,'2018-10-15'
insert into attendance select 1,'2018-10-16'
insert into attendance select 1,'2018-10-17'
insert into attendance select 2,'2018-09-14'
insert into attendance select 2,'2018-08-15'
insert into attendance select 2,'2018-08-16'
insert into attendance select 2,'2018-09-05'
insert into attendance select 2,'2018-10-14'
insert into attendance select 2,'2018-10-15'
insert into attendance select 2,'2018-10-16'
insert into attendance select 2,'2018-10-17'
go
SQL
;WITH attctern
AS (SELECT Row_number()
OVER (
partition BY emp_no
ORDER BY date_attendance) AS rn,
*
FROM attendance),
daysabsentcte
AS (SELECT a.rn AS arn,
a.emp_no AS aemp_no,
a.date_attendance
adate_attendance,
b.rn AS brn,
b.emp_no bemp_no,
b.date_attendance
bdate_attendance,
Datediff(day, b.date_attendance, a.date_attendance) AS days
FROM attctern a
JOIN attctern b
ON a.emp_no = b.emp_no
AND a.rn = b.rn + 1)
SELECT a.*,
b.*
FROM daysabsentcte a
JOIN slab b
ON a.days BETWEEN b.from1 AND b.to1
Results
