Solved

CREATE SERIAL # IN SQL RESULT WITHOUT USING INTO CLAUSE

Posted on 2007-04-11
8
194 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 143

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 143

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 143

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need help with T-SQL on SQL Server 2014 9 37
SSRS Deployment problem 5 66
SQL create line numbers for data sampling 11 28
SQL Group By Question 4 20
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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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

829 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