Payroll_problem_SQL/Daily_Attendence_Master_A2.sql at master · anik112/Payroll_problem_SQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
SELECT info.cardno,info.empname,info.lineno,mapp.intime,mapp.outtime, mapp.duration,mapp.latestatus,info.departmentnm,
              info.sectionnm, info.shift,  info.joining_date,  info.designation, mapp.latehr, mapp.earlyhr, (mapp.otmin/60)othr,
              mapp.earlystatus, mapp.isln, mapp.lateded, mapp.earlyded, mapp.tf
FROM  tb_data_master mapp,tb_personal_info info
WHERE  info.company   = :p_company
AND    mapp.company    = :p_com
AND    mapp.finyear        = TO_CHAR(TO_DATE(:p_date - 1,'dd/mm/rrrr'),'RRRR')
AND    mapp.finmonth     = RTRIM(TO_CHAR(TO_DATE(:p_date - 1,'dd/mm/rrrr'),'Month'))
AND    mapp.pdate         = (:p_date - 1)
AND    info.joining_date  <=  (:p_date -  1)
AND    info.cardno          = mapp.cardno
AND    info.cardno IN ( SELECT INFO.CARDNO
                                     FROM   tb_personal_info info
                                     WHERE  info.company  = :p_company
                                     AND    info.active           = 0
                                     AND    info.cardno NOT IN (SELECT mapp.CARDNO FROM  tb_data_master mapp
						   WHERE  mapp.company  = :p_com
						   AND    mapp.finyear          = TO_CHAR(:p_date,'RRRR')
						   AND    mapp.finmonth       = RTRIM(TO_CHAR(:p_date,'Month'))
						   AND    mapp.pdate            = :p_date
						   )
                                     AND    info.departmentnm      LIKE DECODE(NVL(:p_dept,'all'),'all','%',:p_dept)
                                     AND    info.sectionnm            LIKE DECODE(NVL(:p_section,'all'),'all','%',:p_section)
                                     AND    info.designation          LIKE DECODE(NVL(:p_designation,'all'),'all','%',:p_designation)
                                     AND    info.workertype           LIKE DECODE(NVL(:p_woker,'all'),'all','%',:p_woker)
                                     AND    info.floorno                 LIKE DECODE(NVL(:p_floorno,'all'),'all','%',:p_floorno)
                                     AND    info.machineno           LIKE DECODE(NVL(:p_machineno,'all'),'all','%',:p_machineno)
                                     AND    info.lineno                   LIKE DECODE(NVL(:p_line,'all'),'all','%',:p_line)
                                     AND    info.shift                      LIKE DECODE(NVL(:p_shift,'all'),'all','%',:p_shift)
                                     AND    info.gender                 LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
                                     )
AND    info.departmentnm      LIKE DECODE(NVL(:p_dept,'all'),'all','%',:p_dept)
AND    info.sectionnm            LIKE DECODE(NVL(:p_section,'all'),'all','%',:p_section)
AND    info.designation          LIKE DECODE(NVL(:p_designation,'all'),'all','%',:p_designation)
AND    info.workertype           LIKE DECODE(NVL(:p_woker,'all'),'all','%',:p_woker)
AND    info.floorno                 LIKE DECODE(NVL(:p_floorno,'all'),'all','%',:p_floorno)
AND    info.machineno           LIKE DECODE(NVL(:p_machineno,'all'),'all','%',:p_machineno)
AND    info.lineno                   LIKE DECODE(NVL(:p_line,'all'),'all','%',:p_line)
AND    info.shift                      LIKE DECODE(NVL(:p_shift,'all'),'all','%',:p_shift)
AND    info.gender                 LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND    info.cardno                 LIKE DECODE(NVL(:p_card,'all'),'all','%',:p_card)
ORDER BY info.lineno,info.cardno ASC


-----------------------


SELECT info.cardno,info.empname,info.lineno,mapp.intime,mapp.outtime, mapp.duration,mapp.latestatus,info.departmentnm,
              info.sectionnm, info.shift,  info.joining_date,  info.designation, mapp.latehr, mapp.earlyhr, (mapp.otmin/60)othr,
              mapp.earlystatus, mapp.isln, mapp.lateded, mapp.earlyded, mapp.tf
FROM  tb_data_master mapp,tb_personal_info info
WHERE  info.company   = :p_company
AND    mapp.company    = :p_com
AND    mapp.finyear        = TO_CHAR(TO_DATE(:p_date - 1,'dd/mm/rrrr'),'RRRR')
AND    mapp.finmonth     = RTRIM(TO_CHAR(TO_DATE(:p_date - 1,'dd/mm/rrrr'),'Month'))
AND    mapp.pdate         = (:p_date + 1)
AND    info.joining_date  <=  (:p_date +  1)
AND    info.cardno          = mapp.cardno
AND    info.cardno IN ( SELECT INFO.CARDNO
                                     FROM   tb_personal_info info
                                     WHERE  info.company  = :p_company
                                     AND    info.active           = 0
                                     AND    info.cardno NOT IN (SELECT mapp.CARDNO FROM  tb_data_master mapp
						   WHERE  mapp.company  = :p_com
						   AND    mapp.finyear          = TO_CHAR(:p_date,'RRRR')
						   AND    mapp.finmonth       = RTRIM(TO_CHAR(:p_date,'Month'))
						   AND    mapp.pdate            = :p_date
						   )
                                     AND    info.departmentnm      LIKE DECODE(NVL(:p_dept,'all'),'all','%',:p_dept)
                                     AND    info.sectionnm            LIKE DECODE(NVL(:p_section,'all'),'all','%',:p_section)
                                     AND    info.designation          LIKE DECODE(NVL(:p_designation,'all'),'all','%',:p_designation)
                                     AND    info.workertype           LIKE DECODE(NVL(:p_woker,'all'),'all','%',:p_woker)
                                     AND    info.floorno                 LIKE DECODE(NVL(:p_floorno,'all'),'all','%',:p_floorno)
                                     AND    info.machineno           LIKE DECODE(NVL(:p_machineno,'all'),'all','%',:p_machineno)
                                     AND    info.lineno                   LIKE DECODE(NVL(:p_line,'all'),'all','%',:p_line)
                                     AND    info.shift                      LIKE DECODE(NVL(:p_shift,'all'),'all','%',:p_shift)
                                     AND    info.gender                 LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
                                     )
AND    info.departmentnm      LIKE DECODE(NVL(:p_dept,'all'),'all','%',:p_dept)
AND    info.sectionnm            LIKE DECODE(NVL(:p_section,'all'),'all','%',:p_section)
AND    info.designation          LIKE DECODE(NVL(:p_designation,'all'),'all','%',:p_designation)
AND    info.workertype           LIKE DECODE(NVL(:p_woker,'all'),'all','%',:p_woker)
AND    info.floorno                 LIKE DECODE(NVL(:p_floorno,'all'),'all','%',:p_floorno)
AND    info.machineno           LIKE DECODE(NVL(:p_machineno,'all'),'all','%',:p_machineno)
AND    info.lineno                   LIKE DECODE(NVL(:p_line,'all'),'all','%',:p_line)
AND    info.shift                      LIKE DECODE(NVL(:p_shift,'all'),'all','%',:p_shift)
AND    info.gender                 LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND    info.cardno                 LIKE DECODE(NVL(:p_card,'all'),'all','%',:p_card)
ORDER BY info.lineno,info.cardno ASC