If you do that select you will end up with EXACTLY the same data as you started with + a count column that will always be 1.
The issue is that you kind of want to group the paycatid field within changes of the paycatid field. Therefore you have to process each record one at a time looking for a change and then returning the proper start and end dates for that "range". Therefore you have to use a cursor to process the records one by one. ______________________________________________________ Bill Grover Supervisor MIS Phone: 301.424.3300 x3324 EU Services, Inc. FAX: 301.424.3696 649 North Horners Lane E-Mail: [EMAIL PROTECTED] Rockville, MD 20850-1299 WWW: http://www.euservices.com ______________________________________________________ > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: Thursday, March 14, 2002 11:06 AM > To: CF-Talk > Subject: Re: SQL help please > > > try > > select employeeid, startdate, enddate, count(*), paycatid > from X > group by employeeid, startdate, enddate, paycatid > > > > > > ksuh > > @shaw.ca To: CF-Talk > <[EMAIL PROTECTED]> > cc: > > 03/13/02 Subject: Re: SQL > help please > 04:51 PM > > Please > > respond to > > cf-talk > > > > > > > > > > Anyone? > > Bueller? Bueller? > > ----- Original Message ----- > From: [EMAIL PROTECTED] > Date: Wednesday, March 13, 2002 2:51 pm > Subject: SQL help please > > > Brain not working.... Must help... > > > > Here's my data set: > > > > employeeid startdate enddate paycatid > > ----------- ----------- ----------- ----------- > > 936 2002-02-08 2002-02-08 1 > > 936 2002-02-11 2002-02-11 1 > > 936 2002-02-12 2002-02-12 1 > > 936 2002-02-13 2002-02-13 11 > > 936 2002-02-14 2002-02-14 1 > > 936 2002-02-15 2002-02-15 1 > > 936 2002-02-18 2002-02-18 11 > > 936 2002-02-19 2002-02-19 11 > > 936 2002-02-20 2002-02-20 11 > > 936 2002-02-21 2002-02-21 11 > > 936 2002-02-22 2002-02-22 11 > > 936 2002-02-25 2002-02-25 7 > > 936 2002-02-27 2002-02-27 7 > > > > What I'm wondering is how do I get something like: > > > > employeeid startdate enddate same paycatid in a row paycatid > > ----------- ----------- ----------- ----------- ---------- > > 936 2002-02-08 2002-02-12 3 1 > > 936 2002-02-13 2002-02-13 1 11 > > 936 2002-02-14 2002-02-15 2 1 > > 936 2002-02-18 2002-02-22 5 11 > > 936 2002-02-25 2002-02-27 2 7 > > > > I'm using SQL Server 7. > > > > > > > > ______________________________________________________________________ Why Share? Dedicated Win 2000 Server � PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation � $99/Month � Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
