Service Database Queries

This document contains SQL queries organized by HRMS modules.

---

Table of Contents

• Confirmation
• Payroll Setup
• Separation
• Leave Management
• Attendance Management
• Employee Management
• Expense
• Transfer
• HR Documents
• Configuration
• Task Management
• Timesheet

---

Confirmation

Probation Form Status Query

select * from ProbationFormStatusPMSNew p1 
where p1.id in (
    select max(id)  
    from ProbationFormStatusPMSNew 
    where createddate > '2025-04-11 16:24:37.968' 
      and stage = 'HR Pending' 
    group by employeeid
);

SQL Queries for Probation and Form Details

select * from tbl_formdetail;
select * from tbl_probationcategory;
select * from tbl_probationquestion where tbl_probationquestion.formid = 10013;
select * from tbl_ProbationRatingNew;

Form and Probation Configuration Queries

select * FROM tbl_FormDetail tfd 
-- For form creation
-- REMOVE FORM ID FROM QUERY, STATUS AND WAIGHTAGE CONVERT INTO TRUE/FALSE

select * from tbl_ProbationCategory 
-- For Category creation
-- Change status to true/false and update form ID

select * from tbl_ProbationQuestion 
-- For Sub Question creation
-- Change probationCatId and form ID
-- Update status, isParent, openEnded to true/false

select * from tbl_ProbationRating tpr 
-- For rating configuration
-- Change formID

select * from tbl_FormFinalRat 
-- For storing final ratings (Weightage for self/rep1 and rep2)
-- Change formID

Employee Probation Data Update

select Eprobation, * from tbl_Employee WHERE employeeid = 'EC055';

-- Update probation status
UPDATE tbl_Employee 
SET Eprobation = 1 
WHERE employeeid = 'EC055';

-- Check employee probation details
select * from tbl_EmployeeProbation where employeeid = 'EC055';

-- Insert probation record
INSERT INTO tbl_EmployeeProbation (
    EmployeeID, StartDate, EndDate, Extended, ExtendedDate, ReasonForExtend, 
    Confirmed, ConfirmedDate, ReasonForConfirm, ConfirmationStatus, 
    CreatedDate, CreatedBy, ModifiedBy, ModifiedDate, LeaveAllocatedOnConfirmed
)
VALUES (
    'employeeid', 'joiningDate format //2022-11-08 00:00:00.000', 
    'probation end date format //2022-11-08 00:00:00.000', 
    0, null, null, 0, null, null, 0, null, null, null, null, 0
);

select * from tbl_EmployeeProbation where EmployeeID in('rtw0174')
--update tbl_EmployeeProbation set EndDate = '2022-12-24 00:00:00.000' where EmployeeID = 'RTW0174'

Probation Question PMS and Category Queries

select * from tbl_ProbationQuestionPMS where QuestionID in ('33','34','35');
update tbl_ProbationQuestionPMS set ProbationCatID = '5' where QuestionID in ('33','34','35');
select * from tbl_probationcategoryPMS;
select * from tbl_FormDetail;
select * from tbl_ProbationQuestionPMS;

Change Assessment Level

select LevelOfAssessment,* from tbl_FormDetail tfd 
where FormID in (select FormID from tbl_PMSGoalList tpl where EmployeeID = '5448');

update tbl_FormDetail set LevelOfAssessment = '3' 
where FormID in (select FormID from tbl_PMSGoalList tpl where EmployeeID = '5448');

Level Mapping:

• 2 → employee + rep1
• 3 → employee + rep1 + rep2
• 4 → rep1
• 5 → rep1 + rep2

Change Rating of emp + rep1 + rep2

select * from tbl_FormDetail;
update tbl_FormFinalRat set SelfRat='50', Rep1Rat='25', Rep2Rat='25' where FormID='30006';
select * from tbl_FormFinalRat;

SQL Queries for EmployeeID '0007'

select * from tbl_TNIFormEmployeeMaster where EmployeeID in ('0007');
select * from tbl_ProbRatingMasterPms where EmployeeID in ('0007');
select * from ProbationFormStatusPMSNew where EmployeeID in ('0007');
select * from tbl_PMSKRATypeMaster where EmployeeID in ('0007');
select * from tbl_pmsgoallIst where EmployeeID in ('0007');
select * from tbl_pms_cycle_employee where Employee_ID in ('0007');

TNI Form Employee Master with PMS Goal List

select tfe.EmployeeID,
       (select Question from tbl_TNIFormQuestionMaster where QuestionID = tfe.TniQueID) as Question,
       (select CatName from tbl_TNIFormCategoryMaster where CatID = tfe.TniCatID) as CategoryName,
       tfe.EmpComments, tfe.Rep1Comments, tfe.Rep2Comments,
       tbl_PMSGoalList.reviewStatus as cycleNumber
from tbl_TNIFormEmployeeMaster tfe
join tbl_PMSGoalList on id = pms_goal_list_id
where pms_goal_list_id in (Select id from tbl_PMSGoalList where pms_cycle_id = 3)
order by tbl_PMSGoalList.reviewStatus;

select EmployeeID,
       (select Question from tbl_TNIFormQuestionMaster where QuestionID = tfe.TniQueID) as Question,
       (select CatName from tbl_TNIFormCategoryMaster where CatID = tfe.TniCatID) as CategoryName,
       EmpComments, Rep1Comments, Rep2Comments
from tbl_TNIFormEmployeeMaster tfe 
where pms_goal_list_id in (Select id from tbl_PMSGoalList where pms_cycle_id = 3 and ReviewStatus = 2);

select col5, col6, MarketStandard1, MarketStandard2, Bonus1, Bonus2, col1, col3, stage, *
from ProbationFormStatusPMSNew
where employeeid = 'I0137' and stage = 'HR Pending';

PMS Cycle Detail Query

select * from tbl_PmsCycleDetailInfo;

Delete PMS Records

delete from tbl_TNIFormEmployeeMaster where EmployeeID='AV1107202034';
delete from tbl_ProbRatingMasterPms where EmployeeID='AV1107202034';
delete from ProbationFormStatusPMSNew where EmployeeID='AV1107202034';
delete from tbl_PMSKRATypeMaster where EmployeeID='AV1107202034';
delete from tbl_pmsgoallIst where EmployeeID='AV1107202034';
delete from tbl_pms_cycle_employee where Employee_ID='AV1107202034';

---

Payroll Setup

TDS Section Master and Title Status Update

To Disable

UPDATE tbl_tdssectionmaster SET status='INACTIVE' WHERE id=5;
UPDATE tbl_tdssectiontitle SET status='INACTIVE' WHERE sectionid=5;

To Enable

UPDATE tbl_tdssectionmaster SET status='ACTIVE' WHERE id=5;
UPDATE tbl_tdssectiontitle SET status='ACTIVE' WHERE sectionid=5;

TDS Declaration and Detail Queries

select Attachment,* from tbl_TDS_Detail ttd 
where TdsId in (
    select TdsId from tbl_TDS_MASTER ttm 
    where ttm.History = 0 and EmployeeId in ('A0888','A1036')
) 
and ttd.Attachment is not null;

update TDS_DeclarationCategoryMaster set Status = 1 
where CategoryName = 'Donations(sec 80G)' and TDSDclID = 32;

select * from tds_declarationcategorymaster td where CategoryName = 'Donations(sec 80G)';

UPDATE public.tds_declarationcategorymaster SET status = true WHERE tdsdclid = 32;

Report Footer Reference Query

select signature5, * from tbl_Reportfooterreference;

TDS Declaration Master Queries

select * from TDS_DeclarationTitleMaster tdtm where TdsDecCatID = '28';
select status, flag, * from TDS_DeclarationTitleMaster where TDSDecTitleID in ('119','122','127');
select * from TDS_DeclarationTitleMaster tdtm where Title = 'Running Repair and Maintenance';
--update TDS_DeclarationTitleMaster set status='0', flag='1' where TDSDecTitleID in ('119','127');
update TDS_DeclarationTitleMaster set status='0', flag='1' where TDSDecTitleID in ('123','130');

TDS Declaration Details Queries

select * from tbl_TDS_Detail ttd where TDSDecTitleID in ('121','123','130');
--update tbl_TDS_Detail set TDSDecTitleID = '122' where TdsDetailId = '8619';

Notes:

• In tbl_TDS_Detail, TDSDecTitleID should be the same.
• Apart from that, the element needs to change status and flag in the related declaration table.

FNF Query

select * from tbl_fndfline tf where fnfheaderid = '173';
select * from tbl_fndfheader tf where employeeid = '2384';

Monthly Attendance Query

select * from tbl_MonthlyAttendance where MAYear = '2023' and Payslipmonth = 6;

-- update tbl_monthlyattendance set Processed = 1, ProcessPost = 1, IsDisbursed = 1,  
--     PostedCount = 1, DisbursedCount = 1 where MAYear = '2023' and Payslipmonth = 6;

Employee Pay Element Queries

select * from tbl_EmployeePayElement where employeeid = 'PF021';
update tbl_EmployeePayElement set SalaryType = 'Salary' where PayElementID = '529';
update tbl_EmployeePayElement set AddDeduction = 'Deduction' where PayElementID = '528';

Company-Wise Payslip and Employee Day Count

SELECT companyWisePayslip, * FROM tbl_CompanyMaster;
UPDATE tbl_CompanyMaster SET companyWisePayslip = '0' WHERE CompanyID = '3';
SELECT * FROM tbl_EmpDayCount WHERE EmpId = '20' AND month = 11;

Employee Salary Update Queries

select ESalaryOn, EPaymentMethod, * from tbl_Employee 
where ESalaryOn = 'Day' and EmployeeID = 'SB003';

--update tbl_Employee set ESalaryOn = 'WorkingDay' where ESalaryOn = 'WorkingDay';
--update tbl_Employee set ESalaryOn = 'Day-HO' where ESalaryOn = 'Day-HO';
--update tbl_Employee set ESalaryOn = 'Month' where ESalaryOn = 'Month';
--update tbl_Employee set ESalaryOn = 'DWages' where ESalaryOn = 'DWages';
--update tbl_Employee set ESalaryOn = 'Stipend' where ESalaryOn = 'Stipend';
--update tbl_Employee set ESalaryOn = 'Hourly' where ESalaryOn = 'Hourly';

Monthly Attendance SQL Queries

SELECT * FROM tbl_MonthlyAttendance 
WHERE EmployeeID = 'STL004' AND MAYear = '2022' AND PayslipMonth = 7;

-- DELETE FROM tbl_MonthlyAttendance 
-- WHERE MonthlyAttendanceID IN ('205', '206', '207', '208', '209', '210', '211', '213');

Freeze and Unfreeze Queries

Notes: 0 = Unfreeze, 1 = Freeze

-- Daily Attendance Freeze Check
select Freezed, * from tbl_DailyAttendanceN 
where EmployeeID = 'V10008' and date between '2022/11/21' and '2022/12/20';

-- Daily Attendance Unfreeze Update
-- update tbl_DailyAttendanceN set Freezed = 0 
-- where EmployeeID = '5500049' and date between '2022/10/01' and '2022/10/31';

-- Employee Day Count Freeze Check
select IsFreezed, * from tbl_EmpDayCount 
where EmpId = 'V10008' and Month = 12 and Year = 2022;

-- Employee Day Count Unfreeze Update
-- update tbl_EmpDayCount set IsFreezed = 0 
-- where EmpId = '5500049' and Month = 10 and Year = 2022;

Attendance Cleanup SQL Queries

-- For Daily Attendance (remove duplicates)
DELETE FROM tbl_DailyAttendanceN 
WHERE AttendanceID IN (
    SELECT MAX(AttendanceID) FROM tbl_DailyAttendanceN 
    GROUP BY EmployeeID, [Date] HAVING COUNT(*) > 1
);

-- For Monthly Attendance (remove duplicates)
DELETE FROM tbl_MonthlyAttendance  
WHERE MonthlyAttendanceID NOT IN (
    SELECT MIN(MonthlyAttendanceID) FROM tbl_MonthlyAttendance 
    GROUP BY EmployeeID, PaySlipMonth, MAYear
);

---

Separation

Exit Interview & Resignation Queries

select * from exit_interviewtrack ei where empid = '00894';
select * from tbl_sepexitinterview ts where employeeid = '00894';
select * from tbl_employeeexitdata te where employeeid  = '00894';
select exitinterviewstatus, * from tbl_resignationmaster tr where employeeid = '00894';

Resignation Clearance SQL Scripts

Insert Queries

INSERT INTO tbl_ResignationClearanceHead
(ResignationID, RCEmployeeID, RCAttachment, ClearanceStatus, HrStatus, CreatedBy, CreatedDate, ModifiedBy, ModifiedDate, Remarks)
VALUES (
    (SELECT ResignationID FROM tbl_ResignationMaster trm WHERE EmployeeID = 'MGO0061' ORDER BY ResignationID DESC LIMIT 1),
    'MGO0061', NULL, N'Pending', N'Pending', N'Administrator01',
    '2023-07-25 13:47:09.287', N'Administrator01', '2023-07-25 13:47:09.287', NULL
);

INSERT INTO tbl_ResignationClearance
(RCHeaderID, DepartmentID, RCStatus, RCDiscriptions, RCCreateDate, RCApprowBy, DepartmentIDNew, DptEmpHead,
 HRStatus, Attachment, DeptClearanceSetUpId, PocName, CreatedBy, CreatedDate, ModifiedBy, ModifiedDate,
 DeptClearanceSetUpName, ApprovedBy, ApprovedDate)
VALUES (
    (SELECT RCHeaderID FROM tbl_ResignationClearanceHead trch
        WHERE ResignationID = (SELECT ResignationID FROM tbl_ResignationMaster trm WHERE EmployeeID = 'MGO0061' ORDER BY ResignationID DESC LIMIT 1)
        AND RCEmployeeID = 'MGO0061' ORDER BY RCHeaderID DESC LIMIT 1),
    NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
    (SELECT id FROM tbl_DepartmentClearanceSetUp tdcsu ORDER BY id DESC LIMIT 1),
    (SELECT POC FROM tbl_DepartmentClearanceSetUp tdcsu ORDER BY id DESC LIMIT 1),
    N'Administrator01', '2023-07-25 13:47:09.403', N'Administrator01', '2023-07-25 13:47:09.403',
    (SELECT name FROM tbl_DepartmentClearanceSetUp tdcsu ORDER BY id DESC LIMIT 1),
    NULL, NULL
);

INSERT INTO tbl_ResignationClearanceTaskInfo
(RClearanceId, ClearanceName, TaskName, Remarks, Status, Attachment, CreatedBy, CreatedDate, ModifiedBy, ModifiedDate, ApprovedBy, ApprovedDate, ClearanceId)
VALUES (
    (SELECT RClearanceID FROM tbl_ResignationClearance trc WHERE RCHeaderID = (
        SELECT RCHeaderID FROM tbl_ResignationClearanceHead trch
        WHERE ResignationID = (SELECT ResignationID FROM tbl_ResignationMaster trm WHERE EmployeeID = 'MGO0061' ORDER BY ResignationID DESC LIMIT 1)
        AND RCEmployeeID = 'MGO0061' ORDER BY RCHeaderID DESC LIMIT 1
    )),
    (SELECT name FROM tbl_DepartmentClearanceSetUp tdcsu ORDER BY id DESC LIMIT 1),
    (SELECT TaskName FROM tbl_DepartmentClearanceTaskSetUp tdctsu WHERE DepartmentClearanceId = (
        SELECT id FROM tbl_DepartmentClearanceSetUp tdcsu ORDER BY id DESC LIMIT 1
    ) ORDER BY id DESC LIMIT 1),
    NULL, N'Pending', NULL, N'Administrator01', '2024-04-22 17:55:09.433',
    N'Administrator01', '2024-04-22 17:55:09.433', NULL, NULL,
    (SELECT id FROM tbl_DepartmentClearanceSetUp tdcsu ORDER BY id DESC LIMIT 1)
);

Select Queries

SELECT * FROM tbl_ResignationClearanceTaskInfo ORDER BY id DESC;
SELECT * FROM tbl_ResignationClearance ORDER BY RCHeaderID DESC;
SELECT * FROM tbl_ResignationClearanceHead ORDER BY RCHeaderID DESC;
SELECT * FROM tbl_ResignationMaster tr WHERE EmployeeID = 'MGO0061';

Resignation Clearance Queries

select * from tbl_ResignationClearanceHead;
select * from tbl_ResignationClearance;
select * from tbl_resignationclearancetaskinfo tr;

Employee Reactivation SQL Queries

-- Check Employee Status
select Comm10, EEmployeeStatus, * from tbl_Employee 
where EmployeeID in ('AV1107202052','AV1107202045');

-- Reactivate Employee (Commented for safety)
-- update tbl_Employee set Comm10 = NULL, EEmployeeStatus = 'Active' 
-- where EmployeeID in ('AV1107202052','AV1107202045');

-- Check User Login Status
select LoginStatus, * from tbl_Userlogin where userid in ('EMP01');

-- Update User Login Status to Active
update tbl_Userlogin set LoginStatus = 1 where userid in ('EMP01');

-- Check Inactive Employee Record
select * from tbl_inactiveemployee where employeeid = 'EMP01';

SQL Queries for Resignation and Employee Exit Data

Select Queries

select * from tbl_ResignationMaster where EmployeeID in ('AV1107202052','AV1107202045');
select * from tbl_ResignationClearanceHead where ResignationID in ('30','31');
select * from tbl_ResignationClearance where RCHeaderID in ('20019','20020');
select * from tbl_ResignationClearanceTaskInfo where RClearanceId in ('20019','20020');
select * from Exit_InterviewTrack where Empid in ('AV1107202052','AV1107202045');
select * from tbl_EmployeeExitData where Employeeid in ('AV1107202052','AV1107202045');

Delete Queries

delete from tbl_ResignationMaster where EmployeeID in ('AV1107202052','AV1107202045');
delete from tbl_ResignationClearanceHead where ResignationID in ('30','31');
delete from tbl_ResignationClearance where RCHeaderID in ('20019','20020');
delete from tbl_ResignationClearanceTaskInfo where RClearanceId in ('20019','20020');
delete from Exit_InterviewTrack where Empid in ('AV1107202052','AV1107202045');
delete from tbl_EmployeeExitData where Employeeid in ('AV1107202052','AV1107202045');

Employee Resignation and Status Update Queries

-- Set Resignation Status to 'F&F Pending'
update tbl_Employee set ResigStatus='F&F Pending' where EmployeeID='RTW0004';

-- Bulk Update Resignation Status to 'F&F Pending'
update tbl_Employee set ResigStatus='F&F Pending' 
where EmployeeID in ('TEC043','TEC040', 'TEC046','TEC014', 'LOM015');

select ResigStatus, EEmployeeStatus, * from tbl_Employee 
where EmployeeID in ('TEC043','TEC040', 'TEC046','TEC014', 'LOM015');

-- Update Employee 'RTW0145' Details
update tbl_Employee set ResigStatus='F&F Pending' where EmployeeID='RTW0145';
update tbl_Employee set EEmployeeStatus='Active' where EmployeeID in ('RTW0145');
select EEmployeeStatus, * from tbl_Employee where EmployeeID in ('RTW0145');

---

Leave Management

COFF Data and Leave Application Queries

-- For All Data
select leavecode, extraworkingid, * from tbl_leaveapplicationheader tl 
where leavecode not in (
    select tl2.leavecodename from tbl_leavecodemaster tl2 
    where tl2.leavesubtype='COMPENSATORY_OFF'
) and extraworkingid is not null;

-- For Getting Employee COFF Data
select totalworkeddays, coffapproveddays, coffpendingdays, coffremainingdays, * 
from tbl_employeecoff te where employeeid = 'HIIL0740';

-- For Getting Other Leave Applications (Actual COFF Taken)
select * from tbl_leaveapplicationheader tl 
where employeeid = 'HIIL0740' and tl.extraworkingid = '404' 
  and leavecode in (
      select tl2.leavecodename from tbl_leavecodemaster tl2 
      where tl2.leavesubtype='COMPENSATORY_OFF'
  );

-- For Leave Balance Transactions
select * from tbl_leavebalancetransactions tl where employeeid = 'HIIL0902';

Employee Comp Off Queries

select * from tbl_employeecoff where Employeeid = 'GT0078';
select * from tbl_LeaveApplicationHeader where employeeid = 'GT0078' and Leavecode = 'Comp Off';

Notes:

• CoffApprovedDays – Approved compensatory off days for the employee.
• CoffPendingDays – Should be 0 when employee applies for Comp Off.

Leave Report SQL Queries

select * from tbl_LeaveReport tlr;
delete from tbl_LeaveReport where id = 3;

Holiday Queries

SELECT * FROM tbl_HolidayEmployeeMapping;
SELECT * FROM tbl_holidayDetails thd;
SELECT * FROM tbl_holidayAllocationDetails thad;
SELECT * FROM tbl_HolidayEmployeeMapping them;

Leave Policy Config Update Queries

UPDATE tbl_LeaveCodePolicyConfig 
SET EffectiveFromDate = '2022-11-01' 
WHERE LeaveCodePolicyName = 'Copy of Earned Leave Policy';

SELECT * FROM tbl_LeaveCodePolicyConfig;

Leave Application and Credit/Debit SQL Queries

SELECT * FROM tbl_LeaveApplicationHeader WHERE employeeid = 'EC008';
SELECT * FROM tbl_LeaveApplicationLine WHERE LeaveAppID = 'LV91';
UPDATE tbl_LeaveApplicationHeader SET NoofDays = '1' WHERE LeaveAppID = 'LV91';
SELECT * FROM tbl_leavecreditdebit WHERE employeeid = 'A0953' AND Leavecode = 'EL';
SELECT * FROM tbl_MonthlyLeaves WHERE employeeid = 'A0953' AND Leavecode = 'EL' ORDER BY MLMonth DESC;

---

Attendance Management

Attendance and Tracking Queries

-- Attendance and Daily Records
select * from tbl_AttendanceRequest where EmployeeID = 'CIPL185';
select * from tbl_DailyAttendanceN where EmployeeID = 'CIPL185' and date = '2022-07-09';
select * from tbl_user_tracker where employee_id = 'CIPL185' and cast(date as date) = '2022-07-09';
select * from tbl_daily_tracker where employee_id = 'CIPL185' and cast(date as date) = '2022-07-09';
select * from tbl_user_tracking where employee_id = 'CIPL185' and cast(date as date) = '2022-07-09' order by date desc;

-- User and Daily Tracking for Other Employees
select * from tbl_daily_tracker tdt where employee_id = 'GN0002';
select * from tbl_user_tracker tut where employee_id = 'GN0002';
select * from tbl_user_tracking tut where employee_id = 'GN0002' and cast(date as date) = '2024-09-25';
select * from tbl_user_tracking tut where employee_id = 'GN9230' and date >= '2025-06-28' and date <= '2025-06-29';

User Tracker Queries

select * from tbl_user_tracker 
where date >= '2023/05/13' and employee_id in ('51', '', '') 
order by CreatedDate, date;

select * from tbl_user_tracker 
where date >= '2023/05/13' and employee_id in ('51', '', '') 
order by date, CreatedDate;

User Tracker SQL Queries

SELECT * FROM tbl_user_tracker WHERE employee_id = 'SS0014' AND date = '';
UPDATE tbl_user_tracker SET punch_type = 'PUNCH OUT' WHERE id = '8761';
UPDATE tbl_user_tracker SET punch_type = 'PUNCH IN' WHERE id = '8756';

Delete User Tracker and Daily Tracker Records

delete from tbl_user_tracker where daily_tracker_id in ('217397','217399');
delete from tbl_daily_tracker where id in ('217397','217399');

select * from tbl_daily_tracker where employee_id = '3324' order by id desc;
select * from tbl_daily_tracker where id in ('217397','217399');

select * from tbl_user_tracker where date >= '2023/05/06' and employee_id in ('3324','','') order by id desc;
select * from tbl_user_tracker where daily_tracker_id in ('217397','217399');

select * from tbl_dailyattendanceN where date >= '2023/05/06' and employeeID = '3324';
update tbl_DailyAttendanceN set ActualInTime = NULL where AttendanceID = '524637';
update tbl_DailyAttendanceN set ActualInTime = '2023-05-10 03:39:06.000' where AttendanceID = '524741';

Attendance Work Schedule Tables

• tbl_attendance_workschedule_history - Historical records of schedule changes
• tbl_attendance_workschedule - Current employee attendance scheduling

Shift Master and User Login SQL Queries

SELECT ToleranceStart, ToleranceEnd, * FROM tbl_ShiftMasterV1;
SELECT * FROM tbl_ShiftMasterV1 WHERE shiftid IN ('3', '4', '5', '6', '7', '8', '9', '10', '11');
UPDATE tbl_ShiftMasterV1 SET ToleranceStart = NULL, ToleranceEnd = NULL WHERE shiftid IN ('14', '15', '16');
SELECT * FROM tbl_userlogin WHERE UserID = 'OT0062';
UPDATE tbl_ShiftMasterV1 SET HalfDayHours = '195' WHERE shiftid = '9';
-- UPDATE tbl_ShiftMasterV1 SET FullDayHours = '480' WHERE ShiftId = '14';

Database Tables and SQL Queries

SELECT * FROM tbl_user_tracking tut 
WHERE employee_id = 'SY051' AND DATE(createddate) = '2025-01-27' 
ORDER BY id DESC;

SELECT * FROM tbl_user_tracker
WHERE DATE(date) = '2024-05-14' AND DATE(createddate) = '2024-05-15';

SELECT * FROM tbl_user_tracker tut WHERE employee_id = 'WT001';
SELECT * FROM tbl_daily_tracker tdt WHERE employee_id = 'WT001';
SELECT * FROM tbl_dailyattendancen td WHERE employeeid = 'WT001';

---

Employee Management

Employee Structure and Transfer Queries

-- Get Employee Structure
select * from tbl_EmployeeStructure where EmployeeID = 'YP373' and Erunning = '1';

-- Update Employee Structure
update tbl_EmployeeStructure 
set DesignationID = '33383', PaycaderID = '33359' 
where Estructureid = '10699';

-- Lookup Master Queries
select * from tbl_LookupMaster where LookupID in ('33410', '33372');
select * from tbl_LookupMaster where LookUpName = 'sales';

Update Reporting Person in Employee Structure

-- Clear Reporting Persons
update tbl_employeestructure
set ReportingPerson = NULL, ReportingPerson1 = NULL
where EmployeeID = '1100149' and erunning = '1';

-- Set Reporting Persons
update tbl_employeestructure
set ReportingPerson = '1100669', ReportingPerson1 = '1100669'
where EmployeeID = '1100149' and erunning = '1';

Employee Structure Queries - Clean Reporting Person Data

select * from tbl_employeestructure;

select replace(
    ltrim(rtrim(replace(reportingperson, char(9), '    '))),
    '    ',
    char(9)
), employeeId from tbl_employeestructure;

-- Update Reporting Person
update tbl_employeestructure 
set reportingperson = replace(
    ltrim(rtrim(replace(reportingperson, char(9), '    '))),
    '    ',
    char(9)
);

-- Update Reporting Person 1
update tbl_employeestructure 
set reportingperson1 = replace(
    ltrim(rtrim(replace(reportingperson1, char(9), '    '))),
    '    ',
    char(9)
);

Employee Relation Query

select * from tbl_employeerelation where employeeid = '2';

Database Tables

• tbl_Employee
• tbl_UserLogin
• tbl_AttendanceRequest
• tbl_Companymaster

---

Expense

Expense Policy Queries

select * from tbl_customfields_options tco where value = 'Pole Purchase Expense';
select * from tbl_expense_policy tep where expense_type_id = '8';
select * from tbl_expense_policy tep where expense_type_id = '63';

Expense Query File

select * from tbl_expenceapproval te where expid in ('118');
select * from tbl_expenceclaimmaster te where comm1 = 'NSPL0007';

-- Additional Tables to Check
-- tbl_expense_custom_field_values
-- tbl_expensepaymentbunch

Expense Approval

select * from tbl_expenceapproval where ExpID = '31795';

---

Transfer

Transfer Requisition Master

select * from tbl_TransferRequisitionMaster where EmployeeID = 'PVPLR1866';
update tbl_TransferRequisitionMaster 
set Fromdate = '2022-11-30' 
where TransferRequisitionID in ('11434', '11435', '11433');

---

HR Documents

HR_DocumentMaster Update Script

select * from HR_DocumentMaster;
update HR_DocumentMaster set DocCategoryId = 1 where DocumentID = 2;

HR_DocumentMaster - Null Check Query

select * from HR_DocumentMaster hdm where DocCategoryID = NULL;

Note: Check that DocCategoryID should not be null.

Employee Document Queries

-- Select Employee Document
SELECT * from tbl_EmployeeDocument ted where EmployeeID = 'RIBPL-0135';

-- Delete Employee Document
--delete FROM tbl_EmployeeDocument where EDocumentID = '299';

-- Select Employee Document History
SELECT * from tbl_EmployeeDocumentHistory tedh where EDocumentID = '299';

-- Delete Employee Document History
--delete from tbl_EmployeeDocumentHistory where EDocumentID = '299';

---

Configuration

System Workflow and Album SQL Queries

select AlbumSize, * from tbl_SystemWorkFlow tswf;
--update tbl_SystemWorkFlow set AlbumSize = '10';

select PhotoCount, * from tbl_album;
--update tbl_album set PhotoCount = '10' where Id in ('9','10');

Lookup and Notice Period Queries

select * from tbl_LookupMaster where LookupID in ('33360','33362');
select * from tbl_EmployeeStructure where employeeid = 'mgo0239';
select * from tbl_NoticePeriodSetUp;

Update fetchlocationname in tbl_systemworkflow

UPDATE public.tbl_systemworkflow
SET fetchlocationname = true
WHERE id = 1;

SQL Update Query

UPDATE tbl_SystemWorkFlow 
SET OverTimeConfig = 'ACTIVE_AUTO';

System Workflow and Album SQL Queries

SELECT AlbumSize, * FROM tbl_SystemWorkFlow tswf;
UPDATE tbl_SystemWorkFlow SET AlbumSize = '10';
SELECT * FROM tbl_album;
UPDATE tbl_album SET PhotoCount = '0' WHERE Id = '6';

Email SMTP Master SQL Query

UPDATE tbl_EmailSmtpMaster 
SET EmailUserName = 'hrms1@continentalgroup.com' 
WHERE <condition>;

⚠️ Note: Replace <condition> with the appropriate filter.

Master Tenant Query Update

-- Select Query
select * from emgage.dbo.master_tenant where tenant_id = 'sartransport';

-- Update Query
UPDATE emgage.dbo.master_tenant 
SET TokenValidFrom = '2023-04-18 12:38:00.250' 
WHERE tenant_id = 'sartransport';

---

Task Management

SQL Queries - Stage and Daily Report

select * from tbl_DailyReport;
select * from tbl_stage_access where uid in (118);
select * from tbl_stage where entity = 'DailyTask';
select * from tbl_stage_access_controll;

---

Timesheet

No specific queries categorized for Timesheet module yet.

---

Promotion

No specific queries categorized for Promotion module yet.

---

Loan

No specific queries categorized for Loan module yet.

---

Ticket Management

No specific queries categorized for Ticket Management module yet.

---

LMS (Learning Management System)

No specific queries categorized for LMS module yet.

---

Dynamic Field Configuration

No specific queries categorized for Dynamic Field Configuration module yet.

---

Email Notification

No specific queries categorized for Email Notification module yet.