API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jun 18th, 2019
54
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 11.14 KB | None | 0 0
raw download clone embed print report
  1. DECLARE @EmployeePayrollDataTable AS TABLE (EmployeeId int,LegalOverTime int,ExtraOverTime int,ToatalOverTime int,OK int,LateDays int,
  2. AbsentDays int, LeaveDays int, HolidayOffdays int,BasicPay decimal,GrossPay decimal,HouseRent decimal,MedicalFoodTransportCost decimal,
  3. BasicPayRate decimal,GrossPayRate decimal,OverTimeRate float,AttendanceBonus decimal,StampDeduction decimal);
  4.  
  5. DECLARE @EmployeePayrollTable AS TABLE (EmployeeId int,BasicSalary decimal,HouseRent decimal,MedicalFoodTransportCost decimal,GrossSalary decimal, TotalPresents int,
  6. HolidayOffdays int, LeaveDays int,AbsentDays int, TotalDeduction decimal, TotalSalary decimal, AttendanceBonus decimal, OvertimeHours int, OvertimeRate float,
  7. OvertimeAmount decimal, StampCharge decimal, FinalSalary decimal)
  8.  
  9. INSERT INTO @EmployeePayrollDataTable
  10. SELECT A.EmployeeId,A.LegalOverTime,A.ExtraOverTime,A.ToatalOverTime,A.OK,A.LateDays,A.AbsentDays,A.LeaveDays,A.HolidayOffdays,
  11. B.BasicPay,B.GrossPay,B.HouseRent,B.MedicalFoodTransportCost,B.BasicPayRate,B.GrossPayRate,B.OverTimeRate,B.AttendanceBonus,B.StampDeduction FROM
  12. (SELECT TT.EmployeeId,
  13. SUM(TT.LegalOverTime) AS LegalOverTime,
  14. SUM(TT.ExtraOverTime) AS ExtraOverTime,
  15. SUM(TT.LegalOverTime)+SUM(TT.ExtraOverTime) AS ToatalOverTime,
  16. ISNULL(COUNT(OK), 0 ) OK,
  17. ISNULL(COUNT(LateDays), 0 ) LateDays,
  18. ISNULL(COUNT(AbsentDays), 0 ) AbsentDays,
  19. ISNULL(COUNT(LeaveDays), 0 ) LeaveDays,
  20. ISNULL(COUNT(HolidayOffdays), 0 ) HolidayOffdays
  21. FROM
  22. (SELECT HAH.EmployeeId,
  23. CASE WHEN HAH.AttendanceStatus!=5 OR HAH.AttendanceStatus!=6 THEN
  24. CASE WHEN HAH.PayableOverTime<=2 THEN HAH.PayableOverTime
  25. WHEN HAH.PayableOverTime>2 THEN 2
  26. ELSE 0
  27. END
  28. ELSE 0
  29. END AS LegalOverTime,
  30.  
  31. CASE WHEN HAH.AttendanceStatus!=5 OR HAH.AttendanceStatus!=6 THEN
  32. CASE WHEN HAH.PayableOverTime<=2 THEN 0
  33. WHEN HAH.PayableOverTime>2 THEN HAH.PayableOverTime-2
  34. ELSE 0
  35. END
  36. ELSE HAH.PayableOverTime
  37. END AS ExtraOverTime,
  38. CASE WHEN
  39. (AttendanceStatus=1 and HAH.[Status]=1) THEN AttendanceStatus END AS OK,
  40. CASE WHEN
  41. (AttendanceStatus=2 and HAH.[Status]=1) THEN AttendanceStatus END AS LateDays,
  42. CASE WHEN
  43. (AttendanceStatus=3 and HAH.[Status]=1) THEN AttendanceStatus END AS AbsentDays,
  44. CASE WHEN
  45. (AttendanceStatus=4 and HAH.[Status]=1) THEN AttendanceStatus END AS LeaveDays,
  46. CASE WHEN
  47. ((AttendanceStatus=5 or AttendanceStatus=6) and HAH.[Status]=1) THEN AttendanceStatus END AS HolidayOffdays
  48.  
  49. FROM HRMS_Attendance_History as HAH
  50. JOIN HRMS_Employee HE on HAH.EmployeeId=HE.ID
  51. WHERE HE.Present_Status=1 and CAST(HE.Joining_Date as Date)<='2019-04-01' and CAST([Date] as Date)>='2019-04-01' and CAST([Date] as Date)<='2019-04-30') TT
  52. GROUP BY TT.EmployeeId) A
  53. JOIN
  54. (SELECT
  55. T.EmployeeId,
  56. ISNULL(SUM(BasicPay), 0 ) BasicPay,
  57. ISNULL(SUM(GrossPay), 0 ) GrossPay,
  58. ISNULL(SUM(HouseRent), 0 ) HouseRent,
  59. ISNULL(SUM(MedicalFoodTransportCost), 0 ) MedicalFoodTransportCost,
  60. ISNULL(SUM(BasicPay)/30, 0 ) BasicPayRate,
  61. ISNULL(SUM(GrossPay)/30, 0 ) GrossPayRate,
  62. ISNULL(SUM(OverTimeRate), 0 ) OverTimeRate,
  63. ISNULL(SUM(AttendanceBonus), 0 ) AttendanceBonus,
  64. ISNULL(SUM(StampDeduction), 0 ) StampDeduction
  65. FROM (SELECT EmployeeId,
  66. CASE WHEN
  67. (Eod_RefFk=1 and er.[Status]=1) THEN ActualAmount END AS BasicPay,
  68. CASE WHEN
  69. ((Eod_RefFk=1 or Eod_RefFk=2 or Eod_RefFk=3) and er.[Status]=1) THEN ActualAmount END AS GrossPay,
  70. CASE WHEN
  71. (Eod_RefFk=2 and er.[Status]=1) THEN ActualAmount END AS HouseRent,
  72. CASE WHEN
  73. (Eod_RefFk=3 and er.[Status]=1) THEN ActualAmount END AS MedicalFoodTransportCost,
  74. CASE WHEN
  75. (Eod_RefFk=11 and er.[Status]=1) THEN ActualAmount END AS OverTimeRate,
  76. CASE WHEN
  77. (Eod_RefFk=7 and er.[Status]=1) THEN ActualAmount END AS AttendanceBonus,
  78. CASE WHEN
  79. (Eod_RefFk=12 and er.[Status]=1) THEN ActualAmount END AS StampDeduction
  80. FROM HRMS_EodRecord er JOIN HRMS_Employee e on er.EmployeeId=e.ID WHERE e.Present_Status=1 and CAST(e.Joining_Date as Date)<='2019-04-01') t
  81. group by t.EmployeeId) B ON A.EmployeeId=B.EmployeeId
  82.  
  83.  
  84. INSERT INTO @EmployeePayrollTable
  85. SELECT EmployeeId,
  86. BasicPay,
  87. HouseRent,
  88. MedicalFoodTransportCost,
  89. GrossPay,
  90. (OK+LateDays+LeaveDays+HolidayOffdays),
  91. HolidayOffdays,
  92. LeaveDays,
  93. AbsentDays,
  94. AbsentDays*BasicPayRate,
  95. GrossPay-(AbsentDays*BasicPayRate),
  96. CASE WHEN AbsentDays>0 or LeaveDays>0 or LateDays>2 THEN 0
  97. ELSE AttendanceBonus END AS AttendanceBonus,
  98. LegalOverTime,
  99. OverTimeRate,
  100. LegalOverTime*OverTimeRate,
  101. StampDeduction,
  102. CASE WHEN AbsentDays>0 or LeaveDays>0 or LateDays>2 THEN(LegalOverTime*OverTimeRate)+(GrossPay-(AbsentDays*BasicPayRate))-StampDeduction
  103. ELSE (LegalOverTime*OverTimeRate)+(GrossPay-(AbsentDays*BasicPayRate)+AttendanceBonus)-StampDeduction END AS OvertimeAmount
  104. FROM @EmployeePayrollDataTable
  105.  
  106.  
  107. select * from @EmployeePayrollTable
  108.  
  109. WHERE HE.Present_Status = 1
  110. AND HE.Joining_Date <= '20190401'
  111. AND [Date] >= '20190401'
  112. AND [Date] <= '20190430'
  113.  
  114. CASE WHEN (AttendanceStatus=1 AND HAH.[Status]=1) THEN 1 ELSE 0 END AS OK,
  115. CASE WHEN (AttendanceStatus=2 AND HAH.[Status]=1) THEN 1 ELSE 0 END AS LateDays,
  116. CASE WHEN (AttendanceStatus=3 AND HAH.[Status]=1) THEN 1 ELSE 0 END AS AbsentDays,
  117. CASE WHEN (AttendanceStatus=4 AND HAH.[Status]=1) THEN 1 ELSE 0 END AS LeaveDays,
  118.  
  119. ...
  120.  
  121. SUM(TT.LateDays) AS LateDays,
  122. SUM(TT.AbsentDays) AS AbsentDays,
  123. SUM(TT.LeaveDays) AS LeaveDays,
  124. SUM(TT.HolidayOffdays) AS HolidayOffdays
  125.  
  126. WITH EmployeePayrollDataTable AS
  127. (...inner join of A and B aggregate subqueries...)
  128.  
  129. SELECT EmployeeId, BasicPay, HouseRent, MedicalFoodTransportCost, GrossPay,
  130. (OK+LateDays+LeaveDays+HolidayOffdays), HolidayOffdays, LeaveDays,
  131. AbsentDays, AbsentDays*BasicPayRate, GrossPay-(AbsentDays*BasicPayRate),
  132. CASE WHEN AbsentDays>0 or LeaveDays>0 or LateDays>2
  133. THEN 0
  134. ELSE AttendanceBonus
  135. END AS AttendanceBonus,
  136. LegalOverTime, OverTimeRate, LegalOverTime*OverTimeRate, StampDeduction,
  137. CASE WHEN AbsentDays>0 or LeaveDays>0 or LateDays>2
  138. THEN (LegalOverTime*OverTimeRate) +
  139. (GrossPay-(AbsentDays*BasicPayRate))-StampDeduction
  140. ELSE (LegalOverTime*OverTimeRate) +
  141. (GrossPay-(AbsentDays*BasicPayRate) + AttendanceBonus) -
  142. StampDeduction
  143. END AS OvertimeAmount
  144. FROM EmployeePayrollDataTable
  145.  
  146. SELECT ...all unit level CASE statements...
  147.  
  148. FROM HRMS_Attendance_History HAH
  149. JOIN HRMS_Employee HE ON HAH.EmployeeId = HE.ID
  150. JOIN HRMS_EodRecord er ON HAH.EmployeeId = er.EmployeeId
  151. JOIN HRMS_Employee e ON er.EmployeeId = e.ID
  152. WHERE HE.Present_Status = 1
  153. AND HE.Joining_Date <= '20190401'
  154. AND [Date] >= '20190401'
  155. AND [Date] <= '20190430'
  156. AND e.Present_Status = 1
  157. AND e.Joining_Date as Date <= '20190401'
  158.  
  159. WITH EmployeePayrollDataTable AS
  160. (SELECT A.EmployeeId, A.LegalOverTime, A.ExtraOverTime, A.ToatalOverTime,
  161. A.OK, A.LateDays, A.AbsentDays, A.LeaveDays, A.HolidayOffdays,
  162. A.BasicPay, A.GrossPay, A.HouseRent, A.MedicalFoodTransportCost, A.BasicPayRate,
  163. A.GrossPayRate, A.OverTimeRate, A.AttendanceBonus, A.StampDeduction
  164. FROM
  165. (SELECT TT.EmployeeId,
  166. SUM(TT.LegalOverTime) AS LegalOverTime,
  167. SUM(TT.ExtraOverTime) AS ExtraOverTime,
  168. SUM(TT.LegalOverTime) + SUM(TT.ExtraOverTime) AS ToatalOverTime,
  169. SUM(TT.OK) AS OK,
  170. SUM(TT.LateDays) AS LateDays,
  171. SUM(TT.AbsentDays) AS AbsentDays,
  172. SUM(TT.LeaveDays) AS LeaveDays,
  173. SUM(TT.HolidayOffdays) AS HolidayOffdays,
  174.  
  175. SUM(TT.BasicPay) AS BasicPay,
  176. SUM(TT.GrossPay) AS GrossPay,
  177. SUM(TT.HouseRent) AS HouseRent,
  178. SUM(TT.MedicalFoodTransportCost) AS MedicalFoodTransportCost,
  179. SUM(TT.BasicPay)/30 AS BasicPayRate,
  180. SUM(TT.GrossPay)/30 AS GrossPayRate,
  181. SUM(TT.OverTimeRate) AS OverTimeRate,
  182. SUM(TT.AttendanceBonus) AS AttendanceBonus,
  183. SUM(TT.StampDeduction) AS StampDeduction
  184. FROM
  185. (SELECT HAH.EmployeeId,
  186. CASE WHEN HAH.AttendanceStatus!=5 OR HAH.AttendanceStatus!=6
  187. THEN
  188. CASE WHEN HAH.PayableOverTime <= 2 THEN HAH.PayableOverTime
  189. WHEN HAH.PayableOverTime > 2 THEN 2
  190. ELSE 0
  191. END
  192. ELSE 0
  193. END AS LegalOverTime,
  194.  
  195. CASE WHEN HAH.AttendanceStatus!=5 OR HAH.AttendanceStatus!=6
  196. THEN
  197. CASE WHEN HAH.PayableOverTime <= 2 THEN 0
  198. WHEN HAH.PayableOverTime > 2 THEN HAH.PayableOverTime-2
  199. ELSE 0
  200. END
  201. ELSE HAH.PayableOverTime
  202. END AS ExtraOverTime,
  203.  
  204. CASE WHEN (AttendanceStatus=1 AND HAH.[Status]=1) THEN 1 ELSE 0 END AS OK,
  205. CASE WHEN (AttendanceStatus=2 AND HAH.[Status]=1) THEN 1 ELSE 0 END AS LateDays,
  206. CASE WHEN (AttendanceStatus=3 AND HAH.[Status]=1) THEN 1 ELSE 0 END AS AbsentDays,
  207. CASE WHEN (AttendanceStatus=4 AND HAH.[Status]=1) THEN 1 ELSE 0 END AS LeaveDays,
  208. CASE WHEN ((AttendanceStatus=5 OR AttendanceStatus=6) AND HAH.[Status]=1) THEN AttendanceStatus END AS HolidayOffdays,
  209.  
  210. CASE WHEN (Eod_RefFk=1 AND er.[Status]=1) THEN ActualAmount ELSE 0 END AS BasicPay,
  211. CASE WHEN ((Eod_RefFk=1 OR Eod_RefFk=2 OR Eod_RefFk=3) AND er.[Status]=1) THEN ActualAmount ELSE 0 END AS GrossPay,
  212. CASE WHEN (Eod_RefFk=2 AND er.[Status]=1) THEN ActualAmount ELSE 0 END AS HouseRent,
  213. CASE WHEN (Eod_RefFk=3 AND er.[Status]=1) THEN ActualAmount ELSE 0 END AS MedicalFoodTransportCost,
  214. CASE WHEN (Eod_RefFk=11 AND er.[Status]=1) THEN ActualAmount ELSE 0 END AS OverTimeRate,
  215. CASE WHEN (Eod_RefFk=7 AND er.[Status]=1) THEN ActualAmount ELSE 0 END AS AttendanceBonus,
  216. CASE WHEN (Eod_RefFk=12 AND er.[Status]=1) THEN ActualAmount ELSE 0 END AS StampDeduction
  217.  
  218. FROM HRMS_Attendance_History HAH
  219. JOIN HRMS_Employee HE ON HAH.EmployeeId = HE.ID
  220. JOIN HRMS_EodRecord er ON HAH.EmployeeId = er.EmployeeId
  221. JOIN HRMS_Employee e ON er.EmployeeId=e.ID
  222. WHERE HE.Present_Status = 1
  223. AND HE.Joining_Date <= '20190401'
  224. AND [Date] >= '20190401'
  225. AND [Date] <= '20190430'
  226. AND e.Present_Status = 1
  227. AND e.Joining_Date as Date <= '20190401'
  228. ) TT
  229. GROUP BY TT.EmployeeId) A
  230. )
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!