Add Value to first of many identical records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Add Value to first of many identical records

Author

Topic

dosteroid
Starting Member

29 Posts

Posted - 2012-03-07 : 05:10:33

I need help with a little query

Select SDDOCO,SDFEA from F4211
Returns
SDDOCO SDFEA
110002 100000
110002 5000
110002 75000
110003 8000
110003 9000

I need to add the value 1 in a new column, one for each "set" of SDDOCO (so that there's only a count for one of many SDDOCO), how can I do that?

Result should be
SDDOCO SDFEA DOC_COUNT
110002 100000 1
110002 5000 0
110002 75000 0
110003 8000 1
110003 9000 0

I was thinking to use something like:

Select SDDOCO,SDFEA
,case when count(sddoco)>1 then "add a number to the first of SDDOCO statement" else 0 end DOC_COUNT
from F4211

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-07 : 05:13:49

Why do you think you need this?
How do you determine which row is "the first"?

No, you're never too old to Yak'n'Roll if you're too young to die.

dosteroid
Starting Member

29 Posts

Posted - 2012-03-07 : 05:19:21

I need it in a report to determine how many orders has been delivered on time or not and to to that I first need to count our orders, but I can't just add a 1 to each order, because an order can be splitted up into several orders, but with the same orderNo....

dosteroid
Starting Member

29 Posts

Posted - 2012-03-07 : 05:30:23

I could use a group by, but the user wants the ability to see all records

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-07 : 05:48:06

what webfred asking is how do you determine


110003 8000 1   <- this is the first record
110003 9000 0   <- and not this ?

KH
[spoiler]Time is always against us[/spoiler]

dosteroid
Starting Member

29 Posts

Posted - 2012-03-07 : 06:04:21

It actually doesn't matter if it's the first or the last as long as I only return one value for each "set" of orders...
110003 8000 1 <- this is the first record
110003 9000 0 <- or this is the first record, doesn't matter

Result can be either:
110003 8000 1
110003 9000 0

or

110003 8000 0
110003 9000 1

Cheers

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-07 : 06:17:28

[code]
Select SDDOCO, SDFEA ,
DOC_COUNT = case when row_number() over (partition by SDDOCO order by SDDOCO) = 1 then 1 else 0 end
from F4211
[/code]

KH
[spoiler]Time is always against us[/spoiler]

dosteroid
Starting Member

29 Posts

Posted - 2012-03-07 : 06:46:25

Thank s alot... I was actually looking at Row_number() over ... but I don't fully understand it to write it by my own...

dosteroid
Starting Member

29 Posts

Posted - 2012-03-08 : 04:29:47

Hi again... It works perfect, but I just found out that there can be identical OrderNo (SDDOCO) for each Site/Company... Meaning there can be an Order 110003 for company US and an Order 110003 for company EU... As the statement is now it will only Add 1 to DOC_COUNT for one of the Orders... Is there a way to modify the statement, if I also add another Column for Company, so that DOC_COUNT add 1 to both US and EU for the same OrderNo?
SDDOCO, SDFEA, SDCOMPANY
DOC_COUNT = case when row_number() over (partition by SDDOCO order by SDDOCO) = 1 then 1 else 0 end

Result should be:
110002 6000 US 1
110002 1000 US 0
110003 8000 EU 1
110003 9000 US 1

dosteroid
Starting Member

29 Posts

Posted - 2012-03-08 : 04:32:10

Nevermind, I guess the following will do what Im after:
SDDOCO, SDFEA, SDCOMPANY
DOC_COUNT = case when row_number() over (partition by SDDOCO,SDKCOO order by SDDOCO,SDKCOO) = 1 then 1 else 0 end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources