SQL Query: Assigning values

[sqlnew]

Dear Forum Members,

I am working on a SQL query for couple of days for which I am not able to arrive at a solution:

Below is the table structure:

Customer# Account # State CreditLimit
abc 1 NY 10,000
abc 2 CA 10,000
abc 3 NY 10,000
abc 4 GA 10,000

The requirement is that we need to find the State NY (only one record) and take the credit limit and rest of the record's credit limits should be flagged as 0 (zero). If the customer is not located in NY, we need to pick up the first account record's credit limit and mark the remaining records as zero.

I'm using CASE logic to find the first record, but I am not sure how to flag the other records with zero values.

 

[gmmastros]

What version of SQL Server are you using? It should be SQL2000, SQL2005, SQL2008, SQL2008R2. If you are not sure, then run:

Select @@Version and paste the output here.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sqlnew]

It is PL/SQL and I am using TOAD to run the queries. Thanks.

 

[gmmastros]

PL/SQL syntax is different from SQL Server in some respects. I would encourage you to find a forum that specializes in your particular flavor of SQL.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sqlnew]

Solution in SQL 2005 is fine as I can convert it into PL/SQL. It is the logic that I am having trouble.

 

[gmmastros]

Well... in SQL2005 I would use a combination of CTE and Ranking functions, like this:

Declare @Temp Table(Customer VarChar(10), Account Int, State VarChar(2), CreditLimit Int)

Insert Into @Temp Values('abc',1,'NY',10000)
Insert Into @Temp Values('abc',2,'CA',20000)
Insert Into @Temp Values('abc',3,'NY',30000)
Insert Into @Temp Values('abc',4,'GA',40000)

Insert Into @Temp Values('xyz',2,'CA',2000)
Insert Into @Temp Values('xyz',3,'PA',20000)
Insert Into @Temp Values('xyz',4,'GA',20000)

;With Data As
(
Select Customer, Account, CreditLimit, 
       Row_Number() Over(Partition By Customer 
                         Order By Case When State = 'NY' 
                                       Then 1 
                                       Else 2 End, Account) As RowNumber
From   @Temp
)
Update	T
Set     T.CreditLimit = Case When Data.RowNumber = 1 
                             Then Data.CreditLimit 
                             Else 0 End
From    @Temp T
        Inner Join Data
          On T.Customer = Data.Customer
          And T.Account = Data.Account
          
Select * From @Temp

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom