1 Reply Latest reply: Oct 2, 2012 3:51 PM by Alex Pan RSS

    Converting SQL CASE statement

      Hi All

       

      I am trying to convert a SQL Statement into a QlikView compatible statement can anybody please help

       

      SQL Statement:

       

      This currently calculates the number of Active employees at the end of a given period + the number of new starters in the period - the number of leavers in the period to give me the new balance of Active Employees

       

      select

      Count(CASE WHEN IsNull(EMPLOYEE.START_DATE, '1901-01-01') < '2012-06-01' And IsNull(EMPLOYEE.LEAVE_DATE,'2999-12-31') > '2012-06-30' THEN EMPLOYEE.EMP_CODE END) as  [B/Fwd Active Employees (July)],

      Count(CASE WHEN EMPLOYEE.START_DATE >= '2012-07-01' And EMPLOYEE.START_DATE <= '2012-07-31' THEN EMPLOYEE.EMP_CODE END) as [Employee Starters (July)],

      Count(CASE WHEN EMPLOYEE.LEAVE_DATE >= '2012-07-01' And EMPLOYEE.LEAVE_DATE <= '2012-07-31' THEN EMPLOYEE.EMP_CODE END) as [Employee Leavers (July)],

      Count(CASE WHEN IsNull(EMPLOYEE.START_DATE, '1901-01-01') < '2012-07-01' And IsNull(EMPLOYEE.LEAVE_DATE,'2999-12-31') > '2012-07-31' THEN EMPLOYEE.EMP_CODE END) as  [C/Fwd Active Employees (July)]

      from EMPLOYEE

       

      I am trying to convert this to achieve the same either through the script or directly in a number of chart expressions

       

      Kind Regard

       

      Martyn