select next record? - 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

Transact-SQL (2000)

select next record?

Author

Topic

deadfish
Starting Member

38 Posts

Posted - 2003-03-28 : 04:52:36

Hi,

Is there any way to process the records in a table one by one besides using a cursor?

Thanks!

nr
SQLTeam MVY

12543 Posts

Posted - 2003-03-28 : 05:32:43

Take the primary key and loop through it using variables.
Get all the records into a temp table with an identity and loop through the identity using a variable.

If you need to do this there is maybe something wrong with your design. Or maybe you should be doing it in an application rather than sql.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

deadfish
Starting Member

38 Posts

Posted - 2003-03-28 : 11:39:37

Thanks nr..

But I still can't understand...

Suppose I have the following table:

EMPLOYEE DEPT PROJECTS
--------------------------
E1 D1 P1
E2 D2 P2
E3 D3 P4
..........

How to process all the records one by one?

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-28 : 11:43:22

I guess what Nigel is referring to is...

What's do you want to process? For example what business rules do you have that will effect each record?

For example, for all odd number project will belong Department X, and all others will belong Y...

Let us know

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-28 : 11:56:52

Yes, please define the word "Process".

If it's

"print on a page" -- use a client tool like Access, Crystal reports, an ASP page, etc, for reporintg

"print to the screen" -- using query analzyer, use the SELECT statement.

"update it somehow" -- look at the syntax and examples of the SQL UPDATE statement

"get a summary, add up totals" -- look at the SELECT statement and the GROUP BY clause

"delete certain rows" -- look at the DELETE command

"copy to another table" -- look at INSERT INTO

"create a temp table" -- look at SELECT ... INTO

"see if a row matches in another table" -- look at SELECT and JOINs

Also, look at the WHERE clause for all of the above to make sure you only "process" rows that you want.

If that doesn't help, let us know what you're trying to do.

- Jeff

deadfish
Starting Member

38 Posts

Posted - 2003-03-29 : 03:50:04

Thanks Jeff.

Actually I need to do some calculations on each record and insert it to another temp table..

I need to do a summarize report which summarize the working hours for each employee. I need to do the calculations one by one because I need to sepearate the working hours by days.

I need to fill in a table which stores the working days of each employee in the following format:

EMPLOYEE SUN MON TUE WED THU FRI SAT
----------------------------------------------------
E1 5 7 3 .........
E2 1 2 3 ........

How to do this if I don't want to use a cursor?

Bambola
Posting Yak Master

103 Posts

Posted - 2003-03-29 : 07:58:43

Sounds like something that can be done without a cursor. If you show us how you would process one of those rows, and explain the tables structure, we can come up with a better solotion than using a cursor.

Bambola.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-29 : 09:06:39

INSERT INTO WeeklyHours (Employee, Sun, Mon, Tue, Wed, Thu, Fri, Sat)
SELECT Employee,
SUM(CASE DatePart(dw, CheckIn) WHEN 1 THEN DateDiff(hour, CheckIn, CheckOut) ELSE 0 END) AS Sun,
SUM(CASE DatePart(dw, CheckIn) WHEN 2 THEN DateDiff(hour, CheckIn, CheckOut) ELSE 0 END) AS Mon,
SUM(CASE DatePart(dw, CheckIn) WHEN 3 THEN DateDiff(hour, CheckIn, CheckOut) ELSE 0 END) AS Tue,
SUM(CASE DatePart(dw, CheckIn) WHEN 4 THEN DateDiff(hour, CheckIn, CheckOut) ELSE 0 END) AS Wed,
SUM(CASE DatePart(dw, CheckIn) WHEN 5 THEN DateDiff(hour, CheckIn, CheckOut) ELSE 0 END) AS Thu,
SUM(CASE DatePart(dw, CheckIn) WHEN 6 THEN DateDiff(hour, CheckIn, CheckOut) ELSE 0 END) AS Fri,
SUM(CASE DatePart(dw, CheckIn) WHEN 7 THEN DateDiff(hour, CheckIn, CheckOut) ELSE 0 END) AS Sat
FROM TimeSheet
WHERE CheckIn BETWEEN '03/16/2003' AND '03/22/2003 23:59:59'
GROUP BY Employee

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-29 : 09:14:04

Also, it sounds like you are just reporting.

Consider that fact that you don't need to store this reporting data in another table to report off it -- just leave it is a regular SELECT statement and use that as the basis for your reports. Also, read about VIEWs, which can be written once and any time you want to view your raw data in this format, you just open the VIEW and it is a real-time transformation of your raw data into the format you want.

- Jeff

deadfish
Starting Member

38 Posts

Posted - 2003-03-29 : 10:46:43

Thanks everyone!

Actually I have tried to think about not to use a cursor. But seems I can't do that when I find some records like this:

EMPLOYEE IN_TIME OUT_TIME
---------------------------------------------------------
E1 2002-09-22 20:00:00.000 2002-09-23 03:00:00.000

I have to have the result:

SUN MON TUE WED THU FRI SAT
----------------------------------------------
4 3

Which means 4 hours on 22nd Sept and 3 hours on 23rd Sept...I can't just simply sum up all the records from employee because an employee may have more than one record that the OUT_TIME may not be on another day...

Is there any better way than using a cursor? Please help me...
Thanks!

Bambola
Posting Yak Master

103 Posts

Posted - 2003-03-29 : 16:14:19

There must be a better solution, but here is what I have so far.

DECLARE @TimeSheet table
(EMPLOYEE varchar(5), IN_TIME datetime, OUT_TIME datetime)

INSERT INTO @TimeSheet SELECT 'E1', '2002-09-21 13:00:00.000', '2002-09-21 14:00:00.000'
INSERT INTO @TimeSheet SELECT 'E2', '2002-09-20 20:00:00.000', '2002-09-21 03:00:00.000'
INSERT INTO @TimeSheet SELECT 'E1', '2002-09-22 08:00:00.000', '2002-09-22 16:00:00.000'
INSERT INTO @TimeSheet SELECT 'E1', '2002-09-22 17:00:00.000', '2002-09-22 18:00:00.000'
INSERT INTO @TimeSheet SELECT 'E1', '2002-08-20 20:00:00.000', '2002-08-21 05:00:00.000'
INSERT INTO @TimeSheet SELECT 'E2', '2002-09-21 04:00:00.000', '2002-09-21 07:00:00.000'

-- within the same day
SELECT EMPLOYEE, THE_DAY, SUM(HOURS)
FROM (
SELECT Employee
, DATEADD(dd, DATEDIFF(dd,0,IN_TIME),0) the_day
, SUM(DATEDIFF(hour, IN_TIME, OUT_TIME)) hours
FROM @TimeSheet
WHERE IN_TIME BETWEEN '2002-08-01' AND '2002-09-24 23:59:59'
AND DATEDIFF (dy, IN_TIME, OUT_TIME) = 0
GROUP BY Employee, DATEADD(dd, DATEDIFF(dd,0,IN_TIME),0)

UNION ALL

-- IN_TIME to midnight.
SELECT Employee
, DATEADD(dd, DATEDIFF(dd,0,IN_TIME),0) the_day
, SUM(DATEDIFF(hour, IN_TIME, DATEADD(dd, DATEDIFF(dd,0,OUT_TIME),0))) hours
FROM @TimeSheet
WHERE IN_TIME BETWEEN '2002-08-01' AND '2002-09-24 23:59:59'
AND DATEDIFF (dy, IN_TIME, OUT_TIME) = 1
GROUP BY Employee, DATEADD(dd, DATEDIFF(dd,0,IN_TIME),0)

-- midnight to OUT_TIME
UNION ALL

SELECT Employee
, DATEADD(dd, DATEDIFF(dd,0,OUT_TIME),0) the_day
, SUM(DATEDIFF(hour, DATEADD(dd, DATEDIFF(dd,0,OUT_TIME),0), OUT_TIME)) hours
FROM @TimeSheet
WHERE IN_TIME BETWEEN '2002-08-01' AND '2002-09-24 23:59:59'
AND DATEDIFF (dy, IN_TIME, OUT_TIME) = 1
GROUP BY Employee, DATEADD(dd, DATEDIFF(dd,0,OUT_TIME),0)
) SubSet
GROUP BY EMPLOYEE, THE_DAY
ORDER BY 1, 2

Bambola.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources