for each row in a table - 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

General SQL Server Forums

New to SQL Server Programming

for each row in a table

Author

Topic

bobz_0585
Yak Posting Veteran

55 Posts

Posted - 2009-06-07 : 03:51:15

i have a table containing a number of employees...and for each employee there is a field containing the number of activities they are performing..now i was doing it using the code in my vb plus sql commands in order to scan the activities table and store the result in the employee table but it was slowing down my application so i was asked to make it as a job that runs every 10 minutes...i know how to create a job and have everything ready in terms of calculation and updateing the mother table...but i dont know how to go through every row in my table..take the id(primary key from it and perform the operation accordingly) can u guys help me

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-07 : 03:53:14

what operation do you want to perform for each id in employee table? also is activity table separate or is activities just a column in your employee table?

bobz_0585
Yak Posting Veteran

55 Posts

Posted - 2009-06-07 : 04:05:29

no it is a colmn in the employee table in the employee table actually not the entire employee table but for a certain department the employees of that departmentare in a deferent table than the employee table..and the operation is very simple i would just get the count of activities(another table) for which status are not completed..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-07 : 04:15:12

sorry not fully clear. can you post data & o/p required in below format
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

bobz_0585
Yak Posting Veteran

55 Posts

Posted - 2009-06-07 : 04:32:06

ok i will make i clearer i have a table containing the employees of the engineering department..for each engineer i want to count how many pending activities does he have and store it in a column in the same table....the activities is stored in activities table...

when the job runs it takes every employee in the employee table and scans the activities table scans it and gets the number of pending activities using the following query

select count(*) from team_members1 join team_data1 on team_members1.team_identifier=team_data1.id1 join remote_list_view() f1 on f1.[node code]=team_members1.node_code where team_members1.Team_Member_Name=@name and f1.technology='SCPC' and team_data1.activity_status='pending'

and then i update the employee table and the @name parameter should contain the name of the employee..... from the loop

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-07 : 04:40:47

ok. i think it should be something like this


select count(*) from team_members1 
join team_data1 on team_members1.team_identifier=team_data1.id1 
join remote_list_view() f1 on f1.[node code]=team_members1.node_code 
join employee e
on team_members1.Team_Member_Name=e.employee_name
where  f1.technology='SCPC' 
and team_data1.activity_status='pending'

as of current stage, i cant suggest you more because i dont know how your data is and you havent posted the information in requested format

bobz_0585
Yak Posting Veteran

55 Posts

Posted - 2009-06-07 : 04:54:50

ok here is my first table

CREATE TABLE [dbo].[Group_Members](
	[ID] [nvarchar](15) COLLATE SQL_Latin1_General_CP1256_CI_AS NOT NULL,
	[EmpName] [nvarchar](60) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL,
	[Region] [nvarchar](50) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL,
	[Title] [nvarchar](50) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL,
	[Department] [nvarchar](50) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL,
	[count_scpc] [int] NULL,
	[count_skywan] [int] NULL,
	[count_skyarc] [int] NULL,
	[count_hughes] [int] NULL,
	[count_other] [int] NULL,
 CONSTRAINT [PK_Group_Members] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

this is the table containing the employees and each count_X should contain the count for each technology

table 2 has this defintion


CREATE TABLE [dbo].[Team_Members1](
	[Team_Identifier] [nvarchar](10) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL,
	[Team_Member_ID] [int] NULL,
	[Team_Member_Name] [nvarchar](60) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL,
	[Node_code] [nvarchar](50) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL,
	[Activity] [nvarchar](50) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL,
	[team_creationdate] [smalldatetime] NULL,
	[surrogate_key] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_Team_Members1] PRIMARY KEY CLUSTERED 
(
	[surrogate_key] ASC
) ON [PRIMARY]
) ON [PRIMARY]

team member name is the same as employee and here i get every activity there is
now what i want is to go through every employee in group_members1 get the number of activities that are pending and for each technology and store (update) in the group_members table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-07 : 05:15:48

[code]
UPDATE gm
SET gm.count_scpc=t1.count_scpc,
gm.count_skywan=t1.count_skywan,
gm.count_skyarc=t1.count_skyarc,
gm. count_hughes =t1.count_hughes,
gm. count_others =t1.count_others
from Group_Members gm
JOIN
(
select team_members1.Team_Member_Name,
SUM(CASE WHEN f1.technology='SCPC' THEN 1 ELSE 0 END) AS count_scpc,
SUM(CASE WHEN f1.technology='skywan' THEN 1 ELSE 0 END) AS count_skywan,
SUM(CASE WHEN f1.technology='skyarc' THEN 1 ELSE 0 END) AS count_skyarc,
SUM(CASE WHEN f1.technology='hughes' THEN 1 ELSE 0 END) AS count_hughes,
SUM(CASE WHEN f1.technology NOT IN ('SCPC','skywan','skyarc','hughes') THEN 1 ELSE 0 END) AS count_others
from team_members1
join team_data1
on team_members1.team_identifier=team_data1.id1
join remote_list_view() f1
on f1.[node code]=team_members1.node_code
where team_data1.activity_status='pending'
GROUP BY team_members1.Team_Member_Name
)t1
ON t1.Team_Member_Name =gm.Team_Member_Name
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources