July 16, 2009 at 5:52 am
select distinct
emp.First_Name,emp.Last_Name,emp.Middle_Name,
comp.employee_id,comp.request_dt,comp.numofdays,
comp.reason,comp.request_by,comp.approve_dt,
tech.First_Name+' ' +tech.Last_Name+' '+isNull(tech.Middle_Name,'') as REQUESTEDBY,
comp.approve_status,comp.approve_desc,
case when (comp.approve_by) is null then '' else ( select first_name+' '+last_name from employee_tbl where user_id=comp.approve_by) end as APPROVEBY
from
employee_tbl emp,tl_compoff_tbl comp,TL_activity_tbl tla
,employee_tbl tech
where emp.employee_id = comp.employee_id
and comp.employee_id = tla.employee_id
and emp.employee_id = tla.employee_id
and comp.request_by = 'AST/016'
and emp.employee_status = 'A'
and (comp.approve_status='O' or comp.approve_status='D')
and tla.status='A'
and getdate() between tla.start_date and tla.end_date
and tech.User_Id = comp.request_by
order by comp.approve_status--emp.First_Name
Hi,
My question is can we use any other statement instead of case in this.Pls correct and reply
July 16, 2009 at 6:14 am
your case statement looks incorrect. can you post table structure, sample data and expected output pls.
This URL will help you in posting these.
http://www.sqlservercentral.com/articles/Best+Practices/61537
July 16, 2009 at 6:32 am
Try this:
SELECT DISTINCT
emp.First_Name
,emp.Last_Name
,emp.Middle_Name
,comp.employee_id
,comp.request_dt
,comp.numofdays
,comp.reason
,comp.request_by
,comp.approve_dt
,tech.First_Name + ' ' + tech.Last_Name + ' ' + COALESCE(tech.Middle_Name, '') AS REQUESTEDBY
,comp.approve_status
,comp.approve_desc
,COALESCE(approve.first_name + ' ' + approve.last_name, '') AS APPROVEBY
FROM employee_tbl emp
JOIN l_compoff_tbl comp
ON emp.employee_id = comp.employee_id
JOIN TL_activity_tbl tla
ON emp.employee_id = tla.employee_id
JOIN employee_tbl tech
ON comp.request_by = tech.[User_Id]
LEFT JOIN employee_tbl approve
ON comp.approve_by = approve.[user_id]
WHERE comp.request_by = 'AST/016'
AND emp.employee_status = 'A'
AND (comp.approve_status='O' OR comp.approve_status='D')
AND tla.status='A'
AND GETDATE() BETWEEN tla.start_date AND tla.end_date
ORDER BY approve_status--First_Name
July 16, 2009 at 7:14 am
Thanks dude...........it really works
July 17, 2009 at 1:11 pm
Ken McKelvey (7/16/2009)
Try this:
SELECT DISTINCT
emp.First_Name
,emp.Last_Name
,emp.Middle_Name
,comp.employee_id
,comp.request_dt
,comp.numofdays
,comp.reason
,comp.request_by
,comp.approve_dt
,tech.First_Name + ' ' + tech.Last_Name + ' ' + COALESCE(tech.Middle_Name, '') AS REQUESTEDBY
,comp.approve_status
,comp.approve_desc
,COALESCE(approve.first_name + ' ' + approve.last_name, '') AS APPROVEBY
FROM employee_tbl emp
JOIN l_compoff_tbl comp
ON emp.employee_id = comp.employee_id
JOIN TL_activity_tbl tla
ON emp.employee_id = tla.employee_id
JOIN employee_tbl tech
ON comp.request_by = tech.[User_Id]
LEFT JOIN employee_tbl approve
ON comp.approve_by = approve.[user_id]
WHERE comp.request_by = 'AST/016'
AND emp.employee_status = 'A'
AND (comp.approve_status='O' OR comp.approve_status='D')
AND tla.status='A'
AND GETDATE() BETWEEN tla.start_date AND tla.end_date
ORDER BY approve_status--First_Name
Can you please tell me how to change this toa HQL(Hibernate Query Language)
Tanx 😀
July 21, 2009 at 4:24 am
Jinu try this:
select distinct
emp.First_Name,emp.Last_Name,emp.Middle_Name,
comp.employee_id,comp.request_dt,comp.numofdays,
comp.reason,comp.request_by,comp.approve_dt,
tech.First_Name+' ' +tech.Last_Name+' '+isNull(tech.Middle_Name,'') as REQUESTEDBY,
comp.approve_status,comp.approve_desc,
case when (comp.approve_by) is null then '' else app.first_name+' '+app.Last_Name end as APPROVEBY
from
employee_tbl emp,tl_compoff_tbl comp,TL_activity_tbl tla
,employee_tbl tech,employee_tbl app
where emp.employee_id = comp.employee_id
and comp.employee_id = tla.employee_id
and emp.employee_id = tla.employee_id
and comp.request_by = 'AST/016'
and emp.employee_status = 'A'
and (app.user_id=comp.approve_by or (comp.approve_by) is null)
and (comp.approve_status='O' or comp.approve_status='D')
and tla.status='A'
and getdate() between tla.start_date and tla.end_date
and tech.User_Id = comp.request_by
order by comp.approve_status--emp.First_Name
Tanx 😀
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply