Advanced SQL query, need to add another table and employee ID

Question

I'm new to advanced SQL. I have a query that I need to add a variable. It needs to connect to an employee table and only show a record for that specific employeeId. This is Outsystems and advanced SQL query. Any ideas?

This is what I need to add: Employee.EmployeeId = EmployeeId or EmployeeId = NullIdentifier()

Existing query that I need to add the above to some how:

Select 
      EMPLOYEEDISPLAYNAME ,
      ISNULL(Sick.Total,0.00) as SickValue,
      ISNULL( Vacation.Total,0.00) as VacationValue
   from 
      {Employee} 
         left join 
         ( Select 
                 EMPLOYEEID,
                 Sum( DEPOSITVALUE ) - Sum( WITHDRAWLVALUE ) as Total
              from 
                 {TimeOffRegisterEntry}
                    join {TimeOffRegister} 
                       on {TimeOffRegisterEntry}.TimeOffRegisterId = {TimeOffRegister}.TimeOffRegisterId 
                    join {TimeOffYear} 
                       on {TimeOffRegister}.TimeOffYearId = {TimeOffYear}.TimeOffYearId 
              where 
                     TIMEOFFTYPE = @VacationType 
                 and {TimeOffYear}.[TimeOffYearId] = @Year 
              group by 
                 EMPLOYEEID 
              having 
                 Sum( DEPOSITVALUE ) - Sum( WITHDRAWLVALUE ) < 0
         ) as Vacation 
           on {Employee}.EmployeeId = Vacation.EMPLOYEEID 

         left join 
         ( Select 
                 EMPLOYEEID,
                 Sum( DEPOSITVALUE ) - Sum( WITHDRAWLVALUE ) as Total
              from 
                 {TimeOffRegisterEntry}   
                    join {TimeOffRegister}  
                       on {TimeOffRegister}.TimeOffRegisterId = {TimeOffRegisterEntry}.TimeOffRegisterId
                    join {TimeOffYear} 
                       on {TimeOffRegister}.TimeOffYearId = {TimeOffYear}.TimeOffYearId 
              where 
                     TIMEOFFTYPE = @SickType 
                 and {TimeOffYear}.[TimeOffYearId] = @Year 
              group by 
                 EMPLOYEEID 
              having 
                 Sum( DEPOSITVALUE ) - Sum( WITHDRAWLVALUE ) < 0
         ) as Sick
            on {Employee}.EmployeeId = Sick.EMPLOYEEID 

   where 
         Vacation.total is not null 
      or Sick.Total is not null

Which dbms? (Some non-ANSI SQL there...) Also, can you try to format the query so it can be read without too much effort? — jarlh, Apr 22 '15 at 14:20

Gonçalo Borrêga · Accepted Answer · 2015-04-23T09:45:01.773

You'll need to add a new parameter to the query (EmployeeId, probably of type EmployeeId Identifier) and add the condition to your query.

enter image description here

Without knowing your full database structure:

Adding it only in the outermost Where condition should be enough
Adding it in the inner queries may also be required / optimize the query

In the end the query would look something like

Select 
  EMPLOYEEDISPLAYNAME ,
  ISNULL(Sick.Total,0.00) as SickValue,
  ISNULL( Vacation.Total,0.00) as VacationValue
from 
  {Employee} 
     left join 
     ( Select 
             EMPLOYEEID,
             Sum( DEPOSITVALUE ) - Sum( WITHDRAWLVALUE ) as Total
          from 
             {TimeOffRegisterEntry}
                join {TimeOffRegister} 
                   on {TimeOffRegisterEntry}.TimeOffRegisterId = {TimeOffRegister}.TimeOffRegisterId 
                join {TimeOffYear} 
                   on {TimeOffRegister}.TimeOffYearId = {TimeOffYear}.TimeOffYearId 
          where 
                 TIMEOFFTYPE = @VacationType 
             and {TimeOffYear}.[TimeOffYearId] = @Year 
             AND (@EmployeeId = 0 OR EMPLOYEEID = @EmployeeId)
          group by 
             EMPLOYEEID 
          having 
             Sum( DEPOSITVALUE ) - Sum( WITHDRAWLVALUE ) < 0
     ) as Vacation 
       on {Employee}.EmployeeId = Vacation.EMPLOYEEID 

     left join 
     ( Select 
             EMPLOYEEID,
             Sum( DEPOSITVALUE ) - Sum( WITHDRAWLVALUE ) as Total
          from 
             {TimeOffRegisterEntry}   
                join {TimeOffRegister}  
                   on {TimeOffRegister}.TimeOffRegisterId = {TimeOffRegisterEntry}.TimeOffRegisterId
                join {TimeOffYear} 
                   on {TimeOffRegister}.TimeOffYearId = {TimeOffYear}.TimeOffYearId 
          where 
                 TIMEOFFTYPE = @SickType 
             and {TimeOffYear}.[TimeOffYearId] = @Year 
              AND (@EmployeeId = 0 OR EMPLOYEEID = @EmployeeId)
          group by 
             EMPLOYEEID 
          having 
             Sum( DEPOSITVALUE ) - Sum( WITHDRAWLVALUE ) < 0
     ) as Sick
        on {Employee}.EmployeeId = Sick.EMPLOYEEID 

where 
    (@EmployeeId = 0 OR {Employee}.[EmployeeId] = @EmployeeId)
    AND (
         Vacation.total is not null 
      or Sick.Total is not null
    )

Look for references to @EmployeeId in the above code snippet.

Notice the AND ( ... or ... ) wrapping the alternate condition

score 1 · Answer 2 · answered Apr 22 '15 at 14:24

I think you should look at the mechanism suggested here (passing in an input variable and setting 'expand'): http://www.outsystems.com/forums/discussion/6103/advanced-query-with-search-parameters/

The forums for Outsystems are a font of knowledge! ( a credit to them!)

Advanced SQL query, need to add another table and employee ID

2 Answers2