Q:

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?

A:

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

