assigning sequence numbers to records

hey all,
i have a table with about 1300 rows
the records are grouped by a type and i want to
assign each type a numbering sequence. what's the best way to do this?
For instance,
Rec1 Type1 Seq1
Rec2 Type1 Seq2
Rec3 Type2 Seq1
Rec4 Type2 Seq2
Rec5 Type2 Seq3
The seq columns is what i'm trying to incorporate.
thanks,
rodcharI assume from your post that the records are already sequenced globally...
Then you can do this for output only with a query
Select RecSeq, Type,
(Select Count(*) From TableName
Where Type = T.Type
And RecSeq <= T.RecSeq) TypeSeq
From TableName T
Or if you have another column in the table that you want to actually
populate with the value
Then
Update T Set TypeSeq =
(Select Count(*) From TableName
Where Type = T.Type
And RecSeq <= T.RecSeq)
From TableName T
"rodchar" wrote:

> hey all,
> i have a table with about 1300 rows
> the records are grouped by a type and i want to
> assign each type a numbering sequence. what's the best way to do this?
> For instance,
> Rec1 Type1 Seq1
> Rec2 Type1 Seq2
> Rec3 Type2 Seq1
> Rec4 Type2 Seq2
> Rec5 Type2 Seq3
> The seq columns is what i'm trying to incorporate.
> thanks,
> rodchar
>
>|||We need to know if there is another column we can using to uniquely identity
a row in a group.
How to dynamically number rows in a SELECT Statement
http://support.microsoft.com/defaul...kb;en-us;186133
AMB
"rodchar" wrote:

> hey all,
> i have a table with about 1300 rows
> the records are grouped by a type and i want to
> assign each type a numbering sequence. what's the best way to do this?
> For instance,
> Rec1 Type1 Seq1
> Rec2 Type1 Seq2
> Rec3 Type2 Seq1
> Rec4 Type2 Seq2
> Rec5 Type2 Seq3
> The seq columns is what i'm trying to incorporate.
> thanks,
> rodchar
>
>|||I don't think I'm doing something right here cause it's not working.
Please let me explain a different way to make sure:
Rec1 Type Seq#
-- -- --
1 A
3 B
2 A
4 A
5 B
After should look like the following:
Rec1 Type Seq#
-- -- --
1 A 1
2 A 2
4 A 3
3 B 1
5 B 2
The Seq# field is a new field that I need numbered sequentially by Types
So you're saying that the update statement in the above reply should do this
?
thanks,
rodchar
"CBretana" wrote:
> I assume from your post that the records are already sequenced globally...
> Then you can do this for output only with a query
> Select RecSeq, Type,
> (Select Count(*) From TableName
> Where Type = T.Type
> And RecSeq <= T.RecSeq) TypeSeq
> From TableName T
> Or if you have another column in the table that you want to actually
> populate with the value
> Then
> Update T Set TypeSeq =
> (Select Count(*) From TableName
> Where Type = T.Type
> And RecSeq <= T.RecSeq)
> From TableName T
>
> "rodchar" wrote:
>|||The "new" field that you want populated must already exist in the Table
first. Have you added it? If you have named it "SeqNo" then
Update T Set SeqNo=
(Select Count(*) From TableName
Where Type = T.Type
And Rec1 <= T.Rec1)
From TableName T
What error are you getting ?
"rodchar" wrote:
> I don't think I'm doing something right here cause it's not working.
> Please let me explain a different way to make sure:
> Rec1 Type Seq#
> -- -- --
> 1 A
> 3 B
> 2 A
> 4 A
> 5 B
> After should look like the following:
> Rec1 Type Seq#
> -- -- --
> 1 A 1
> 2 A 2
> 4 A 3
> 3 B 1
> 5 B 2
> The Seq# field is a new field that I need numbered sequentially by Types
> So you're saying that the update statement in the above reply should do th
is?
> thanks,
> rodchar
> "CBretana" wrote:
>|||I'm not getting an error message however the value for the new field contain
s
that maximum number (1372) for all the records:
Here's my actual statement:
UPDATE Records
SET SeqNo =
(SELECT COUNT(*)
FROM Records
WHERE Department = Records.Department AND
RecID <= Records.RecID)
FROM Records
So, here we have
RecID (like Rec1 is just an autonumber field)
Department is the type
"CBretana" wrote:
> The "new" field that you want populated must already exist in the Table
> first. Have you added it? If you have named it "SeqNo" then
> Update T Set SeqNo=
> (Select Count(*) From TableName
> Where Type = T.Type
> And Rec1 <= T.Rec1)
> From TableName T
>
> What error are you getting ?
>
> "rodchar" wrote:
>|||On Tue, 5 Apr 2005 11:55:03 -0700, rodchar wrote:

>I'm not getting an error message however the value for the new field contai
ns
>that maximum number (1372) for all the records:
>Here's my actual statement:
>UPDATE Records
>SET SeqNo =
> (SELECT COUNT(*)
> FROM Records
> WHERE Department = Records.Department AND
>RecID <= Records.RecID)
>FROM Records
(snip)
You omitted the table alias from CBretana's solution. Try this one
instead (not exactly the same as CBretana's suggestion - I changed it to
use ANSI-standard instead of proprietary Transact-SQL syntax):
UPDATE Records
SET SeqNo = (SELECT COUNT(*)
FROM Records AS R
WHERE R.Department = Records.Department
AND R.RecID <= Records.RecID)
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Because you're not using an alias for the tablename in the outer query... Th
e
way you wrote it,
UPDATE Records SET
SeqNo = (SELECT COUNT(*)
FROM Records
WHERE Department = Records.Department
AND RecID <= Records.RecID) -- THIS IS SAME AS X <=X
FROM Records
The token <Records> in the Last part of the subquery's where clause refers
to the Records table IN the subquery, so you're saying Where RecID in The
Subquery Record <= Same RecID in Sam eSubqueryRecord... This part need s to
ask
" Where RecID in SubQuery Record <= RecID in teh OUTER Query Record. That's
why you need to use Table ALias
UPDATE R SET
SeqNo = (SELECT COUNT(*)
FROM Records
WHERE Department = R.Department
AND RecID <= R.RecID)
FROM Records As R
"rodchar" wrote:
> I'm not getting an error message however the value for the new field conta
ins
> that maximum number (1372) for all the records:
> Here's my actual statement:
> UPDATE Records
> SET SeqNo =
> (SELECT COUNT(*)
> FROM Records
> WHERE Department = Records.Department AND
> RecID <= Records.RecID)
> FROM Records
> So, here we have
> RecID (like Rec1 is just an autonumber field)
> Department is the type
>
> "CBretana" wrote:
>|||thank you all, I give a try and let you know.
"CBretana" wrote:
> Because you're not using an alias for the tablename in the outer query...
The
> way you wrote it,
> UPDATE Records SET
> SeqNo = (SELECT COUNT(*)
> FROM Records
> WHERE Department = Records.Department
> AND RecID <= Records.RecID) -- THIS IS SAME AS X <=X
> FROM Records
> The token <Records> in the Last part of the subquery's where clause refer
s
> to the Records table IN the subquery, so you're saying Where RecID in The
> Subquery Record <= Same RecID in Sam eSubqueryRecord... This part need s t
o
> ask
> " Where RecID in SubQuery Record <= RecID in teh OUTER Query Record. That'
s
> why you need to use Table ALias
> UPDATE R SET
> SeqNo = (SELECT COUNT(*)
> FROM Records
> WHERE Department = R.Department
> AND RecID <= R.RecID)
> FROM Records As R
> "rodchar" wrote:
>|||That's awesome, hey any recommendations on a good book or how to get up to
speed in knowing how to write statements like these?
thanks so much for everyone's help here,
rodchar
"CBretana" wrote:
> Because you're not using an alias for the tablename in the outer query...
The
> way you wrote it,
> UPDATE Records SET
> SeqNo = (SELECT COUNT(*)
> FROM Records
> WHERE Department = Records.Department
> AND RecID <= Records.RecID) -- THIS IS SAME AS X <=X
> FROM Records
> The token <Records> in the Last part of the subquery's where clause refer
s
> to the Records table IN the subquery, so you're saying Where RecID in The
> Subquery Record <= Same RecID in Sam eSubqueryRecord... This part need s t
o
> ask
> " Where RecID in SubQuery Record <= RecID in teh OUTER Query Record. That'
s
> why you need to use Table ALias
> UPDATE R SET
> SeqNo = (SELECT COUNT(*)
> FROM Records
> WHERE Department = R.Department
> AND RecID <= R.RecID)
> FROM Records As R
> "rodchar" wrote:
>

assign an output of derive column to variable

I have a derive column( sequence) transformation in data flow , i am trying to assign this column to a variable , so that i can use it in the SQLtask control flow... how can i do this? can you show me some examples?

Why are you trying to assign a value from a derived column to a variable? The derived column works for every row passing through it. When passing a variable value back to the control flow, it would only contain the last value in the data flow.

With that said, you can use a script component's PostExecute sub to assign data to a variable.|||

Actually the derived column is in For each loop ( row by row process)...

"script component's PostExecute sub to assign data to a variable"

--> is there a code example that you can show me ?

Thank you for your help

|||

safddddddddddddddddddddd wrote:

Actually the derived column is in For each loop ( row by row process)...

No it isn't. You might have a data flow in the foreach loop.

The foreach loop can assign variables for each iteration. Why doesn't that work for you?|||

you are correct I meant I have a data flow which has the derive column transformation in the foreach loop..

I am tying to tryting to pass the result of derive column ( @.[User::Assessment_Dt] > assessment_dt ? 1 : 0 ) to a variable so that i can use it in SQLTASK ..

I understand i need to use a script task to assign the output of derive column in data flow to a variable ..but i don't know how? is there any examples that i can see?

|||What is the source for the foreach loop?

What is the source in the data flow?

I don't understand what you are trying to achieve, and I'd really like to help you architect this better.|||

The source of the foreach loop is

Select columns from S_enrollment_assessment

and i am not quite sure how to create the data source for the data flow in the for each loop since it it assign to row by row ?

|||

safddddddddddddddddddddd wrote:

The source of the foreach loop is

Select columns from S_enrollment_assessment

and i am not quite sure how to create the data source for the data flow in the for each loop since it it assign to row by row ?

So you're populating a data set with an execute SQL task and then using a foreach loop against that dataset?|||

yes.. and i assigned each columns to variables ( 71 of them) and follow the complex loding process..

for example,

FINDSTRING("1,2,3,4",@.TypeofAssessment,1) > 0 in precedence editor - expression

then go the SQLTASK1 otherwise go to SQLTASK2

and there are so many different logics that i need to follow...

so how to use 71 variables as the source of data flow in for each loop?

|||

safddddddddddddddddddddd wrote:

yes.. and i assigned each columns to variables ( 71 of them) and follow the complex loding process..

for example,

FINDSTRING("1,2,3,4",@.TypeofAssessment,1) > 0 in precedence editor - expression

then go the SQLTASK1 otherwise go to SQLTASK2

and there are so many different logics that i need to follow...

so how to use 71 variables as the source of data flow in for each loop?

You don't. You can write a SQL command in a variable expression that maps other variables (from your foreach loop) and then use the SQL variable as your OLE DB Source.

For instance:
@.TypeofAssessment - Source: Foreach loop
@.SQLCommand - Source: Expression: "select * from table where assessment = " + @.TypeofAssessment

OLE DB Source - Set to use sql command from variable - choose @.SQLCommand.|||yes. that is what i was trying to do...but since there are 71 columns that i am passing as variables,it's a kind of time consuming job.. thank you for all your help.. i really appreciate it..