Help with loop - 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)

Help with loop

Author

Topic

kevinxyx
Starting Member

11 Posts

Posted - 2011-08-23 : 00:34:11

how to display multiple tables from my time keeping table

example.

<table>
EmpID
1
1
1
1
1
1
</table>

<table>
EmpID
2
2
2
2
2
2
</table>

<table>
EmpID
3
3
3
3
3
3
3
</table>

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 00:38:07

sorry your question is not clear please elaborate on what you mean by

multiple tables from my time keeping table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kevinxyx
Starting Member

11 Posts

Posted - 2011-08-23 : 01:39:50

sorry what i mean is, how to display my timekeeping table in separate tables by its empid

example timekeeping table, fields

empid date timein time out
1 7/22/2011 8:30AM 12:00PM
1 7/23/2011 9:00AM 1:00PM
2 7/24/2011 12:00PM 3:00PM
2 7/25/2011 1:00PM 3:00PM
3 7/26/2011 9:00AM 12:00PM
3 7/26/2011 1:00PM 3:00PM

so it will be like this
<table>
empid date timein time out
1 7/22/2011 8:30AM 12:00PM
1 7/23/2011 9:00AM 1:00PM
</table>

<table>
empid date timein time out

2 7/24/2011 12:00PM 3:00PM
2 7/25/2011 1:00PM 3:00PM
</table>

<table>
empid date timein time out
3 7/26/2011 9:00AM 12:00PM
3 7/26/2011 1:00PM 3:00PM
</table>

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 02:05:22

thats easier do like

insert into table1
select * from timekeeping where empid=1

insert into table2
select * from timekeeping where empid=2

....

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kevinxyx
Starting Member

11 Posts

Posted - 2011-08-23 : 04:14:12

thanks visakh, i need to inner join 3 tables

SELECT a.EmpID, a.FirstName, a.MiddleName, a.LastName, b.College_Name, C.Dept_Name
FROM Employee a INNER JOIN
College b INNER JOIN
PLP_DEPARTMENT C ON a.RECID = b.COLLEGE ON a.RECID = b.DEPT_NAME

getting error "column prefix 'a' does not match with a table name"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 04:26:06

try this


SELECT a.EmpID, a.FirstName, a.MiddleName, a.LastName, b.College_Name, C.Dept_Name
FROM Employee a INNER JOIN
College b ON a.RECID = b.COLLEGE 
INNER JOIN
PLP_DEPARTMENT C ON a.  c.RECID = b.DEPT_NAME

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kevinxyx
Starting Member

11 Posts

Posted - 2011-08-23 : 05:20:53

woah thanks again visahk, i have another problem

i want to delete the time in and time out where DLOG = 1, making it like this, marking the deleted to absent

can it be done? with delete statement

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 05:27:38

sorry didnt get that. Whats DLOG? I cant see that column in your sample.
Also do you have any column which determines whether emp is absent?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kevinxyx
Starting Member

11 Posts

Posted - 2011-08-23 : 05:45:44

dlog is on the last column

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 07:10:34

i think what you're asking is this

DELETE FROM timekeeping WHERE DLOG=1

first put select and see if it returns required records and then do delete
i didnt understand below

marking the deleted to absent

do you mean adding deleted details to another table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kevinxyx
Starting Member

11 Posts

Posted - 2011-08-23 : 09:46:55

do you mean adding deleted details to another table?

yes and marking them as absent

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 13:14:23

quote:
Originally posted by kevinxyx

woah thanks again visahk, i have another problem

i want to delete the time in and time out where DLOG = 1, making it like this, marking the deleted to absent

can it be done? with delete statement

for this what you need is update


UPDATE timekeeping 
SET TimeIn='Absent',
timeOut='Absent'
WHERE DLOG=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 13:15:24

quote:
Originally posted by kevinxyx

do you mean adding deleted details to another table?

yes and marking them as absent

for this you need a trigger if you're using sql 2000. if on or above sql 2005 you can use new OUTPUT clause

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources