How to get the specified number of previous rows from a specified row in SQL Server 2014?

Question

In the next dataset in SQL Server 2014, how would I choose prices> = 2/1/2014 and then 7 rows until 2/1/2014?

Date        Price
2014-01-23  10269.389648
2014-01-24  10034.44043
2014-01-27  9981.349609
2014-01-28  10066.839844
2014-01-29  9962.919922
2014-01-30  10048.69043
2014-01-31  9967.650391
2014-02-03  9741.580078
2014-02-04  9816.969727
2014-02-05  9809.030273
2014-02-06  9940.219727
2014-02-07  10055.339844
2014-02-10  10050.400391

I can easily get the strings> = 2/1 in the WHERE clause, but I'm not sure how to select the 5 previous rows, since the dates are not touching. I cannot use the start date 5 days before 2/1, because I need a certain amount of prices up to 2/1. If I just deduct 7 days before 2/1, I do not get the correct number of prices / rows.

Answer 1

Here you can use ROW_NUMBERto get the previous 7 records to 2014-02-01:

;With Previous As
(
    Select  Date, Price, Row_Number() Over (Order By Date Desc) RN
    From    Table
    Where   Date < '2014-02-01'
),
Cur As
(
    Select  Date, Price
    From    Table
    Where   Date >= '2014-02-01'
)
Select  Date, Price
From    Cur
Union All
Select  Date, Price
From    Previous
Where   RN <= 7

Answer 2

, , @Siyual, . , 7 . TOP(7). , TOP ROW_NUMBER, .

WITH
CTE_Prev
AS
(
    SELECT TOP(7) [Date], Price
    FROM T
    WHERE [Date] < '2014-02-01'
    ORDER BY [Date] DESC
)
,CTE_Curr
AS
(
    SELECT [Date], Price
    FROM T
    WHERE [Date] >= '2014-02-01'
)
SELECT [Date], Price
FROM CTE_Prev

UNION ALL

SELECT [Date], Price
FROM CTE_Curr
;

CREATE NONCLUSTERED INDEX [IX_Date] ON [dbo].[T]
(
    [Date] ASC
)
INCLUDE ([Price])

:

ROW_NUMBER :

, 7 , .

, , . . . , .

700.000 2014-02-01:

CREATE TABLE T
    ([Date] datetime, [Price] numeric)
;
GO

INSERT INTO T ([Date], [Price]) VALUES
('2014-01-23 00:00:00', 10269.389648),
('2014-01-24 00:00:00', 10034.44043),
('2014-01-27 00:00:00', 9981.349609),
('2014-01-28 00:00:00', 10066.839844),
('2014-01-29 00:00:00', 9962.919922),
('2014-01-30 00:00:00', 10048.69043),
('2014-01-31 00:00:00', 9967.650391),
('2014-02-03 00:00:00', 9741.580078),
('2014-02-04 00:00:00', 9816.969727),
('2014-02-05 00:00:00', 9809.030273),
('2014-02-06 00:00:00', 9940.219727),
('2014-02-07 00:00:00', 10055.339844),
('2014-02-10 00:00:00', 10050.400391);
GO

INSERT INTO T ([Date], [Price]) VALUES
('2013-01-23 00:00:00', 10269.389648),
('2013-01-24 00:00:00', 10034.44043),
('2013-01-27 00:00:00', 9981.349609),
('2013-01-28 00:00:00', 10066.839844),
('2013-01-29 00:00:00', 9962.919922),
('2013-01-30 00:00:00', 10048.69043),
('2013-01-31 00:00:00', 9967.650391);
GO 100000

Answer 3

I do not like the union. So I just calculated what min value for>= '2014-02-01'

WITH withID as 
(
  SELECT *, row_number() over (order by [Date]) rn
  FROM Prices
),
myDate as 
(
   SELECT min(rn) minRN
   FROM withID
   WHERE   [Date] >= '2014-02-01'
)
SELECT *
FROM withID, myDate
WHERE rn + 7 >=  myDate.minRN   

EDIT SQL Fiddle Demo

I found another cool way to do the same. Using partition byto separate dates in BEFOREand AFTER. Then bring everything AFTERwith all rn <= 7ofBEFORE

WITH withID as 
(
  SELECT 
      *,
      case 
         when [Date] >= '2014-02-01' THEN 'AFTER'
         else  'BEFORE'
      END as dateRange
      , row_number() over (partition by 
                                  case 
                                     when [Date] >= '2014-02-01' THEN 'AFTER'
                                     else  'BEFORE'
                                  END
                           order by [Date] DESC) rn
  FROM Prices
)
SELECT *
FROM withID
WHERE 
    dateRange = 'AFTER'
OR  rn <= 7

EXIT

|       Date | price | dateRange | rn |
|------------|-------|-----------|----|
| 10/02/2014 | 10050 |     AFTER |  1 |
| 07/02/2014 | 10055 |     AFTER |  2 |
| 06/02/2014 |  9940 |     AFTER |  3 |
| 05/02/2014 |  9809 |     AFTER |  4 |
| 04/02/2014 |  9817 |     AFTER |  5 |
| 03/02/2014 |  9742 |     AFTER |  6 |
| 31/01/2014 |  9968 |    BEFORE |  1 |
| 30/01/2014 | 10049 |    BEFORE |  2 |
| 29/01/2014 |  9963 |    BEFORE |  3 |
| 28/01/2014 | 10067 |    BEFORE |  4 |
| 27/01/2014 |  9981 |    BEFORE |  5 |
| 24/01/2014 | 10034 |    BEFORE |  6 |
| 23/01/2014 | 10269 |    BEFORE |  7 |