CODEWITHSUNDEEP
sql-serversql-server-2008
Mahajan344
Mahajan344

Reputation: 2550

SQL query to get value of next 5th row without using while loop or cursor in SQL Server 2008

I am working on query where I need next nth no of row. My table structure is like 

ID    StockName StockDate   DayOpen DayHigh DayLow  DayClose
--------------------------------------------------------------------
  60    IDBI    2014-01-01  66.50   67.80   66.50   67.60
 197    IDBI    2014-01-02  67.55   69.20   65.25   65.60
 334    IDBI    2014-01-03  65.00   66.40   64.35   66.15
 471    IDBI    2014-01-06  66.15   66.35   65.10   65.55
 608    IDBI    2014-01-07  66.10   66.15   63.85   64.25
 745    IDBI    2014-01-08  64.00   67.10   63.10   66.80
 882    IDBI    2014-01-09  66.60   67.80   64.50   64.75
1019    IDBI    2014-01-10  65.00   65.90   63.75   64.10
1156    IDBI    2014-01-13  63.85   65.00   63.25   64.20
1293    IDBI    2014-01-14  64.00   64.95   63.80   64.05

What I want from output is column name which will give me next 5th row date

E.g. For 1st row new column should return value of next 5th row date ie 2014-01-08 same for 2nd row it should return 2014-01-09 date.

And I can't use datediff with -5 day count as these are working day data excluding weekend days

How do I get this value without using while loop or cursor?

Upvotes: 0

Views: 844

Answers (4)

Solomon Rutzky
Solomon Rutzky

Reputation: 48826

Use a CTE to return the base table plus ROW_NUMBER so that you can LEFT JOIN the CTE to itself on the ROW_NUMBER of the new [Next5thDate] column being 5 rows ahead of current row:

SET NOCOUNT ON;
SET ANSI_NULLS ON;

DECLARE @Data TABLE (
                     ID INT NOT NULL PRIMARY KEY CLUSTERED,
                     StockName VARCHAR(50) NOT NULL,
                     StockDate DATE NOT NULL,
                     DayOpen MONEY NOT NULL,
                     DayHigh MONEY NOT NULL,
                     DayLow MONEY NOT NULL,
                     DayClose MONEY NOT NULL,
                     UNIQUE(StockDate)
                    )

INSERT INTO @Data VALUES (60, 'IDBI', '2014-01-01', 66.50, 67.80, 66.50, 67.60)
INSERT INTO @Data VALUES (197, 'IDBI', '2014-01-02', 67.55, 69.20, 65.25, 65.60)
INSERT INTO @Data VALUES (334, 'IDBI', '2014-01-03', 65.00, 66.40, 64.35, 66.15)
INSERT INTO @Data VALUES (471, 'IDBI', '2014-01-06', 66.15, 66.35, 65.10, 65.55)
INSERT INTO @Data VALUES (608, 'IDBI', '2014-01-07', 66.10, 66.15, 63.85, 64.25)
INSERT INTO @Data VALUES (745, 'IDBI', '2014-01-08', 64.00, 67.10, 63.10, 66.80)
INSERT INTO @Data VALUES (882, 'IDBI', '2014-01-09', 66.60, 67.80, 64.50, 64.75)
INSERT INTO @Data VALUES (1019, 'IDBI', '2014-01-10', 65.00, 65.90, 63.75, 64.10)
INSERT INTO @Data VALUES (1156, 'IDBI', '2014-01-13', 63.85, 65.00, 63.25, 64.20)
INSERT INTO @Data VALUES (1293, 'IDBI', '2014-01-14', 64.00, 64.95, 63.80, 64.05)

;WITH cte AS
(
    SELECT  d.*, ROW_NUMBER() OVER (ORDER BY d.StockDate ASC) AS [RowNum]
    FROM    @Data d
)
SELECT d1.ID, d1.StockName, d1.StockDate, d1.DayOpen, d1.DayHigh,
       d1.DayLow, d1.DayClose, d2.StockDate AS [Next5thDate]
FROM cte d1
LEFT JOIN cte d2
       ON d2.RowNum = (d1.RowNum + 5)

Results: 

ID  StockName  StockDate    DayOpen DayHigh DayLow  DayClose   Next5thDate
60    IDBI     2014-01-01   66.50   67.80   66.50   67.60      2014-01-08
197   IDBI     2014-01-02   67.55   69.20   65.25   65.60      2014-01-09
334   IDBI     2014-01-03   65.00   66.40   64.35   66.15      2014-01-10
471   IDBI     2014-01-06   66.15   66.35   65.10   65.55      2014-01-13
608   IDBI     2014-01-07   66.10   66.15   63.85   64.25      2014-01-14
745   IDBI     2014-01-08   64.00   67.10   63.10   66.80      NULL
882   IDBI     2014-01-09   66.60   67.80   64.50   64.75      NULL
1019  IDBI     2014-01-10   65.00   65.90   63.75   64.10      NULL
1156  IDBI     2014-01-13   63.85   65.00   63.25   64.20      NULL
1293  IDBI     2014-01-14   64.00   64.95   63.80   64.05      NULL

Upvotes: 3

Thomas
Thomas

Reputation: 64645

With RnkedItems As
    (
    Select Id, StockName, StockDate, DayOpen, DayHigh, DayLow, DayClose
        , Row_Number() Over ( Order By StockDate, Id ) As Rnk
    From MyTable
    )
Select ...
From RnkedItems As Original
    Left Join RnkedItems
        On RnkedItems.Rnk = Original.Rnk + 5

I'm assuming you want to return both the target row and the row five entries after the target row on the same line.

Upvotes: 1

Mudassir Hasan
Mudassir Hasan

Reputation: 28751

You can account for next 5th date falling on weekends by extracting name of day from date that falls on next 5th day by skipping date by two extra more days if its Saturday and by one extra day if its Sunday.

Select * , Case DAYNAME( dateadd(day,5,stockdate))
                When 'Saturday' Then dateadd(day,7,stockdate)
                When 'Sunday' Then dateadd(day,6,stockdate)
           Else dateadd(day,6,stockdate) End As newStockDate
From tableName

Upvotes: 0

Denis Valeev
Denis Valeev

Reputation: 6015

Try this:

;with EnumeratedStocks as (
    select rn = row_number() over(order by StockDate), * 
    from Stocks
)
select * from EnumeratedStocks es where rn > 5

Upvotes: 0

Related Questions