Solved

CREATE SERIAL # IN SQL RESULT WITHOUT USING INTO CLAUSE

Posted on 2007-04-11
8
193 Views
Last Modified: 2010-03-19
I WANT TO CREATE SERIAL # LIKE 1,2,3 AGAINST OUT COME OF THIS STATEMENT WITHOUT USING INTO CLAUSE, IS THERE ANY WAY? PLEASE HELP

SELECT                           Sno='',
            A.FILE_JOB,
            A.AGENT,
            B.INVOICENO,
            B.ExpenseDate,
            DAYSPASS=DATEDIFF(DAY,B.ExpenseDate,GETDATE())+1,
            DEBIT=SUM(B.DEBIT),
            C.BALANCE,
            RUNNINGBALANCE=CASE WHEN SUM(B.DEBIT) <= C.BALANCE THEN B.DEBIT ELSE 0 END
FROM
(select a.agent, Balance=sum(debit-credit) from cargodetail a join cargodetailinvoice b on a.file_job=b.file_job
where a.agent='Ghorayeb-2007' and a.doc_Arvd_on >='01/01/2007' and a.doc_arvd_on <='4/30/2007'
group by a.agent
having sum(debit-credit) <> 0)C
JOIN DLIServer.DLI_DB.DBO.CARGODETAIL A ON (A.AGENT=C.AGENT)
JOIN DLIServer.DLI_DB.DBO.CARGODETAILINVOICE B ON (A.FILE_JOB=B.FILE_JOB)
where a.agent='Ghorayeb-2007' and a.doc_Arvd_on >='01/01/2007' and a.doc_arvd_on <='4/30/2007'
GROUP BY A.FILE_JOB, A.AGENT, B.INVOICENO, B.ExpenseDate, B.DEBIT, C.BALANCE
HAVING SUM(B.DEBIT) > 0
ORDER BY DAYSPASS DESC

0
Comment
Question by:Mehram
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 18

Expert Comment

by:Sham Haque
ID: 18888411
i think this is what you're after:

How to dynamically number rows in a SELECT Transact-SQL statement
http://support.microsoft.com/kb/186133
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18888435
in sql 2005, use the ROW_NUMBER() function:


SELECT                           ROW_NUMBER() SNO,
            A.FILE_JOB,
            A.AGENT,
            B.INVOICENO,
            B.ExpenseDate,
            DAYSPASS=DATEDIFF(DAY,B.ExpenseDate,GETDATE())+1,
            DEBIT=SUM(B.DEBIT),
            C.BALANCE,
            RUNNINGBALANCE=CASE WHEN SUM(B.DEBIT) <= C.BALANCE THEN B.DEBIT ELSE 0 END
FROM
(select a.agent, Balance=sum(debit-credit) from cargodetail a join cargodetailinvoice b on a.file_job=b.file_job
where a.agent='Ghorayeb-2007' and a.doc_Arvd_on >='01/01/2007' and a.doc_arvd_on <='4/30/2007'
group by a.agent
having sum(debit-credit) <> 0)C
JOIN DLIServer.DLI_DB.DBO.CARGODETAIL A ON (A.AGENT=C.AGENT)
JOIN DLIServer.DLI_DB.DBO.CARGODETAILINVOICE B ON (A.FILE_JOB=B.FILE_JOB)
where a.agent='Ghorayeb-2007' and a.doc_Arvd_on >='01/01/2007' and a.doc_arvd_on <='4/30/2007'
GROUP BY A.FILE_JOB, A.AGENT, B.INVOICENO, B.ExpenseDate, B.DEBIT, C.BALANCE
HAVING SUM(B.DEBIT) > 0
ORDER BY DAYSPASS DESC
0
 

Author Comment

by:Mehram
ID: 18888470
On using ROW_NUMBER() it is showing following error:

.Net SqlClient Data Provider: Msg 195, Level 15, State 10, Line 3
'ROW_NUMBER' is not a recognized function name.
.Net SqlClient Data Provider: Msg 170, Level 15, State 1, Line 16
Line 16: Incorrect syntax near 'C'.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 18888992
are you using sql server 2005 ?
is the database you are connected to in compatibility mode 90 (and not in 80 or less)?
0
 

Author Comment

by:Mehram
ID: 18930532
How Could I check compatibility mode
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18930549
0
 
LVL 2

Expert Comment

by:svalekar
ID: 18945044
Try this

SELECT row_number()OVER (ORDER BY Col1) as SrNo, Col1,col2,... FROM Table1
0
 
LVL 2

Expert Comment

by:svalekar
ID: 18989004
Mehram Please close the quetion. If u have got the answer.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question