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

-

i working on query need next nth no of row. table structure 

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 want output column name give me next 5th row date

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

and can't use datediff -5 day count these working day data excluding weekend days

how value without using while loop or cursor?

use cte return base table plus row_number can left join cte on row_number of 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 @data values (60, 'idbi', '2014-01-01', 66.50, 67.80, 66.50, 67.60) insert @data values (197, 'idbi', '2014-01-02', 67.55, 69.20, 65.25, 65.60) insert @data values (334, 'idbi', '2014-01-03', 65.00, 66.40, 64.35, 66.15) insert @data values (471, 'idbi', '2014-01-06', 66.15, 66.35, 65.10, 65.55) insert @data values (608, 'idbi', '2014-01-07', 66.10, 66.15, 63.85, 64.25) insert @data values (745, 'idbi', '2014-01-08', 64.00, 67.10, 63.10, 66.80) insert @data values (882, 'idbi', '2014-01-09', 66.60, 67.80, 64.50, 64.75) insert @data values (1019, 'idbi', '2014-01-10', 65.00, 65.90, 63.75, 64.10) insert @data values (1156, 'idbi', '2014-01-13', 63.85, 65.00, 63.25, 64.20) insert @data values (1293, 'idbi', '2014-01-14', 64.00, 64.95, 63.80, 64.05)  ;with cte (     select  d.*, row_number() on (order d.stockdate asc) [rownum]        @data d ) select d1.id, d1.stockname, d1.stockdate, d1.dayopen, d1.dayhigh,        d1.daylow, d1.dayclose, d2.stockdate [next5thdate] 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

Comments

Post a Comment

Popular posts from this blog

c# - Unity IoC Lifetime per HttpRequest for UserStore -

-
i'm trying clean default implementation of accountcontroller.cs comes out of box in new mvc5/owin security implementation. have modified constructor this: private usermanager<applicationuser> usermanager; public accountcontroller(usermanager<applicationuser> usermanager) { this.usermanager = usermanager; } also, have created lifetime manager unity looks this: public class httpcontextlifetimemanager<t> : lifetimemanager, idisposable { private httpcontextbase _context = null; public httpcontextlifetimemanager() { _context = new httpcontextwrapper(httpcontext.current); } public httpcontextlifetimemanager(httpcontextbase context) { if (context == null) throw new argumentnullexception("context"); _context = context; } public void dispose() { this.removevalue(); } public override object get...
Read more

Change the color of an oval at click in Java AWT -

-
i have draw ovals in java, , @ click change color. beginning tried change color after 20 ms, doesn't work. my code is: public class mycomponentnew extends frame { public graphics2d g2d; public mycomponentnew(string title) { super(title); setsize(400, 550); } @override public void paint(graphics g) { this.g2d = (graphics2d) g; this.g2d.setcolor(color.red); this.g2d.filloval(10, 55, 50, 100); } public void changecolor () { this.g2d.setcolor(color.blue); this.g2d.filloval(10, 55, 50, 100); } } and in class main method have: mycomponentnew m; m = new mycomponentnew("fereastra cu baloane"); m.setvisible(true); m.addwindowlistener(new windowadapter() { @override public void windowclosing(windowevent we) { system.exit(0); } }); try { thread.sleep(20); } catch(interruptedexception e) {} m.changecolor(); the color of oval remains red. ...
Read more

I am trying to solve the error message 'incompatible ranks 0 and 1 in assignment' in a fortran 95 program. -

-
i writing program in fortran find velocity of parachuting person in relation time. keep getting error can't fix. new programming , appreciated. the error is v(i+1)=v(i)+[32-((c*v(i)*v(i))/m)]*(h) 1 error: incompatible ranks 0 , 1 in assignment @ (1) and program is program para integer :: real :: v(11) !velocity real :: q !initial velocity real :: h !time step real :: c !drag coefficient real :: m !mass ! gravity equal 32 ft/s^2 write (*,*)'enter time step' read(*,*)h write(*,*)'enter initial velocity' read(*,*)q write(*,*)'enter drag coefficient' read(*,*)c write(*,*)'enter mass' read(*,*)m i=1,10 ! 1 10, 1 being interval. end v(i+1)=v(i)+[32-((c*v(i)*v(i))/m)]*(h) q=v(1) end program you cannot use [] normal parenthesis in expressions. array constructor, [ items ] means array items elements. end do should after line.
Read more