need help sql query
I am having on开发者_JAVA技巧e query given below in that Employee
, ShiftAllocation
, ShiftMaster
(two for two shift: Shift1 and shift2) table
SELECT
Em.EmployeeId, Em.EmployeeName,
Sa.ShiftAllocationDate, Sa.ShiftId2, Sa.ShiftId, Sm.ShiftName,
Sm2.ShiftName AS ShiftName2, Sa.ShiftAllocationId
FROM
ShiftAllocation AS Sa
INNER JOIN
EmployeeMaster AS Em ON Sa.EmployeeId = Em.EmployeeId
LEFT OUTER JOIN
ShiftMaster AS Sm2 ON Sa.ShiftId2 = Sm2.ShiftId
LEFT OUTER JOIN
ShiftMaster AS Sm ON Sa.ShiftId = Sm.ShiftId
I am getting this output:
ShiftAlld EmployeeId EmployeeName ShiftAllDate shiftId2 ShifId ShiftName ShiftName2
1 19 XYZ 2011-08-01 NULL 1 General NULL
2 19 XYZ 2011-08-02 NULL 1 General NULL
3 19 XYZ 2011-08-02 NULL -1 NULL NULL
4 19 XYZ 2011-08-02 NULL 1 General NULL
5 19 XYZ 2011-08-02 NULL -2 NULL NULL
6 19 XYZ 2011-08-02 NULL 1 General NULL
I want
shiftId
-1 value should assign ShiftName as Week OffShiftId
-2 value should assign ShiftName as HolidayShiftId2
NULL value should assign ShiftName2 as Not Assign
I want this output:
ShiftAlld EmployeeId EmployeeName ShiftAllDate shiftId2 ShifId ShiftName ShiftName2
1 19 XYZ 2011-08-01 NULL 1 General NotAssign
2 19 XYZ 2011-08-02 NULL 1 General NotAssign
3 19 XYZ 2011-08-02 NULL -1 WeekOff NotAssign
4 19 XYZ 2011-08-02 NULL 1 General NotAssign
5 19 XYZ 2011-08-02 NULL -2 Holiday NotAssign
6 19 XYZ 2011-08-02 NULL 1 General NotAssign
You can use a case something like this (untested so there may be any number of typos in there).
select Em.EmployeeId,
Em.EmployeeName,
Sa.ShiftAllocationDate,
Sa.ShiftId2,
Sa.ShiftId,
case when sa.ShiftId = -1 then 'Week Off'
when sa.ShiftId = -2 then 'Holiday'
else sm.ShiftName
end as ShiftName,
case when sm2.ShiftId is null then 'Not assigned'
else Sm2.ShiftName
end as ShiftName2,
Sa.ShiftAllocationId
from ShiftAllocation as Sa
inner join EmployeeMaster as Em
on Sa.EmployeeId = Em.EmployeeId
left outer join ShiftMaster as Sm2
on Sa.ShiftId2 = Sm2.ShiftId
left outer join ShiftMaster as Sm
on Sa.ShiftId = Sm.ShiftId
Although I am not sure if it would be the best way to do it; you can try using CASE in your sql to arrive at a solution.
Edit : This http://www.dba-oracle.com/t_case_sql_clause.htm should help you if you want to try.
精彩评论