Teradata SQL Test – 1

We have an EmployeeSalary Table which holds information about Employee and the Salary which the Employee is getting in respective months. Below is the snapshot of the EMP_SAL table:

SNAPSHOT of EMP_SAL Table
SNAPSHOT of EMP_SAL Table

Now we need to write an SQL Query which can retrieve EMPLOYEE ID,EMPLOYEE NAME, CURRENT MONTH, CURRENT MONTH SALARY and PREVIOUS MONTH SALARY of the EMPLOYEE. If the SALARY for any particular month is not available then it should be treated as ZERO.

Below is the Output which is required from the SQL Query:

OUTPUT-TERADATA SQL TEST-1
OUTPUT-TERADATA SQL TEST-1

Can you write the correct query? Leave your SQL Query as comment below. There may be several ways of getting the desired output. You can check the answer on ANSWERS PAGE too. However for better understanding of Teradata SQL , it is suggested that you leave a comment below with your SQL Query and then check our answer. We may get better and more ways of getting the result.

Hint: Try it via self join

15 thoughts on “Teradata SQL Test – 1

  1. sel empname,sal_month as curr_month,sal as curr_sal,ZEROIFNULL((sel sal from emp_sal where sal_month = extract(month from CURRENT_DATE)-3)) prev_sal from emp_sal where sal_month = extract(month from CURRENT_DATE);

  2. SELECT e.emp_name, e.salary as CURRENT_MONTH_SALARY, p1.salary as PREV_MONTH_SALARY, p2.salary as PREV_2_MONTH_SALARY FROM employee e
    LEFT OUTER JOIN employee p1 ON e.Employee_Id=p1.Employee_Id AND e.MONTH-1=p1.MONTH
    LEFT OUTER JOIN employee p2 ON e.Employee_Id=p2.Employee_Id AND e.MONTH-2=p2.MONTH;

  3. select a.employee_id,a.employee_name ,a.sal_month as current_month,a.sal as current_month_salary ,NVL(b.sal,0) from emp_sal a left outer join emp_sal b on a.employee_id=b.employee_id and a.sal_month-1=b.sal_month;

  4. SEL A.EMP_ID, A.EMP_NAME, A.SAL_MON AS CURRENT_MONTH, A.SAL AS CURRENT_MONTH_SALARY
    , COALESCE(SUM(A.SAL) OVER (PARTITION BY A.EMP_ID ORDER BY A.SAL_MON ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),0)
    AS PREVIOUS_MONTH_SALARY
    FROM EMP_SAL A
    ;

  5. select empid,empname,salary,
    coalesce ( (max(salary)
    OVER ( Partition by empid order by salary
    rows between 1 preceding and 1 preceding)),
    0) as previous
    from dv8work.sqltest

  6. SELECT

    A.EMP_ID as Employee_Id,
    A.EMP_NAME as Employee_Name,
    A.SAL_MON AS CURRENT_MONTH,
    ZEROIFNULL(A.SAL_MON) as CURRENT_SAL_MON,
    ZEROIFNULL(A.SAL_MON) as PREVIOUS_SAL_MON

    FROM

    EMP_SAL A LEFT OUTER JOIN EMP_SAL B

    ON

    A.EMP_ID = B.EMP_ID AND
    A.SAL_MON-1 = B.SAL_MON;

  7. select
    t1.emp_id
    ,t1.emp_name
    ,t1.sal_mon
    ,coalesce(t1.sal,0) as current_sal
    ,coalesce(t2.sal,0) as prev_sal
    from employee t1,
    inner join
    employee t2
    on
    t1.emp_id=t2.emp_id
    and
    (t1.sal_mon-1)=t2.sal_mon;

  8. SELECT EMP_ID,EMP_NAME,SAL_MON AS CURRENT_MONTH,SAL AS CURRENT_MONTH_SALARY,
    COALESCE(MIN(SAL) OVER(PARTITION BY EMP_NAME ORDER BY SAL_MON ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),’0′) AS PREV_MONTH
    FROM EMP_SAL_GUJJA

  9. SELECT a.EMP_ID AS ‘EMP ID’,
    a.EMP_NAME AS ‘NAME’,
    a.SAL_MONTH AS ‘CURRENT MONTH’.
    a.SAL AS ‘CUR SAL’,
    Coalesce(b.SAL,0) AS ‘PREV SAL’
    FROM EMP_SAL a
    LEFT OUTER JOIN EMP_SAL b ON a.EMP_ID = b.EMP_ID
    AND a.SAL_MONTH-1=b.SAL_MONTH;

  10. SELECT EMP_ID AS EMP ID, EMP_NAME AS NAME, SAL_MONTH AS CURRENT MONTH, SAL AS CUR SAL
    ,COALESCE(SUM(SAL) OVER(PARTITION BY EMP_ID ORDER BY SAL_MONTH ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),0) AS PREV SAL
    FROM EMP_SAL ORDER BY EMP_ID, EMP_NAME, SAL_MONTH;

  11. SEL A.EMP_ID,A.EMP_NAME ,A.SAL_MONTH AS CURRENT_SAL_MONTH , A.SAL AS CURRENT_SALARY ,
    COALESCE(MAX (A.SAL) OVER (PARTITION BY A.EMP_ID ORDER BY A.SAL ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING), 0) AS PREV_SALARY
    FROM EMPLOYEE A

  12. SEL TB1.EMP_ID AS “EMP ID”, TB1.EMP_NAME AS NAME, TB1.SAL_MON AS “CURRENT MONTH”, TB1.SAL AS “CUR SAL”, COALESCE(TB2.SAL,0) AS “PREV SAL”
    FROM EMP_SAL TB1
    LEFT OUTER JOIN EMP_SAL TB2
    ON TB1.EMP_ID = TB2.EMP_ID
    AND TB1.SAL_MON-1 = TB2.SAL_MON;

    select t1.emp_id, t1.emp_name, t1.sal_mon, t1.sal, case when t1.sal_mon – 1 = t2.sal_mon then t2.sal else 0 as Prev_sal
    from table t1, table t2
    where t1.emp_id = t2.emap_id

    1. sel emp_id,emp_name,sal_mth,nullifzero(sal)
      from salary_table
      where sal_mth= extract(month from current_date)
      or sal_mth=extract(month from current_date)-1

Leave a Reply

Your email address will not be published. Required fields are marked *