sql server - Sql Closest 2 Days to Today -

i have product table , join ordertracking table

product

• productcode
• productname
• manufacturercode
• price
• etc...

ordertracking

• productcode
• orderid
• ammount
• deliverydate
• shippingnumber
• etc...

---

select product.productcode,   product left outer join ordertracking     on product.productcode=ordertracking.productcode   group productcode 

there past , future order infos of products in ordertracking table. goal if there future orders of product want list first 2 of them(closest 2 orders today) in same row. if there no order in future, columns null or if there 1 order in future, second column null. example row that:

• productcode

• 1stclosestdeliverydate

• 1stclosestorderamount
• 1stshippingnumber
• 2ndclosestdeliverydate
• 2ndclosestorderamount

• 2ndshippingnumber

     select product.productcode, 1stclosestdeliverydate,1tclosestorderamount,      1stshippingnumber,2stclosestorderdate, 2stclosestdeliveryamount,      2stshippingnumber    product left outer join ordertracking     on product.productcode = ordertracking.productcode    group product.productcode 

  i should use getdate() function , compare other date values couldn't figure out how these values ordertracking table. appreciate...

use cte row_number:

with orders (  select row_number() on (partition productcode order orderdate asc) rnumber        , productcode        , orderdate        , orderamount        , ordershippingnumber  ordertracking  orderdate > getdate() ) select product.productcode        , 1stclosestorderdate = o1.orderdate        , 1tclosestorderamount = o1.orderamount        , 1stshippingnumber = o1.ordershippingnumber        , 2stclosestorderdate = o2.orderdate        , 2stclosestorderamount = o2.orderamount        , 2stshippingnumber = o2.ordershippingnumber product        left outer join orders o1 on product.productcode = o1.productcode , o1.rnumber = 1        left outer join orders o2 on product.productcode = o2.productcode , o2.rnumber = 2; 

in structure post there column missing guessed name above should starting point final result.

the first select (the cte) gathers data orders adding row numbers product: rows row number = 1 next order product , rows number = 2 second next order.

the second select there join table & cte required output.

warning: cte syntax requires previous statement (if any) terminated ;. since closing semicolon not mandatory it's easy forget , syntax errors when running whole statement in script multiple statements.