You need to order your query, but of course the problem becomes what to order it by!
You can use
CASE
in an
ORDER BY
clause to achieve this e.g.
ORDER BY CASE WHEN INVOICENO <> 0 THEN INVOICENO ELSE ASSESSABLE_VALUE END
I chose this specific things because I observed that
INVOICENO
on my test data was 0 for the Grand Total row - you might need to fiddle with it a bit.
Note that because of this re-ordering the values for
SRNO
start at 2, go through to the number of records and then show 1. The easiest way to get this showing 1 through number of rows again is to use
select ROW_NUMBER() OVER (ORDER BY src.INVOICENO ) - 1 SRNO,
. If you do this then the Grand Total with have
SRNO = 0
[EDIT after OP comments]
I've used a cut-down version of your query to demonstrate a couple of ways of getting the row number to reflect the new order, and I've stripped out the where clauses purely for this demo
WITH CTE AS
(
SELECT ROW_NUMBER() OVER (ORDER BY INVOICENO ) SRNO, ISNULL(INVOICENO,'') INVOICENO,
ASSESSABLE_Value,TOTALAMOUNT
from
(
SELECT
ExDcNo AS INVOICENO,SUM(Amount) AS ASSESSABLE_Value,SUM(TotalAmt) AS TOTALAMOUNT
from SalesMaster
GROUP BY ExDcNo with rollup
) SRC
),
CTE2 AS
(
SELECT MAX(ExDcNo) + 1 ino FROM SalesMaster
)
select CASE WHEN CTE.SRNO = 1 THEN CTE2.ino ELSE CTE.SRNO - 1 END AS SRNO,
INVOICENO, ASSESSABLE_Value, TOTALAMOUNT
FROM CTE, CTE2
ORDER BY 1
DECLARE @GT_INVOICE int = (SELECT MAX(ExDcNo) FROM SalesMaster) + 1
;WITH CTE AS
(
SELECT ROW_NUMBER() OVER (ORDER BY INVOICENO ) SRNO, ISNULL(INVOICENO,'') INVOICENO,
ASSESSABLE_Value,TOTALAMOUNT
from
(
SELECT
ExDcNo AS INVOICENO,SUM(Amount) AS ASSESSABLE_Value,SUM(TotalAmt) AS TOTALAMOUNT
from SalesMaster
GROUP BY ExDcNo with rollup
) SRC
)
select CASE WHEN CTE.SRNO = 1 THEN @GT_INVOICE ELSE SRNO - 1 END AS SRNO,
INVOICENO, ASSESSABLE_Value, TOTALAMOUNT
FROM CTE
ORDER BY 1