Two SQL queries that we found helpful in the Lawson Absence Management Suite

We don’t typically post technical information here on the blog, that’s mostly reserved for the education section of the site. The past couple of week however we were able to write a couple of queries that helped out a customer quite a bit so we figured maybe you all can benefit from them. Without any further delay, here they are:

 

 

---

-- Get available balances for employees in a specific group and/or plan
select E.COMPANY,E.EMPLOYEE,E.FIRST_NAME,E.LAST_NAME ,E.EMP_STATUS, T.R_PLAN, T.EMPLOYEE_GROUP,T.ACCR_LAST_DT,T.AVAIL_HRS_BAL
From [PROD].[dbo].TAEEMASTER T, [PROD].[dbo].EMPLOYEE E
Where 
-- T.EMPLOYEE_GROUP = 'EXAMPLE' and
T.R_PLAN='XXX' and
T.COMPANY = E.COMPANY and
T.EMPLOYEE = E.EMPLOYEE and
E.EMP_STATUS not like 'T%';
-- Obviously you'll have to change the Employee Group, the Plan code, and the Emp-Status condition to match yours.

This next statement does something rather unique. It reaches into GEN to get the status code definitions for the table field. Nifty!

-- Get all transactions for specific employee group and plan since a specific date including field transactions from GEN
select E.COMPANY,E.EMPLOYEE,E.FIRST_NAME,E.LAST_NAME ,E.EMP_STATUS, T.R_PLAN, T.EMPLOYEE_GROUP,T.TA_HOURS,T.DESCRIPTION,A.VALXLT as 'TYPE',B.VALXLT AS 'TRAN TYPE',C.VALXLT AS 'STATUS',D.VALXLT AS 'BALANCE TYPE'
From [PROD].[dbo].TAEMPTRANS T, [prod].[dbo].EMPLOYEE E , [GEN].[dbo].[VALLST] A, [GEN].[dbo].[VALLST] B, [GEN].[dbo].[VALLST] C, [GEN].[dbo].[VALLST] D
Where 
T.EMPLOYEE_GROUP = 'EXAMPLE' and
T.R_PLAN in('XXX','YYY') and
T.COMPANY = E.COMPANY and
T.EMPLOYEE = E.EMPLOYEE and
T.R_DATE >= '2015-01-01' and
A.DOMVAL =  T.TA_TYPE and
B.DOMVAL =  T.TRAN_TYPE and
C.DOMVAL =  T.STATUS and 
D.DOMVAL = T.BALANCE_TYPE and
A.PRODUCTLINE = 'PROD' and A.FILENAME='TAEMPTRANS' and A.FLDNAME='TA-TYPE' and
B.PRODUCTLINE = 'PROD' and B.FILENAME='TAEMPTRANS' and B.FLDNAME='TRAN-TYPE' and
C.PRODUCTLINE = 'PROD' and C.FILENAME='TAEMPTRANS' and C.FLDNAME='STATUS' and
D.PRODUCTLINE = 'PROD' and D.FILENAME='TAEMPTRANS' and D.FLDNAME='BALANCE-TYPE';

-- You'll have to change the Employee Group, the Plan codes, and the date condition to match yours.