LASt 6 records of the output of the stored procedure
The following is the stored procedure and "I want to fetch the LATEST SIX INVOICES FOR EACH CUSTOMER"
THERE COULD BE MORE INVOICES FOR EACH CUSTOMER BUT I HAVE TO FETCH ONLY WHICH ARE LATEST 6 INVOICES.
ALTER PROCEDURE [dbo].[SCA_M_CUSTSOINV_REFRESH]
@COMP_CD NVARCHAR(20)='',
@USER_CD NVARCHAR(20)='',
@USER_TYPE NVARCHAR(1)=''
AS
SET NOCOUNT ON
DECLARE @SLSHIST_DATE NVARCHAR(10)
SELECT
@SLSHIST_DATE = CONVERT(NVARCHAR(10), DATEADD(MONTH,-SLSHIST_MTH,dbo.[GetCountryDate]()),120)
FROM SET_MASTER
WITH SUBQUERY AS
(SELECT
ROW_NUMBER() OVER (PARTITION BY A.CUST_CD ORDER BY C.INV_KEY DESC) "ROW_ID",
A.CUST_CD,C.SO_KEY "TXN_KEY",
C.INV_NO, C.INV开发者_运维百科_KEY, C.INV_DT, C.INV_STATUS, C.NET_TTL_TAX AS INV_AMT
FROM
(SELECT DIST_CD, SLSMAN_CD, CUST_CD FROM T_CA_SLSMANCUST
WHERE DIST_CD = @COMP_CD AND SLSMAN_CD = @USER_CD) A
INNER JOIN TXN_INVOICE C ON C.CUST_CD=A.CUST_CD
AND C.INV_DT >= @SLSHIST_DATE
)
SELECT
CUST_CD, TXN_KEY, INV_NO, INV_KEY, INV_DT, INV_STATUS, INV_AMT,
CASE ROW_ID WHEN 1 THEN 'Y' ELSE 'N' END "LAST_INV"
FROM SUBQUERY
ORDER BY CUST_CD,INV_KEY
You are getting ROW_ID
by ROW_NUMBER() OVER (PARTITION BY A.CUST_CD ORDER BY C.INV_KEY DESC)
and the last invoice is the one with ROW_ID = 1 so don't you just need to add WHERE ROW_ID <= 6
as below?
SELECT
CUST_CD, TXN_KEY, INV_NO, INV_KEY, INV_DT, INV_STATUS, INV_AMT,
CASE ROW_ID WHEN 1 THEN 'Y' ELSE 'N' END "LAST_INV"
FROM SUBQUERY
WHERE ROW_ID <= 6
ORDER BY CUST_CD,INV_KEY
SELECT TOP 6 * FROM invoices ORDER BY date DESC
精彩评论