Create a new column with criteria

Question

Create a new column with criteria

Biz

Right there with Babe

Points: 731
More actions
December 20, 2009 at 9:30 pm

#137771

I have a table with data like:
Group,Item,startdate,ID
I need to add a serial number for item if there are more than one item in the group and the serial number should start according to the start date.
For example : these are the two items whcih has same group called 'PGN-0312'
PGN-0312PIT-0068044055449A Unilink Business 7 - Offer by Institution22/2/20100062000000AO6rk
PGN-0312PIT-0068045066608M MetBusiness 7 - Offer by Institution 27/9/20100062000000ANdEX
How can I create the serial number column Say serial No 1 to the first one and No 2 to the second one ( Ordery by start date)
Can I use cursor?
Thanks

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Nabha SSCrazy Eights Points: 8534 More actions · Answer 1

Biz (12/20/2009)
I have a table with data like:
Group,Item,startdate,ID
I need to add a serial number for item if there are more than one item in the group and the serial number should start according to the start date.
For example : these are the two items whcih has same group called 'PGN-0312'
PGN-0312PIT-0068044055449A Unilink Business 7 - Offer by Institution22/2/20100062000000AO6rk
PGN-0312PIT-0068045066608M MetBusiness 7 - Offer by Institution 27/9/20100062000000ANdEX
How can I create the serial number column Say serial No 1 to the first one and No 2 to the second one ( Ordery by start date)
Can I use cursor?
Thanks

If I am not getting the requirement all wrong, then you dont have to use cursor. Row_number is what you need.

Select * , ROW_NUMBER() OVER (Partition by Group order by startdate desc) as Serial_no

FROM URTABLE

Please read this to see how to post the data,

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Its more likely that you will get several responses if you post data like this.

---------------------------------------------------------------------------------

Biz Right there with Babe Points: 731 More actions · Answer 2

Hi,

Thank you for your response, I am using SQL2000 and so I cant user Row number().

I will explain again, The table structure I have is:

CREATE TABLE [dbo].[PKg](

[ID] [int] IDENTITY(1,1) NOT NULL,

[PackageGroup] [nvarchar](255) NULL,

[PackageItem] [nvarchar](255) NULL,

[ItemSrNo] [int] NULL,

[Name] [nvarchar](255) NULL,

[Stage] [nvarchar](255) NULL,

[Course Start Date] [datetime] NULL,

[OpportunityID] [nvarchar](255) NULL

) ON [PRIMARY]

I need to update [ItemSrNo] [int] NULL column. This is the serial number for the [PackageItem].

The [PackageGroup] can have more than one [PackageItem].If it is more than one, I need to update the column to 1 to number of occurance of [PackageItem].

Hope you got me.

Thanks

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 3

This is an "Ordinal Rank" problem. The following has your SQL Server 2000 answer along with a code example and all the things you must do to pull this off in a very high performance manner.

http://www.sqlservercentral.com/articles/T-SQL/68467/

Or, you can use a cursor which will be much slower... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Biz Right there with Babe Points: 731 More actions · Answer 4

Hi Jeff,

Thank you so much.

The document is excellent, it gave me a good insight of many things and solved my issue.

I took the following bit from document :

--===== Declare the working variables

DECLARE @PrevAccountID INT

DECLARE @AccountRunningTotal MONEY

DECLARE @AccountRunningCount INT

UPDATE dbo.TransactionDetail

SET @AccountRunningTotal = AccountRunningTotal =

CASE

WHEN AccountID = @PrevAccountID

THEN @AccountRunningTotal + Amount

ELSE Amount

END,

@AccountRunningCount = AccountRunningCount =

CASE

WHEN AccountID = @PrevAccountID

THEN @AccountRunningCount + 1

ELSE 1

END,

@PrevAccountID = AccountID FROM dbo.TransactionDetail WITH (TABLOCKX) OPTION (MAXDOP 1)

GO

and changed to my version:

ALTER PROCEDURE Update_SrNo

-- Add the parameters for the stored procedure here

AS

BEGIN

SET NOCOUNT ON;

DECLARE @PrevPakageGroupID varchar(255)

DECLARE @ItemSrNoCount INT

UPDATE dbo.Pkg

SET @ItemSrNoCount = ItemSrNo =

CASE

WHEN PackageGroup = @PrevPakageGroupID

THEN @ItemSrNoCount + 1

ELSE 1

END,

@PrevPakageGroupID = PackageGroup FROM dbo.PKg WITH (TABLOCKX) OPTION (MAXDOP 1)

END

And it worked perfectly well..

Thank you

Biz Right there with Babe Points: 731 More actions · Answer 5

Hi Guys,

I am still modifying this, though it is giving the serial number, I need to get the order of the serial number by the [course start date].

So trying to figure it out.

Any clues?

Thanks.

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 6

Biz (12/22/2009)
Hi Guys,
I am still modifying this, though it is giving the serial number, I need to get the order of the serial number by the [course start date].
So trying to figure it out.
Any clues?
Thanks.

Yes... the clustered index on the table must be in the precise order that you want the update to occur in. If you cannot change it on the original table for some reason, use SELECT/INTO to build a temp table and put the correct clustered index on that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Biz Right there with Babe Points: 731 More actions · Answer 7

Hi Jeff,

Thats working... Was thinking to sort it dynamically and then do this.

But this is really easy..

Thanks

Merry Xmas..

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 8

Biz (12/23/2009)
Hi Jeff,
Thats working... Was thinking to sort it dynamically and then do this.
But this is really easy..
Thanks
Merry Xmas..

Thanks for the feedback. "Easy" is good. 😉 Shifting gears a bit, I don't believe trying to "sort it dynamically" would work... I pretty much covered that in the article. The SELECT/INTO a temp table and building the required clustered index in the correct order would probably be just as fast even if you could sort it dynamically.

And Merry Xmas to you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Biz Right there with Babe Points: 731 More actions · Answer 9

Hi Jeff,

I am having similar issue again, but this time,

I need to upate the fileds according to two different fileds occurance.

I have feilds called accounts, package.

If the accounts and package fields are same,it needs to check the fields 'opportunity' and according to the value of oppotunity filed, i need to update another three fields in which one field is , if it is the first one in the group ( same accounts and package) , it should be null, second should be a different string values and if it is third another string value.

I am trying to modify the ruuning total one.

is that the best way>

any other idea??

Biz Right there with Babe Points: 731 More actions · Answer 10

I am adding the spec I have got , i think that may help to get an idea:

- The script will identify the Account that belong to only one package group (use package group No) then

- If the stage for any opportunity in that package group is >=8, invoiced, or credited

Update Accept_Offer__c ='True' (new field)

Update Offer_Letter_Name__c= The first opportunity name in the package base on the course start date (new field)

Update the New Stage (new field) for the rest of opportunity that belong to this package group to

If current Stage = 6 - Offer by Institution (Unconditional) then update New Stage to 10 - Unconditional Offer Accepted

if current stage = 7 - Offer by Institution (Conditional) then update New Stage to 8 - Conditional Offer Accepted

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 11

All that's good but I don't understand what you understand. Please read and heed the article located at the first link in my signature line below so we can not only help but maybe even provide a tested solution.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Biz Right there with Babe Points: 731 More actions · Answer 12

Hi Jeff,

You mean more clarity on my doubt? or data?

thanks

B

Biz Right there with Babe Points: 731 More actions · Answer 13

Hi Jeff,

The condition for the query is :

if the account and package group is same, then if any opportunity in the package group stage >=8

then

set the accept_offer__c=true

for other opportunities in the group, if

stage=6, then set to 10

stage=7, then set to 8

please let me know how can i add some sample data ??

thanks

Biz

Biz Right there with Babe Points: 731 More actions · Answer 14

CREATE TABLE [PackageData] (

[Package Grp No] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,

[Package Item No] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,

[Opportunity Name] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,

[Opportunity Stage] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,

[Course Start Date] [datetime] NULL ,

[Opportunity ID] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,

[Group: ID] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,

[Account] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,

[PackageID] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL

) ON [PRIMARY]

GO

/code]

THis is the table structure that i have data to update with new columns.

So I am thinking to add three new fields in to this structure first giving null values and then update those fields according to the condition.

If the [Account] and [Package Grp No] are same, then update fields

Accept_Offer__c ='True'

Update Offer_Letter_Name__c= The first opportunity name in the package base on the course start date- Here I want your help to take the first one according to date.

Hope this will help.

Thanks

Biz