Search code examples
sql-serverjoinpartitioningrow-number

Joining temp table whilst using row_number()

I am trying to get the following table in Microsoft SQL Server. Basically what I am trying to do is to get, for each Sale ID, the sale date (date and time) and the next sale date (date & time), and also the amount of the sale for the first sale date. The table below should explain the later.

enter image description here

I have done the following coding but haven't managed to get to the table above:

DECLARE @date1 AS datetime;
DECLARE @date2 AS datetime;

SET @date1 = DATEADD(HOUR, -72, GETDATE());
SET @date2 = DATEADD(HOUR, -1, GETDATE());

SELECT SaleID AS SaleID,
      ,[Sale Date & Time] AS SalesDate
      ,RN
INTO #sales
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY SaleID ORDER BY [Sale Date & Time] ASC) AS RN,
             SaleID,
             SalesDate
      FROM Sales
      WHERE [Sale Date & Time] BETWEEN @date1 AND @date2) T;

Data from the first part of the query:

enter image description here

Solution

  • I managed to do the above part just in case someone will find it useful in the future.

    below coding was used

    with cte as

( 

select rownum = ROW_NUMBER() over (partition by SalesID order by [Sales Date & Time] asc)
,SalesID,[Sale Date & Time],[Amount]
from Sales
where [Sales Date & Time] between GETDATE()-7 and GETDATE()
)

select 

    a.SalesID
    ,a.[Sale Date & Time] 
    ,b.Sale Date & Time] 
    ,a.[Amount]

from cte a

inner join cte b on b.rownum = a.rownum+1 and a.SalesID=b.SalesID