Please help! - 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)

Please help!

Author

Topic

Shazam
Starting Member

2 Posts

Posted - 2002-04-09 : 17:08:51

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. I wrote a cursor to do this, but it's incredibly slow. Is there a way to do this without cursors?

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-04-09 : 17:32:07

In your data, would it be possible for two records to have the same startdate, enddate, paycatid?

What determines this "order." Can we see the select statement that returned this dataset?

The only solution I see right off is the cursor solution you currently have, but I'm kicking around a few others.

Michael

Shazam
Starting Member

2 Posts

Posted - 2002-04-09 : 17:35:23

quote:
In your data, would it be possible for two records to have the same startdate, enddate, paycatid?

Nope.

Here's the rather ugly SQL:


SELECT	b.employeeID, b.employeeNumber, b.FullName,
		left(convert(varchar(30), a.startDateTime, 20), 11) AS startDate,
		left(convert(varchar(30), a.endDateTime, 20), 11) AS endDate,
		-- Replace all the 801s with 11s to facilitate grouping.
		CASE	WHEN max(a.paycatid) = 801
			THEN 11
			ELSE max(a.paycatid)
		END AS payCatID,
		e.Description
INTO		#@tmpEmployeeTimeRecords
FROM		srv_sql_hp.tkcsdb.dbo.total a, srv_sql_hp.tkcsdb.dbo.employee b, srv_sql_hp.tkcsdb.dbo.tkcsemployee c,
		srv_sql_hp.tkcsdb.dbo.laboracct d, srv_sql_hp.tkcsdb.dbo.laborlevelentry e
WHERE	b.employeeID = @employeeID
AND		a.enddatetime BETWEEN @pStartDate AND @pEndDate
-- Don't include dates where they were given a pay category that has nothing to do with them showing to work.
AND		a.paycatid NOT IN (SELECT paycatid FROM srv_sql_hp.tkcsdb.dbo.paycategory WHERE is_moneycat = 1)
AND		a.employeeid = b.employeeid
AND		b.employeeid = c.employeeid
AND		c.laboracctid = d.laboracctid
AND		d.laborlev5id = e.laborlevelentryid
GROUP BY 	b.employeeID, b.employeeNumber, b.FullName,
		left(convert(varchar(30), a.startDateTime, 20), 11),
		left(convert(varchar(30), a.endDateTime, 20), 11),
		e.Description
HAVING (count(left(convert(varchar(30), a.startDateTime, 20), 11)) = 1 AND count(left(convert(varchar(30), a.endDateTime, 20), 11)) = 1)

UNION

SELECT	b.employeeID, b.employeeNumber, b.FullName,
		left(convert(varchar(30), a.startDateTime, 20), 11) AS startDate,
		left(convert(varchar(30), a.endDateTime, 20), 11) AS endDate,
		0,
		e.Description
FROM		srv_sql_hp.tkcsdb.dbo.total a, srv_sql_hp.tkcsdb.dbo.employee b, srv_sql_hp.tkcsdb.dbo.tkcsemployee c,
		srv_sql_hp.tkcsdb.dbo.laboracct d, srv_sql_hp.tkcsdb.dbo.laborlevelentry e
WHERE	b.employeeID = @employeeID
AND		a.enddatetime BETWEEN @pStartDate AND @pEndDate
-- Don't include dates where they were given a pay category that has nothing to do with them showing to work.
AND		a.paycatid NOT IN (SELECT paycatid FROM srv_sql_hp.tkcsdb.dbo.paycategory WHERE is_moneycat = 1)
AND		a.employeeid = b.employeeid
AND		b.employeeid = c.employeeid
AND		c.laboracctid = d.laboracctid
AND		d.laborlev5id = e.laborlevelentryid
GROUP BY 	b.employeeID, b.employeeNumber, b.FullName,
		left(convert(varchar(30), a.startDateTime, 20), 11),
		left(convert(varchar(30), a.endDateTime, 20), 11),
		e.Description
HAVING	(count(left(convert(varchar(30), a.startDateTime, 20), 11)) > 1 AND count(left(convert(varchar(30), a.endDateTime, 20), 11)) > 1)

ORDER BY startDate, endDate

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-04-09 : 17:36:59

OMG!

I'll study that tonight and get back to ya :)

Michael

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources