Sql Script 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 2000 Forums

SQL Server Development (2000)

Sql Script query

Author

Topic

abyie
Starting Member

42 Posts

Posted - 2006-08-10 : 20:20:02

Hi,
I am trying to get a resultset of all people who worked at a particualar job for atleast 8 days but with the condition that
all these days should be within 20 days from the first job start date.
Any help would be appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-10 : 20:26:34

can you post your table structure, some sample data and the expected result ?

abyie
Starting Member

42 Posts

Posted - 2006-08-10 : 21:04:16

Here is a sample table structure. As you can see that participant id 1001 has started his job on 7/1/2005 and worked for 12 days which is listed as separate dates on attendance_date column.So he shoud be counted as a distinct participant in my resultset as I am looking for all participants who have worked at least 8 days.Also all these
participants who are counted in my resultset should have their attendance dates should be witin 20 days from his start date.i.e 7/1/2005 for participant id 1001.

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
1002 8/1/2004
1002 8/2/2004
1002 8/3/2004
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

For Participant_ID 1001, the 20 day period ends on 7/20/2005 as his first attend date is on 7/1/2005.Within this period if he works for 8 days, he should be counted in my resultset.

Hope this makes it clear.Please let me know if you need any further info.

Thanks
--------
Abyie

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-08-10 : 21:13:06

From your first post:

>> I am trying to get a resultset of all people who worked at a particualar job for atleast 8 days but with the condition that
all these days should be within 20 days from the first job start date.

What job? How do we know which job each participant works on? There's no notion of jobs in your sample data at all.

Please, before answering these questions, step back and take a look at the information you are providing us carefully and be sure that you are giving us enough information so that we can help. Remember, we don't know you or your problem or your data, we only know what you tell us, and the more guesswork we need to do the harder it is for us to help you.

Thanks.

- Jeff

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-10 : 21:48:45

I think you are looking for something like this.


select
	a.Participant_ID
from
	MyTable a
	join
	(
	Select
		bb.Participant_ID,
		Last_Date = 
		dateadd(dd,19,min(bb.attendance_date))
	from
		MyTable bb
	group by
		bb.Participant_ID
	) b
	on
	a.Participant_ID = b.Participant_ID and
	a.attendance_date <= b.Last_Date
group by
	a.Participant_ID
having
	count(*) > 7
order by
	a.Participant_ID

CODO ERGO SUM

abyie
Starting Member

42 Posts

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

I am sorry for not simplifying the question for you.But in the following scenario it is only one job that all these participants are working on.But every participant has different dates scheduled for their jobs.That's why I am looking for all particpants who worked at this jobs for atleast 8 days.

abyie
Starting Member

42 Posts

Posted - 2006-08-10 : 22:10:22

Hi Michael,
Thanks for getting back to me with the sql query.Could you clarify what would the join condition do in the given sql syntax
i.e a.attendance.date <= b.attendance and when we are counting
the Participants,how are we doing that?

Thanks for help.
Regards
-----------
Abyie

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-10 : 22:35:11

quote:
Originally posted by abyie

Hi Michael,
Thanks for getting back to me with the sql query.Could you clarify what would the join condition do in the given sql syntax
i.e a.attendance.date <= b.attendance and when we are counting
the Participants,how are we doing that?

Thanks for help.
Regards
-----------
Abyie

Do you know the basics of SQL?

CODO ERGO SUM

abyie
Starting Member

42 Posts

Posted - 2006-08-10 : 22:49:47

Yes I do so.But the reason I asked that question was to know exactly
what I would be getting in my resultset.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

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

quote:
Originally posted by abyie

Yes I do so.But the reason I asked that question was to know exactly
what I would be getting in my resultset.

A list of Participant_ID who meet the conditions you specified.

You should run the code against your table to see exactly what you get.

CODO ERGO SUM

abyie
Starting Member

42 Posts

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

Michael,
Thanks for your help.Will try to execute the scripts.

Cheers
----------
Abyie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources