SQl Query Issue - 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 2000 Forums

SQL Server Development (2000)

SQl Query Issue

Author

Topic

abyie
Starting Member

42 Posts

Posted - 2006-08-10 : 22:26:58

Hi,
I want get a resultset which showns me the participant id, with
their first jobStartdate and date on eight day of the same job.All the attendance dates are of the same job.So no need to worry about different job situation.

Sample Current Table
Participant_ID attendance_date
1001 7/1/2005
1001 7/2/2005
1001 7/3/2005
1001 7/4/2005
1001 7/5/2005
1001 7/6/2005
1001 7/7/2005
1001 7/8/2005
1001 7/9/2005
1001 7/10/2005
1001 7/11/2005
1001 7/12/2005
1003 7/8/2005
1003 7/9/2005
1003 7/10/2005
1003 7/11/2005
1003 7/12/2005
1003 7/13/2005
1003 7/14/2005
1003 7/15/2005
1003 7/16/2005

Expected ResultSet
Participant_ID First_Job_Start_Date Eigth_day_of_job
1001 7/1/2005 7/8/2005
1003 7/8/2005 7/15/2005

Your help would be greatly appreciated.
Thanks
----------
Abyie

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-10 : 23:09:57

is this what you need ?


select Participant_ID, min(attendance_date)  as [First_Job_Start_Date],
       dateadd(day, 7, min(attendance_date)) as [Eight_Day_Of_Job]
from   table
group by Participant_ID

abyie
Starting Member

42 Posts

Posted - 2006-08-10 : 23:18:05

thanks for your reply.But here the date i would get would only be date which is 8 days from his first job_start_date.While in some cases, if the participant did not attend his job one day, his eighth day would get shifted back.
For example
if a participant_id 1001 did not attend on 7/4/2005 then his atttendance day would get shifted to 7/9/2005 instead of 7/8/2005.
Hope this makes it clear

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-10 : 23:19:45

so basically Eight_Day_Of_Job is the 8th date ?

abyie
Starting Member

42 Posts

Posted - 2006-08-10 : 23:21:56

Yes.That's what I am looking in the resultset.

regards
----------
Abyie

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-10 : 23:50:25

[code]select Participant_ID, min(attendance_date) as First, max(attendance_date) as Eight
from
(
select t.Participant_ID, t.attendance_date
from table t
where attendance_date in (select top 8 attendance_date from table x where x.Participant_ID = t.Participant_ID order by attendance_date)
) d
group by Participant_ID[/code]

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-11 : 00:06:42

quote:

Originally posted by khtan

select	Participant_ID, min(attendance_date) as First, max(attendance_date) as Eight
from
(
	select	t.Participant_ID, t.attendance_date
	from	table t
	where	attendance_date in (select top 8 attendance_date from table x where x.Participant_ID = t.Participant_ID order by attendance_date)
) d
group by Participant_ID
-- I think you need this to make sure you have at least 8
Having count(*) = 8

CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-11 : 00:08:47

oh yes. You are right

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources