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.
Comments
Post a Comment