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

Popular posts from this blog

python - Healpy: From Data to Healpix map -

c - Bitwise operation with (signed) enum value -

xslt - Unnest parent nodes by child node -