doubt in query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

doubt in query

Author

Topic

shm
Yak Posting Veteran

86 Posts

Posted - 2009-01-06 : 00:41:46

hi

i have written sp ,in the sp query am taking the emp detail for the daterange fromdate and todate...
wt am trying to get is if the emp is not present in that daterange he should also come..means total 5 employee ,3 employees are present in the date between '01-02-2008' and '03-03-2008' but 2 are absent..how can i get the data for the absentee also along with other 3 employee..i shld display date as null to the absentee...

i tried using union but it is coming wrong..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-06 : 00:42:43

Please provide a data example as it is much easier for us to understand issues if we also see sample data and the expected result set. It's very hard for us to visualize your issue with just an explanation as we aren't familiar with your environment.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-06 : 00:56:00

select empid,cast(null as datetime) as date into #temp from urtable where empdate not between @prevdate and @nowdate
insert into #temp
select empid,empdate as date from urtable where empdate between @prevdate and @nowdate

select * from #temp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-01-06 : 01:21:41

Something like

select emp.employeename, att.date from employee_master as emp
left join attendance as att on emp.emp_code=att.emp_code
and att.date between @date1 and @date2

Madhivanan

Failing to plan is Planning to fail

shm
Yak Posting Veteran

86 Posts

Posted - 2009-01-06 : 01:27:04

ok
SELECT SEPG_EMP.EMP_NAME,
HRMS_SB.SBD_ITEM_DESC ,
PLT.DATE,

P.PRJ_PROJECT_NAME,
PH.PPH_PHASE_NAME,
PM.PMO_MOD_NAME,
PT.PTK_TASK_NAME,
PB.PBG_ID,
PB.PBG_DESCRIPTION,
SD.SDLC_ITEM_NAME,
SA.SUB_ITEM_NAME,
PLT.PLT_HRS,
PLT.PLT_DETAILS

FROM PROJECT_LOG_TIME PLT
INNER JOIN EMPLOYEE SEPG_EMP ON SEPG_EMP.EMP_SEQ_NO = PLT.EMP_SEQ_NO
INNER JOIN PROJECT_TASK PT ON PT.PTK_SEQ_NO = PLT.PLT_OBJECT
INNER JOIN PROJECT_MODULE PM ON PM.PMO_MOD_SEQ_NO = PT.PMO_MOD_SEQ_NO
INNER JOIN PROJECT_PHASE PH ON PH.PPH_SEQ_NO = PM.PPH_SEQ_NO
INNER JOIN PROJECT P ON P.PRJ_SEQ_NO = PH.PRJ_SEQ_NO
INNER JOIN SYS_BUSINESS_CODE_DETAIL AS SB ON SB.SBD_ITEM_CODE = P.PRJ_STATUS_ITEM_CODE and SB.SBD_ITEM_DESC='In Progress'
LEFT OUTER JOIN PROJECT_BUG PB ON PB.PBG_ID = PLT.PBG_ID
INNER JOIN SUB_ACTIVITY SA ON SA.SUB_ITEM_CODE = PLT.PLT_ACT_TYPE_CODE
INNER JOIN SDLC_ACTIVITY SD ON SD.SDLC_ITEM_CODE = PLT.PLT_ACT_HEAD_ITEM_CODE
INNER JOIN [10.10.210.2].hrms.dbo.EMPLOYEE HRMS_EMP ON HRMS_EMP.EMP_LOG_ID = SEPG_EMP.EMP_SAMS_ID
INNER JOIN [10.10.210.2].hrms.dbo.SYS_BUSINESS_CODE_DETAIL HRMS_SB ON HRMS_SB.SBD_ITEM_CODE = HRMS_EMP.EMP_DESIG_ITEM_CODE
WHERE HRMS_EMP.EMP_SEQ_NO = 525
AND HRMS_EMP.EMP_DESIG_ITEM_CODE = 182
---AND date between'01-02-2008' AND '03-03-2008'

wihout giving the date the data is below

emp date
ram 2008-01-03
ram 2008-01-08
ram 2008-01-09
prasad 2008-01-17
prasad 2008-02-04
prasad 2008-02-06
prasad 2008-02-07
prasad 2008-02-11
geeta 2008-03-14
geeta 2008-03-10
geeta 2008-04-02
geeta 2008-03-19
geeta 2008-05-23

if i give the date range then the geeta will not display as there is no record for the date range date between'01-02-2008' AND '03-03-2008'
..i want that to display as the date as null for the geeta record...

i think the above explanation can understand wt am trying to get....

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-06 : 01:48:24

use case ,
try like this
select empid, case when empdate >'01-02-2008' and empdate < '03-03-2008' then null else empdate end as date from urtable

shm
Yak Posting Veteran

86 Posts

Posted - 2009-01-06 : 02:31:18

hi

no am not getting correct..i want all the rows along with geetha employee and date as null for that.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-06 : 02:50:49

get that data into temptable
and use this code
declare @temp table(emp varchar(32), date datetime)
insert into @temp select 'ram', '2008-01-03' union all
select 'ram', '2008-01-08' union all
select 'ram', '2008-01-09' union all
select 'prasad', '2008-01-17' union all
select 'prasad', '2008-02-04' union all
select 'prasad', '2008-02-06 ' union all
select 'prasad', '2008-02-07 ' union all
select 'prasad', '2008-02-11' union all
select 'geeta', '2008-03-14' union all
select 'geeta','2008-03-10' union all
select 'geeta', '2008-04-02' union all
select 'geeta', '2008-03-19' union all
select 'geeta' ,'2008-05-23'

select emp, case when date >= '01-02-2008' AND date <= '03-03-2008' then date else null end as date from @temp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-06 : 02:55:03

something like this


SELECT SEPG_EMP.EMP_NAME,
HRMS_SB.SBD_ITEM_DESC ,
PLT.DATE,

P.PRJ_PROJECT_NAME, 
PH.PPH_PHASE_NAME, 
PM.PMO_MOD_NAME, 
PT.PTK_TASK_NAME, 
PB.PBG_ID, 
PB.PBG_DESCRIPTION, 
SD.SDLC_ITEM_NAME, 
SA.SUB_ITEM_NAME, 
PLT.PLT_HRS, 
PLT.PLT_DETAILS

FROM EMPLOYEE SEPG_EMP
LEFT JOIN PROJECT_LOG_TIME PLT  ON SEPG_EMP.EMP_SEQ_NO = PLT.EMP_SEQ_NO
LEFT JOIN PROJECT_TASK PT ON PT.PTK_SEQ_NO = PLT.PLT_OBJECT
AND PLT.date between'01-02-2008' AND '03-03-2008'
LEFT JOIN PROJECT_MODULE PM ON PM.PMO_MOD_SEQ_NO = PT.PMO_MOD_SEQ_NO 
LEFT JOIN PROJECT_PHASE PH ON PH.PPH_SEQ_NO = PM.PPH_SEQ_NO
LEFT JOIN PROJECT P ON P.PRJ_SEQ_NO = PH.PRJ_SEQ_NO 
LEFT JOIN SYS_BUSINESS_CODE_DETAIL AS SB ON SB.SBD_ITEM_CODE = P.PRJ_STATUS_ITEM_CODE and SB.SBD_ITEM_DESC='In Progress'
LEFT OUTER JOIN PROJECT_BUG PB ON PB.PBG_ID = PLT.PBG_ID 
LEFT JOIN SUB_ACTIVITY SA ON SA.SUB_ITEM_CODE = PLT.PLT_ACT_TYPE_CODE 
LEFT JOIN SDLC_ACTIVITY SD ON SD.SDLC_ITEM_CODE = PLT.PLT_ACT_HEAD_ITEM_CODE
LEFT JOIN [10.10.210.2].hrms.dbo.EMPLOYEE HRMS_EMP ON HRMS_EMP.EMP_LOG_ID = SEPG_EMP.EMP_SAMS_ID
AND HRMS_EMP.EMP_SEQ_NO = 525 
AND HRMS_EMP.EMP_DESIG_ITEM_CODE = 182 
INNER JOIN [10.10.210.2].hrms.dbo.SYS_BUSINESS_CODE_DETAIL HRMS_SB ON HRMS_SB.SBD_ITEM_CODE = HRMS_EMP.EMP_DESIG_ITEM_CODE