serial no

Question

serial no

znkin

Old Hand

Points: 382
More actions
March 20, 2009 at 11:21 am

#93296

How do I generate a serial no. within a select query
My query is
Select CustomerId,CustomerName from CustMaster
and I want the output as
1 23232 John
2 32323 Amit
3 82321 KY
Also another question is
I have a variable as type datetime like
declare @PFDate datetime
set @PFDate = '2009-03-19 00:00:00.000'
and I need to add time to this datetime variable. Like if I would add
15 hours 30 min then the variable should become '2009-03-19 15:30:00.000'
Thanks

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

For the first question:

What is the specification for the Serial No? Any incremental ID? Should it contain any information? Should it be a NVE or a Code39?

For the second question:

declare @PFDate datetime

set @PFDate = '2009-03-19 00:00:00.000'

select @PFDate, @PFDate + CONVERT(DATETIME, '15:30')

Greets

Flo

Lowell SSC Guru Points: 323518 More actions · Answer 2

My questions are the same:

what is a serial number to you? is it a random number? can it be '0000' plust the customer number?(0001,0002, etc). Why did your example have a wide range of values?

is the number unique? does it need to follow a business pattern(ie SN0000YYYY0001 so it makes "at-a-glance' sense to some people?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 3

SELECT ROW_NUMBER() OVER (ORDER BY CustomerID) AS SerialNo,

CustomerId,

CustomerName

FROM dbo.CustMaster

But, I agree with the others... what are you going to use it for? The answer to that question could make a huge difference.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

znkin Old Hand Points: 382 More actions · Answer 4

Thanks for the replies. I am looking for a simple serial no. count starting with 1,2,3......

I just have to display the customerid and customername along with a serial no. starting from 1 , so I was looking to generate this serial no. in the sql query only.

sample output if the query returned 10 records

SR No. CustomerId Customername

1 4444444 ABC

2 3332323 XYX

3 3232323 YTR

'

10 4343434 YTTT

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 5

Then the query I included with my post should do it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

znkin Old Hand Points: 382 More actions · Answer 6

I tried your example with sql 2005 but it gives me this error

Msg 195, Level 15, State 10, Line 1

'ROWNUMBER' is not a recognized built-in function name.

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 7

Florian Reischl

SSC-Dedicated

Points: 37301

March 21, 2009 at 2:32 am

#963934

Hi

Which version of SQL Server are you using?

Greets

Flo

znkin Old Hand Points: 382 More actions · Answer 8

I am using SQL server 2005 and when I try this code for generating a running sequence no. to start from 1 it gives me the error

SELECT ROWNUMBER() OVER (ORDER BY CustomerID) AS SerialNo,

CustomerId,CustomerName FROM dbo.CustMaster

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 9

SELECT ROW_NUMBER() OVER (ORDER BY CustomerID) AS SerialNo,

CustomerId,CustomerName FROM dbo.CustMaster

Lowell SSC Guru Points: 323518 More actions · Answer 10

that error could occur if your database compatibility level is at 80 instead of 90;

the compatibility level determines which syntax version is applied to statements hitting the database, and rownnumber() is a 90 and above functionality.

change the version and you should be all set.

znkin (3/21/2009)
I tried your example with sql 2005 but it gives me this error
Msg 195, Level 15, State 10, Line 1
'ROWNUMBER' is not a recognized built-in function name.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

LutzM SSC Guru Points: 107049 More actions · Answer 11

I don't think it's the compatibility level...

Looks more like a typo: ROWNUMBER vs. ROW_NUMBER()

Underscores sometimes matter 😛

The RowNumber function is for Reporting Services.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 12

Dangit... sorry folks... I posted in a hurry and left out the underscore in ROW_NUMBER(). Andrew has the correct code and I went back to my previous erroneous post and fixed that, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sanghanichetan40 Newbie Points: 9 More actions · Answer 13

I want devide this table in to groups like in first block display Id(1-5) and second block desplay data (5-10) and onward plz help