Get the maximum number of row_number inside the cross

Question

I am having trouble calculating the maximum row_number in my sql case.

I will explain this directly with the SQL Fiddle as I think it will be faster to understand: SQL Fiddle

• Columns 'OrderNumber', 'HourMinute' and 'Code' are intended to represent my table and therefore should not be relevant for coding purposes.
• The DateOnly column contains dates
• The "Phone" column contains the phones of my clients.
• The "Purchases" column shows the number of customer purchases over the past 12 months. Please note that this value is provided for each date, so the 12-month time period refers to the date we are evaluating.

Finally, the column I'm trying to create is “PREVIOUS PURCHASES”, which count the number of times that the figure shown in the “Purchases” column has appeared in the previous 12 months (for each phone).

Using the SQL Fiddle as an example, you can see what I have achieved so far. The "PREVIOUSPURCHASES" column produces what I want, however it also produces lower values ​​(for example, only the maximum is the one I need).

For example, you can see that lines 4 and 5 are duplicated, one with “PREVIOUS PURCHASES” 1, and the other with 2. I do not want to have the 4th line in this case.

I have at least replaced row_number with something like max (row_number), but I could not process it (already looked at similar entries in stackoverflow ...).

This should be implemented in SQL Server 2012.

Thanks in advance.

Answer 1

I'm not sure which set of results you want to see, but is something wrong with what came back with this?

SELECT c.OrderNumber, c.DateOnly, c.HourMinute, c.Code, c.Phone, c.Purchases, MAX(o.PreviousPurchases) FROM cte c CROSS APPLY ( SELECT t2.DateOnly, t2.Phone,t2.ordernumber, t2.Purchases, ROW_NUMBER() OVER(PARTITION BY c.DateOnly ORDER BY t2.DateOnly) AS PreviousPurchases FROM CurrentCustomers_v2 t2 WHERE c.Phone = t2.Phone AND t2.purchases<=c.purchases AND DATEDIFF(DAY, t2.DateOnly, c.DateOnly) BETWEEN 0 AND 365 ) o WHERE c.OrderNumber = o.OrderNumber GROUP BY c.OrderNumber, c.DateOnly, c.HourMinute, c.Code, c.Phone, c.Purchases ORDER BY c.DateOnly