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:
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:
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
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);
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;
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;
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
;
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
Raviteja, did this work perfectly, I was also trying in similar way..thanks for this
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;
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;
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
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;
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;
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
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
Sel emp_id,
emp_name,
currentmonth,
zeroifnull(Cuurmonth),
zeroifnull(Prevmonth)
from Emp)sal;
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