|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
A Question on Inserting a column in SQLI wud claim my knowledge of sql anywhere between novice to intermediate. This is what I'm trying to do... Table Name: demo As is: Date Batch No Part No 05/02/03 154 1 05/02/03 154 2 05/03/03 155 3 05/04/03 155 4 05/04/03 155 5 To Be: Date Batch No Part No Inserted Column 05/02/03 154 1 1 05/02/03 154 2 2 05/03/03 155 3 1 05/04/03 155 4 1 05/04/03 155 5 2 So for every distinct batch no and date depedning on no of unique distinct values in Part No I want to create another column in which I want to insert the no 1 to unique distinct values. Please refer to the above table for example. I dont have a clue about how I would start writing such a query. Any help is appreciated.... Thanks Balaji If I correctly understand your request, it appears that you wish to have the
[Inserted Column] to contain sequentially numbering for each day. If that is correct, then I suggest that you combine a GROUP BY with the information you will find in this article: http://support.microsoft.com/default.aspx?scid=kb;en-us;186133 -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous <balaji.balara***@gmail.com> wrote in message news:1154400577.063988.137190@i42g2000cwa.googlegroups.com... > Hello everyone... > > I wud claim my knowledge of sql anywhere between novice to > intermediate. > > This is what I'm trying to do... > > Table Name: demo > > As is: > > Date Batch No Part No > 05/02/03 154 1 > 05/02/03 154 2 > 05/03/03 155 3 > 05/04/03 155 4 > 05/04/03 155 5 > > > To Be: > > Date Batch No Part No Inserted Column > 05/02/03 154 1 1 > 05/02/03 154 2 2 > 05/03/03 155 3 1 > 05/04/03 155 4 1 > 05/04/03 155 5 2 > > So for every distinct batch no and date depedning on no of unique > distinct values in Part No > I want to create another column in which I want to insert the no 1 to > unique distinct values. > > Please refer to the above table for example. > > I dont have a clue about how I would start writing such a query. > > Any help is appreciated.... > > Thanks > > Balaji > Hi,
Computer column wil be the best option in these scenario. But in your case COMPUTED column will not work out. Probably go for a insert trigger on table DEMO. Sample format, you will need to change slightly Create trigger ins_xx on Demo for insert as Begin declare @t int select @t = count(*) from demo group by convert(char,date,101),batchno update demo set ins_col= @t from inserted ins where demo.date = ins.date and xx.batchno = i.batchno end Thanks Hari SQL Server MVP <balaji.balara***@gmail.com> wrote in message Show quote news:1154400577.063988.137190@i42g2000cwa.googlegroups.com... > Hello everyone... > > I wud claim my knowledge of sql anywhere between novice to > intermediate. > > This is what I'm trying to do... > > Table Name: demo > > As is: > > Date Batch No Part No > 05/02/03 154 1 > 05/02/03 154 2 > 05/03/03 155 3 > 05/04/03 155 4 > 05/04/03 155 5 > > > To Be: > > Date Batch No Part No Inserted Column > 05/02/03 154 1 1 > 05/02/03 154 2 2 > 05/03/03 155 3 1 > 05/04/03 155 4 1 > 05/04/03 155 5 2 > > So for every distinct batch no and date depedning on no of unique > distinct values in Part No > I want to create another column in which I want to insert the no 1 to > unique distinct values. > > Please refer to the above table for example. > > I dont have a clue about how I would start writing such a query. > > Any help is appreciated.... > > Thanks > > Balaji >  |
|||||||||||||||||||||||
Other interesting topics