Mehram
asked on
CREATE SERIAL # IN SQL RESULT WITHOUT USING INTO CLAUSE
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.Ex penseDate, 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.CARGO DETAIL A ON (A.AGENT=C.AGENT)
JOIN DLIServer.DLI_DB.DBO.CARGO DETAILINVO ICE 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
SELECT Sno='',
A.FILE_JOB,
A.AGENT,
B.INVOICENO,
B.ExpenseDate,
DAYSPASS=DATEDIFF(DAY,B.Ex
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.CARGO
JOIN DLIServer.DLI_DB.DBO.CARGO
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
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.Ex penseDate, 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.CARGO DETAIL A ON (A.AGENT=C.AGENT)
JOIN DLIServer.DLI_DB.DBO.CARGO DETAILINVO ICE 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
SELECT ROW_NUMBER() SNO,
A.FILE_JOB,
A.AGENT,
B.INVOICENO,
B.ExpenseDate,
DAYSPASS=DATEDIFF(DAY,B.Ex
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.CARGO
JOIN DLIServer.DLI_DB.DBO.CARGO
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
ASKER
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'.
.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'.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
How Could I check compatibility mode
Try this
SELECT row_number()OVER (ORDER BY Col1) as SrNo, Col1,col2,... FROM Table1
SELECT row_number()OVER (ORDER BY Col1) as SrNo, Col1,col2,... FROM Table1
Mehram Please close the quetion. If u have got the answer.
How to dynamically number rows in a SELECT Transact-SQL statement
http://support.microsoft.com/kb/186133