Adding line sequence numbers

hi all

I am stuck with something that seems easy but im obviously clueless as how to execute the idea.

I have a custom table that houses invoices on the details level. So for example i have:

InvcNo
00000001
00000001
00000001
00000002
00000002
00000003

and so forth

What I am wanting to do in another column is keep track of the sequence number for each distinct invoice like:

SeqNo
1
2
3
1
2
1

I am working in a stored proc and i cant get past adding the numbers up at each line as a whole and not reseting when the next invoice number is present. Any help would be so greatly appreciated.

ThanksWill this help?
http://www.sqlservercentral.com/columnists/mcoles/sequentialordering.asp|||Thanks...but I dont have 2005 (

Is it possible to pull the example that is given in 2005 section in a prior version of SQL?|||Can you uniquely identify each record for a specific InvcNo?|||Yes, I have an InvcKey column set up|||create table #t1 (InvcKey int, InvcNo int)
insert into #t1 select
1,1 union all select
2,1 union all select
3,1 union all select
4,2 union all select
5,2 union all select
6,3

select count(*) 'SeqNo', a.InvcKey, a.InvcNo from #t1 a, #t1 b
where a.InvcNo=b.InvcNo
and a.InvcKey>=b.InvcKey
group by a.InvcKey, a.InvcNo

drop table #t1|||Ok, Ive got it...I should have played around some before asking further

SELECT COUNT(*) AS [SeqNo], c.InvcNo, c.InvcKey
FROM table c, table d
WHERE d.InvcKey <= c.InvcKey AND d.InvcNo = cInvcNo
GROUP BY c.InvcNo, c.InvcKey
ORDER BY c.InvcKey

Thanks for the help!

tibor|||You should probably use a JOIN.
Using

FROM table1, table2, ... , tableN

Is not advised.|||Being a self join, would I really need JOIN?|||Being a self join, would I really need JOIN?It is semantics. The affect it the same but the style you used is considered old fashioned in many quarters. Whether or not it is a self join is irrelevent.

Rather excitingly - you will have created a Theta Join when you have finished :)|||Rather excitingly - you will have created a Theta Join when you have finished :)

neat! normally you'd have to pay $360k to get to that level (http://en.wikipedia.org/wiki/Operating_Thetan)!

Adding information into one field that depends what is in another

Hi,

Im kind of stuck how to do this, i just tried using "when" and "then" commands in sql and its wiped my data.

My table is called "customer_info" and i have a field called "retailer". Retailer is dependant on a field called "clas".

Basically i would like to insert "4" into retailer where clas is equal to "glass".
And then reuse this command to do several more statements that are pretty much the same but just the values are changing.

Thanks in advance!Ha, i managed to sort it with a simple update query! No help needed now.

Cheers

Adding datetime fields

Hi

I'm learning SQL, stuck on a problem, and would be very grateful if someone could point me in the right direction please.

I have a table that contains employee overtime data. The table contains the employee ID number, the work week ID, basic hours, and overtime hours worked.
What i want to do is SUM(OThrs) for a particular employee to get the total OT hours worked in a given workweek.

However, as I understand it the datetime datatype stores its value as a value measured from a base date of Dec-30-1899. As it wouldn't make sense to add the datetime fields due to this, is there any way around it?

The OThrs is brought in from a csv file through a DTS package and is in the format of.... eg 07:45, 13:20, 02:12, 08:10

So if those times above were all for the same employee in the same work week, it would total 31:27

I'd be grateful for some poiters on this problem.
Thanks & Regards
MartyT

If you did not specify any date part in the datetime value, then the date portion will default to 1900-01-01. Confirm that this is the case for those values. Assuming this condition, you can do the following:

select t.EmployeeId, t.WorkWeekId,
convert(varchar(5), dateadd(minute, sum(datediff(minute, '', t.Othrs)), ''), 114) as total_ot_hours
from tbl as t
group by t.EmployeeId, t.WorkWeekId

Note that the above query only has resolution less than 24 hrs. If you need more than that, then take the minute value directly and generate the hour/minutes part yourself.|||That's a big help. Thanks for your time - much appreciated

Adding database to publication stops responding on first article

When adding a database to publications, the process gets stuck on the first
article of the database. This only happens on databases that were previously
replicated, others run through without the slightest problem. We also checked
the transactions issued with sp_who2 / trace, it looks like the transaction
goes into a loop of selecting and updating. We have checked all the
replication related system tables in an attempt to properly remove any traces
of previous publication, but to no avail. Can anyone help? Thanks in advance
Hi Paul
To start of with, we had to manually clear out all the system tables to
properly disable the server as a distributor (The SEM froze) ( We also tried
using sp's which also became non-responsive). At the moment, enabling and
disabling the server as a distributor can be done without any problems
through SEM. We use the SEM to publish the database(s), and the process hangs
on the third step which is adding the articles. It simply gets stuck on
number 1 of X articles, but at least the GUI remains responsive. We have left
the process to run for significant periods without any change - it remains
stuck on article one. Again, we only experience this on databases that were
previously replicated, previously unreplicated databases can be published and
remove without any problems, so I am of the opinion that the problem is not
so much the SQL installation as a problem with the databases.
Thanks
Emile
"Paul Ibison" wrote:

> Emile,
> are you talking about sp_addarticle - manually or
> clicking the articles checkbox in the add publication
> wizard? Or is it when you run the snapshot agent, or the
> distribution/merge agent?
> What procedure gets called in a loop?
> How did you previously remove replication - if you want
> to remove almost all traces, try sp_removedbreplication.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Emile
can you try sp_removedbreplication on the database in
question. Then use sp_dboption to disable and reenable
publishing and then see if the issue still remains.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)