MS SQL Server :: Numbering Rows With A Twist

Numbering Rows In A Result Set

Nov 29, 2001

work_id Work_item_code

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

3 Sup

14 100001

5 100002

16 100003

8 IWSM

9 ES3000

10 AppDev

11 100004

I want to have a counter on this select like

work_id Work_item_code counter

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

3 Sup 1

14 100001 2

5 100002 3

16 100003 4

8 IWSM 5

9 ES3000 6

10 AppDev 7

11 100004 8

I can use rownum in Oracle, what do I do to get this in SQL server ?

Thanks Sonali

View 2 Replies View Related

Numbering Rows In A Resultset

Nov 19, 2001

Hello all,

I'm trying to number rows in a resultset by a grouping.

e.g. if my data looks like this:

personname persondata
----------- -----------------
person1 data
person1 more data
person1 other data
person2 stuff
person2 more stuff
person2 even more stuff
person2 lots of stuff
person2 last bit of stuff
person3 info
person3 more info

I want it to return this, with x as a sub-numbered value (like an identity for each grouping):

x personname persondata
-- ----------- -----------------
1 person1 data
2 person1 more data
3 person1 other data
1 person2 stuff
2 person2 more stuff
3 person2 even more stuff
4 person2 lots of stuff
5 person2 last bit of stuff
1 person3 info
2 person3 more info

Any ideas?

Thanks,

Dan

View 2 Replies View Related

Auto Increment --Numbering Rows

Sep 1, 2005

How can I issue a SQL statement to assign incrementing numbers starting from a particular row. For e.g.,

col1 col2
----- -----
aaa 123
... 345
xxx 001
yyy 002
zzz 003

I would like to have numbering in col2 to start where col1=XXX to ZZZ.

Thank you

View 1 Replies View Related

One-to-many With A Twist

Jul 23, 2005

Hi,What is the best way to model this: Assume I have two objects: Agencyand Publisher, and both have a 1-to-n relationship to Employee. This isa true 1-to-n relationship, as each Employee can only work for oneAgency or one Publisher. Let's assume further that I cannot introduce asupertype (e.g. Employer) which holds the 1-to-n relationship.My preferrd solution is to have a foreign key in Emplyee that caneither link to a primary key of Agency or Publisher (all my primarykeys are 64-bit IDs that are unqiue across the database). However, nowI won't be able to map a bi-directional association, without indicatingin Employee whether this is an "Agency" or "Publisher" relationship(ala <ANY>).My other option is to use two tables, AgencyEmployee andPublisherEmployee, which can then be linked as traditional 1-to-nbidirectional associations.What do you guys consider best practice in this situation?Cheers,Jen

View 19 Replies View Related

Mirroring With A Twist

Jul 18, 2007

I'm really new to SQL and looking for advice.

I'm going through the basics I don't mind doing my own research but I have an issue that needs attention and is beyond my current level.

I'm looking for a way to have any data entered into one database automatically copied to an archive database running on another machine. Items deleted in database one would be retained in database two. Database one would be clean and efficient and only reflect currently necessary information. Database two would grow indefinitely as an archive of what had/does exist in database one.

I'm sure this is quite mundane but I'm not sure what key words I'm looking for. Is there a title for this process?

Is this possible from within the SQL management studio or should I start those videos under the developer section?

I'm in the tall grass. Any pointers appreciated.

Both servers are Server2003 with SQL Server 2005 Standard Edition /Sp2

/drew

View 4 Replies View Related

Last Day Of Previous Month...with A Twist

May 10, 2007

Hi,I have a requirement to design a query that identifies items soldbetween two dates. There is a 'SoldDate' datetime field used toregister what date the item was sold.The query needs to identify all sales between the last day of theprevious month and going back one year.What I would like to do is to design a query / stored procedure thatwill dynamically create the criteria to allow the client to simply runthe query or stored proc.I know how to establish the last day of the previous month part, I'mjust not sure of how best to design the remainder of the query.Thank in advance

View 9 Replies View Related

Searching And Displating Records, With A Twist

Nov 28, 2003

ive got a tough one for everyone this time!

i am trying to get a page up that enables me to enter a number into a box on a form, which then on clicking of a search button, queries the database, and returns 2 other fields that relate to the number found in the database, in an ASP datagrid.

the easiest way to do this would be to use OLEDB or ODBC, but the host i am using doesnt support either of them, and so i am trying to use their code, something using OBJDB, but to no avail. frankly, their code is rubbish, and im not sure how to go about doing the above.

i would be very grateful if someone could help me out with this problem,

thanks,

craig

View 1 Replies View Related

Sequence Number Generating With A Twist

Apr 9, 2008

:eek:

I'm having a brain not functioning day - well who am I kidding - more like a year :rolleyes:

I need some help with some sequence numbering and cannot even get my head around the logic I want to use, let alone the actual code.

I have a dataset with 3 fields:

Area
ID
RefNo

This table contains a list of employee ID's by Area and each employee has a RefNo (counter) in each area.

The data comes from 2 different sources and is combined in this table. Some employees had no RefNo already assigned to them so I have entered their RefNo as 10000 in order to ensure they are sorted at the bottom of the list.

The ID's that have RefNo's have to keep the one they have. Therefore, I need to create RefNo's for the ones that currently have RefNo 10000.

These numbers I create have to follow on from the highest RefNo for the Area.

For example:

Area ID RefNo
A Z 1
A Y 2
A X 3
A W 10000
A V 10000
B N 1
B O 10000
B P 10000

So, for Area A, ID's W and V would have to be assigned RefNo 4 and 5, and for Area B, ID's O and P would have to be assigned RefNo 2 and 3.

Hope this makes sense to all.

BTW, am using SQL 2000 at the moment.

Thanks in advance for any help!

View 11 Replies View Related

SQL 2012 :: Calculating Difference Between Two Times With A Twist (between 9am And 5pm)

Mar 25, 2014

I have Two Time fields in a table. Time(0). An "opening time" and a "closing time". They can hold any legit time.

I want to calculate in a SELECT Statement how many minutes within this range are within 9am to 5pm (which I'll convert to hours).

For example, here's an easy example:

OPEN: 9:00:00
CLOSE: 17:00:00
8 Hours/480 minutes

I could get this easy enough with a DATEDIFF function.

But what about:

OPEN: 08:00:00
CLOSE: 18:00:00

10 Hours total but only 8 of those 10 are within 9am-5pm.

Or what about:

OPEN: 10:00:00
CLOSE: 20:00:00

10 Hours total but only 7 are within 9am-5pm range.

I can calculate the total hours/minutes between the two times but not within that special range.

View 4 Replies View Related

Importing Excel Data Into SQL Server 2005 With A Twist!

Jun 11, 2007

Hi,
I would like to import an Excel spreadsheet into SQL Server 2005. I can do this quite easily using the Import/Export wizard, and have each row in the spreadsheet transfer to a new row in the database table.
However, I want to import the first few columns of the spreadsheet row into one table (called Products), but put the remaining columns into a related, three-column table, called Product_Details. In the Product_Details table, one column would hold the spreadsheet column value, the other column would be a FK integer value linked to the PK in the Products table, and the third column the primary key as normal.
So, somehow, I would need to get hold of the primary key value when the first spreadsheet columns are inserted into the Products table and then insert the remaining columns into the Product_Details table with two values per row - one value being the spreadsheet cell value, the second being the primary key of the new product in the Products table.
TIA,
Graham.

View 1 Replies View Related

Row Numbering

May 30, 2008

Hello,

I am having trouble getting the results I am looking for from this query:

select ROW_NUMBER() OVER(PARTITION BY TransTime, DriverNo ORDER BY TransTime) as CustCount
, ROW_NUMBER() OVER(PARTITION BY Left(ToName,2), TransTime, DriverNo ORDER BY TransTime) As DelCount
, DriverNo
, Convert(VarChar(8), TransTime, 108) As TransTime
, Left(ToName,2) As AirPortCode
from transactions

The results I am trying to get would be like this:

CustCount DelCount DriverNo TransTime AirPortCode
-------------------- -------------------- -------- --------- -----------
1 1 042000 04:00:00 NW
1 2 042000 04:00:00 NW
1 1 042000 06:51:00 AA
1 1 042000 08:45:00 NW
1 1 042000 08:52:00 NW
1 1 042000 10:30:00 NW
1 1 042000 10:45:00 NW
2 1 042000 10:45:00 AA
1 1 042000 11:30:00 NW
1 2 042000 11:30:00 NW

But the closest I have gotten is:

CustCount DelCount DriverNo TransTime AirPortCode
-------------------- -------------------- -------- --------- -----------
1 1 042000 04:00:00 NW
2 2 042000 04:00:00 NW
1 1 042000 06:51:00 AA
1 1 042000 08:45:00 NW
1 1 042000 08:52:00 NW
1 1 042000 10:30:00 NW
1 1 042000 10:45:00 NW
2 1 042000 10:45:00 AA
1 1 042000 11:30:00 NW
2 2 042000 11:30:00 NW

The CustCount field should read 1 if there is only one unique AirPortCode during the TransTime with the specific DriverNo, but the query is counting both rows during that transtime. Any ideas?

Thanks.

View 9 Replies View Related

Numbering

Oct 5, 2005

Hi guys,

I have a table with 3 col like below

col1 col2 col3
LastName FirstName Address City
Nilsen Fred Kirkegt 56 Stavanger
Rasmussen Nina Storgt 67

none of them are unique, some are null and there are about 800,000 rows

I want to insert a col before 1 or after 3 and have numbering from 1 to the last row. How can I do that?

Thank you
Mina

View 5 Replies View Related

Numbering In SQL

May 19, 2006

I have table with 10-20 rows with field P6 which is empty. I want toupdate numbers to P6 starting 1 and increasing by 1. I suppose it isdone by triggers but I don't know how to do that. Help :-)

View 7 Replies View Related

Row Numbering...

Sep 20, 2007

Good Day everyone...

I created a simple report for getting the top selling items of a store. I am getting the report well but my team mate requested to include a number at the first column that will hold the ranking of the item. Is there a function for including a rowrank or someway that i can add a column to my query that will provide the rowrank. Please show me how... c",)

thanks in advance guys...

View 4 Replies View Related

Please Help..Query With Numbering

Mar 31, 2006

Is It possible that, if there are 5 rows selected out, each row will automatically have the number from 1 to 5?

for example, after select query, results:
---------------------------------------
1Tom100
2May93
3Apple87
4Johnny72
5Noel67

View 2 Replies View Related

Help Needed On Numbering

Sep 19, 2007

Hi All,

I've upsized my database from access to SQL server, now im very new to SQL etc and i've been reding up on sqential numbering etc and my mind is buzzing with ideas. This is my problem.

I have a table and a colum called PIR ID this colum needs to start the numbering at 372 and increment by 1 each time. Now initially this was o.k by using identify. However it's a problem when 2 users open the form in access they are given the same number.

Now my though on this was to initially have a seperate table called new PIRID and hold the next bumber in there, so that a new number is taken from the list and entered into the record on save ( does that make sense) now the code i've got up to so far is that i've created the table and i get stuck from there.

Can anyone help me with the code of format of code i'm meant to be using, i'm under the understanding that to perform this funtion i may have to use a trigger of some sort.

Thanks In advance
Carol

View 1 Replies View Related

Auto Numbering

Jul 23, 2005

Hello,For analyses and reporting I would like to add a 'ranking' to atable/view.Example:Using the 'order by desc' clause in query I get a list of Customersordered by Turnover (descending). I would like to add that rankingnumbers (same as recordnumbers) in the query. I would like to have thefollowing result:Cust_nr Cust_Name Turnover_2004 Ranking002234 Bayer 139.000 1003456 Rentokill 123.456 2001231 Air France 105.000 3etc.When the 'ranking' is part of the query/table I can use this ranking inan other query.Important: This questions is not about making an (empty) table structurefor filling in by an application and generating a new unique number eachtime a record is added.I hope you can help me.Thanks,Hans*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 2 Replies View Related

Numbering A Query

Jul 20, 2005

Is there a quick way to number a query?ie I want to number the records returned by a recordset consecutively.This seems like it should be simple but I haven't figured out how to do ityet.Any help is appreciated!TIACarter

View 2 Replies View Related

Auto Numbering

Feb 23, 2008

I have created an ID column for auto numbering, but in the properties, they didn't have the option of setting it to auto numbering, and how am i suppose to set or code it to autonumbering.

Please and thankyou

View 3 Replies View Related

Numbering In Group

Jan 1, 2007

I want to add numbering in group in the reports.(number each row in the detail)

when I worked with access it was possible.

can I do it in the report services?

thanks!

View 1 Replies View Related

Consecutive Numbering WITHOUT Identity

Nov 23, 2006

Hi,I'm
trying to insert records from one table into another table. The
destination table has a ROWID field which cannot be an identity key,
but needs to 'act like' an identity key and have its value populated
with (Max(ROWID) + 1) for each row added to the table.To my
thinking, simply using (Max(ROWID) + 1) in my SELECT statement will not
work as it will only be evaluated once so if I am adding 1000 records
and Max(ROWID) is 1234, all 1000 entries will end up having a ROWID of
1235.Is there a way to accomplish this?Thanks

View 2 Replies View Related

SQL Unique ID - Auto Numbering

May 31, 2008

I know its a simple question. But couldnt find a solution of my own.
My SQL 2005 table has 2 fields, namely - id (primary key), comments(varchar).
I want to automatically increment the value of 'id', starting from 1000.
Is there any DataType which i can specify at the time of table designing, so that the value of the id
field will be automatically incremented, upon inserting each new record.
Im using SQL 2005 Express Edition. Can anyone tell, how to do that ?
Thanks

View 8 Replies View Related

Numbering Issues For Projects

Jun 5, 2008

I'm creating an application to track project issues. I would like to have the issue number start over at 1 for each project. I would like to have the issue numbers assigned to each issue in sequential order for each project. Below is a brief example of what I need it to do. I don't know where to even start to try to figure this out. Thanks
ProjectNumber, IssueNumber
TEST0001, 1TEST0001, 2TEST0002, 1TEST0003, 1TEST0003, 2TEST0001, 3TEST0004, 1TEST0003, 3TEST0002, 2TEST0005, 1

View 18 Replies View Related

Table - 'Auto-numbering'

Sep 10, 2004

Hi guys,

Is it possible to make a table with 'Auto-numbering'? I mean the same function as in MS Access.

Thanks in advance.

View 2 Replies View Related

Insertingupdate Line Numbering

Oct 11, 2004

I have a table which contains order information, which I would like to have line number associated with them

what SQL statement do I use in order to add the line numbering for each line, and have it dependent on reseting on the sales order number?

View 4 Replies View Related

Problems With Week Numbering

Jan 7, 2005

Hi!

I'm trying to get a MSSQL view to return some data marked with a week number based on a date (which is present on every row in the source table). Since I reside in Denmark, I would like the numbering to follow the rule '1. week of a year is the first week with a least 4 days in it'. My instance of MSSQL however is convinced that the rule '1. week starts 1. January'.

Are there any ways to configure my way out of this problem??

Examples:
This year 1. and 2. January is returned as week=1, 3. January is returned as week 2.
I would like 1. and 2. to be week=53 and 3. to be week=1.

Thanx in advance! :)
Ult

View 7 Replies View Related

Sequential Numbering Problem

Jul 27, 2006

I've created a sproc that will provide a recordset for an Access report via a pass-thru query. The report is a production schedule.
Some of the runs on the schedule have a note associated with them. I need
to be able to number these notes, so that they can be displayed in the report
bibliography style. When I populate the data in the sproc, if the "notes"
field in my table contains any data I display a 1, else it's a 0
Like:

SELECT NOTE_FLAG = CASE WHEN ISNULL(SCHED_NOTE,'')<>'',1,0
FROM MASTER_SCHEDULE

The problem is, I really need to display sequential numbers instead of 1's

If I create a base set with this:

CREATE TABLE #TMPRST (
RECID INTEGER,
PRODUCT VARCHAR(10),
QTY FLOAT,
NOTE_FLAG INTEGER)

INSERT INTO #TMPRST
SELECT 1, 'ABC123', 4, 0
UNION ALL
SELECT 4, 'DEF123', 5, 1
UNION ALL
SELECT 5, 'ABC456', 12, 0
UNION ALL
SELECT 13, 'PQR789', 10, 1

How do I go back and convert the note_flag column to read
... 0
... 1
... 0
... 2

View 14 Replies View Related

Identity Column Numbering

Mar 6, 2006

We're performing an insert and the column that has identity is numbering the records without a problem, but it's assigning duplicate ID's on the records. Does Autoincrement or Autonumbering need to be turned on somewhere?

spencer

View 9 Replies View Related

Add Leading Zero For Legal Numbering

Jan 31, 2008

Good afternoon,

I hope the title is self explaining. Here is an example of what i would like to do:

Before...
1
1.1
1.1.1
1.2.1
1.3.1
1.10.1

After...
01
01.01
01.01.01
01.02.01
01.03.01
01.10.01

Thanks,
Phil.

View 6 Replies View Related

Page Numbering Per Group

Nov 8, 2007

I don't think this is possible, but I promised my users I would bring this question to the forum. The users want to have the page numbering change per each group on the report. The report is large, and there are up to 5 pages in each group. I have a large List Control surrounding the many tables on my report that controls the over all report grouping. Everything looks great and the users are pleased.

But when I try to add the Global!PageNumber and Global!TotalPages to the bottom of the List Control I get the error that the Globals properties can only be used in the Page Header and Page Footer section.

Is there any way around this? I could not think of a way around it, but I am not an expert in Reporting Services by any means.

Thank you,

T.J.

View 4 Replies View Related

Sequential Numbering In Queries

Feb 7, 2008

Hi,

I've a column say page#. This has by default 4 pages which I'm numbering as 1,2,3,4. I'll be adding pages to this table and the number needs to start from 5 (since 4 pages are already present)

Page# is not an identity column. I want to know if theres a function which can do the trick.

Thanks for your help!

Subha

View 4 Replies View Related

Page Numbering Per Grouping

Mar 28, 2008

HI

Has anyone figured out how to reset the page numbers after a grouping and have it display in the format ��Page X of Y��? I can get it to reset the page number for each group and display ��Page X�� but it��s the ��of Y�� part that is a problem.

Thanks,

View 6 Replies View Related