CODEWITHSUNDEEP
sqlsql-servert-sql
PJD
PJD

Reputation: 775

ROW_NUMBER & PARTITION

I am trying to use ROW_NUMBER OVER PARTITION to blank out certain values unless someone can suggest a better way, my data looks something like this

create table Emp_Details
(  Emp_Name varchar(10)
, Company varchar(15)
,Qty int
,LineOrderCost money
,OrderUnitCost money
,ExtraCost money
,ExtraDesc varchar(5)
) 

insert into Emp_Details (Emp_Name, Company, Qty, LineOrderCost, OrderUnitCost, ExtraCost, ExtraDesc)
values 
('John', 'PStock', 1, '2.50','3.50', '7.50', 'ABC')
,('John', 'PStock', 1, '2.50','3.50', '7.50', 'DEF')

What I want to do is return both records but set the LineOrderCost, OrderUnitCost, ExtraCost to 0 on the second line or if there are more than just 2 lines all following lines after the first one, hence when Emp_Name, Company and Qty are the same but the ExtraDesc is different blank out those values as described.

I apologies if I have not described this to well but in my head it makes some sort of sense :-)

Thanks P

Upvotes: 1

Views: 225

Answers (1)

Amit Singh
Amit Singh

Reputation: 8129

 Select EMP_NAME,COMPANY,QTY, EXTRADESC,
    Case when row>1 then 0 else ORDERUNITCOST end as ORDERUNITCOST,
    Case when row>1 then 0 else LINEORDERCOST end as LINEORDERCOST,
    Case when row>1 then 0 else EXTRACOST end as EXTRACOST
    from
    (Select Row_Number() Over(Partition By EMP_NAME order by EMP_NAME) as row,* from 
    Emp_Details) t

Sql Fiddle Demo

Upvotes: 2

Related Questions