Yeah, I ended up using a cursor to do it. Thanks Bill.
-----Original Message-----
From: Bill Grover [mailto:[EMAIL PROTECTED]]
Sent: Thursday, March 14, 2002 6:01 AM
To: CF-Talk
Subject: RE: SQL help please
I'm not sure you can do it with a simple select statement. If I understand
what you want you want to look at each entry and whenever the paycatid field
changes report the first start and last end dates for that id.
You will probably need to create and run a stored procedure. Your SP will
need to create a cursor to process the records 1 by 1 looking for the
paycatid field to change and then saving the start and end dates.
Something like this (data types may vary depending on your actual data
types). This routine is for a single employee but can be modified to work
for multiple employees.
CREATE PROCEDURE GetCatInfoForEmp
@tnEmpID smallint
AS
SET NOCOUNT ON
DECLARE @lnEmpID smallint,
@lcStartDate varchar(10),
@lcEndDate varchar(10),
@lcCatID varchar(2),
@lcLastID varchar(2),
@lnNumRec smallint,
@lcLowStart varchar(10),
@lcHighEnd varchar(10
DECLARE DataSet CURSOR FOR
SELECT employeeid startdate enddate paycatid
FROM sometable
WHERE employeeid = @tcEmpID
ORDER BY startdate, paycatid
CREATE TABLE #PayCatSum
(employeeid smallint,
startdate varchar(10),
enddate varchar(10),
paycatid varchar(2),
entrycount smallint)
OPEN DataSet
FETCH NEXT FROM DataSet INTO @lnEmpID, @lcStartDate, @lcEndDate, @lcCatID
SET @lcLastID = @lcCatID
SET @lnNumRec = 0
SET @lcLowStart = @lcStartDate
SET @lcHighEnd = @lcEndDate
WHILE @@FETCH_STATUS = 0
BEGIN
IF @lcLastID <> @lcCatID
BEGIN
INSERT INTO #PayCatSum
VALUES (@lnEmpID, @lcLowStart, @lcHighEnd, @lcLastID, @lnNumRec)
SET @lnNumRec = 1
SET @lcLastID = @lcCatID
SET @lcLowStart = @lcStartDate
SET @lcHighEnd = @lcEndDate
END
ELSE
BEGIN
IF @lcStartDate < @lcLowStart
SET @lcLowStart = @lcStartDate
IF @lcEndDate > @lcHighEnd
SET @lcHighEnd = @lcEndDate
SET @lnNumRec = @lnNumRec + 1
END
FETCH NEXT FROM DataSet INTO @lnEmpID, @lcStartDate, @lcEndDate, @lcCatID
END
INSERT INTO #PayCatSum
VALUES (@lnEmpID, @lcLowStart, @lcHighEnd, @lcLastID, @lnNumRec)
SELECT * FROM #PayCatSum
______________________________________________________
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: Wednesday, March 13, 2002 6:10 PM
> To: CF-Talk
> Subject: Re: SQL help please
>
>
> Nope, that doesn't work.
>
> ----- Original Message -----
> From: laszlo <[EMAIL PROTECTED]>
> Date: Wednesday, March 13, 2002 4:06 pm
> Subject: Re: SQL help please
>
> > select ... count(paycatid) as samepaycatid.... group by paycatid
> >
> > laszlo
> >
> >
> > [EMAIL PROTECTED] wrote:
> >
> > > 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.
> > > >
> > > >
> > > >
> > >
> >
>
______________________________________________________________________
Get Your Own Dedicated Windows 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=coldfusionb
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
