add rowcount in s.p with union

Question

USE exemple
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER  PROCEDURE [dbo].[SP_sum_inventory_byproduct_by_date_range] 
(
 @salesdate  datetime ,
 @upsalesdate1  datetime
 )
AS
BEGIN
SET NOCOUNT ON;

SELECT T1.ItemCode, SUM(T1.Quantity)
FROM
(
 SELECT T1.ItemCode, SUM(T1.Quantity)  
 FROM iiii T0  INNER JOIN ffff T1 ON T0.DocEntry = T1.DocEntry 
               INNER JOIN oooo t2 on t0.cardcode=t2.cardcode
 WHERE T0.DocDate >= @salesdate  and  T0.DocDate <= @upsalesdate 
 GROUP BY T1.ItemCode, T1.Dscription
 UNION
 SELECT T1.ItemCode , T1.Dscription , -SUM(T1.Quantity)
 FROM ORIN T0  INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry 
               INNER JOIN ococ t2 on t0.cardcode=t2.cardcode
 WHERE T0.DocDate >= @salesdate and  T0.DocDate <= @upsalesdate 
 GROUP BY T1.ItemCode, T1.Dscription
 ) new
 GROUP BY [T1.ItemCode], [T1.Dscription] 
 ORDER BY itemcode 
END

this query is working in my SQL SERVER 2008 (i deleted some fields and the real tablename). I TRIED TO ADD ROWCOUNT AND I DIDN'T succeed, i get errors . how shoold i do this in the SP?

Answer 1

For more detailed information about using the ROW_NUMBER ranking function, see MSDN

ALTER  PROCEDURE [dbo].[SP_sum_inventory_byproduct_by_date_range] 
(
 @salesdate  datetime ,
 @upsalesdate1  datetime
 )
AS
BEGIN
SET NOCOUNT ON;

SELECT ROW_NUMBER() OVER(ORDER BY new.ItemCode) AS rn, new.ItemCode, SUM(new.Quantity) AS Quantity
FROM
(
 SELECT T1.ItemCode, SUM(T1.Quantity)  
 FROM iiii T0  INNER JOIN ffff T1 ON T0.DocEntry = T1.DocEntry 
               INNER JOIN oooo t2 on t0.cardcode=t2.cardcode
 WHERE T0.DocDate >= @salesdate  and  T0.DocDate <= @upsalesdate 
 GROUP BY T1.ItemCode, T1.Dscription
 UNION
 SELECT T1.ItemCode , T1.Dscription , -SUM(T1.Quantity)
 FROM ORIN T0  INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry 
               INNER JOIN ococ t2 on t0.cardcode=t2.cardcode
 WHERE T0.DocDate >= @salesdate and  T0.DocDate <= @upsalesdate 
 GROUP BY T1.ItemCode, T1.Dscription
 ) new
 GROUP BY [new.ItemCode], [new.Dscription] 
 ORDER BY new.itemcode 
END