update based on date column

• legeboka

  Mr or Mrs. 500

  Points: 588

  More actions

  January 5, 2015 at 2:32 pm

  #299964

  Hi,

  I need help with update statement. I have 2 tables. One table (#Market) stores pricing by customer, terminal, vendor and date and second table (#invoice) stores invoices. I need to update Invoice table based on product and terminal and right date. Price should be applied from matching record and previous date. For example

  invoice number 1002 will have price=0.02 and vendor=555

  invoice Number 1003 will have price = 0.01 and vendor = 123

  Tables have over million records. Thank you

  SELECT customer, terminal, prod, vendor, price, StartDate

  INTO #Market

  FROM (

  SELECT 100, 1, 'a', 123, '0.01', '2014-11-03 00:04:00.000' UNION ALL

  SELECT 100, 1, 'a', 555, '0.02', '2014-11-04 00:04:00.000' UNION ALL

  SELECT 100, 1, 'a', 123, '0.03', '2014-11-05 00:04:00.000' UNION ALL

  SELECT 100, 1, 'a', 123, '0.04', '2014-11-06 00:04:00.000' UNION ALL

  SELECT 255, 2, 'a', 123, '0.01', '2014-11-03 00:04:00.000' UNION ALL

  SELECT 255, 2, 'a', 123, '0.02', '2014-11-04 00:04:00.000' UNION ALL

  SELECT 255, 2, 'a', 123, '0.03', '2014-11-05 00:04:00.000' UNION ALL

  SELECT 255, 2, 'b', 444, '0.06', '2014-11-06 00:04:00.000' UNION ALL

  SELECT 255, 3, 'b', 444, '0.03', '2014-11-07 00:04:00.000'

  ) d ( customer, terminal, prod, vendor, price, StartDate)

  ;

  SELECT PK, InvoiceNum, customer, terminal, prod, vendor, price, InvoiceDate

  INTO #Invoice

  FROM (

  SELECT 1, 1001, 100, 1, 'a', NULL, NULL, '2014-11-03 00:00:00.000' UNION ALL

  SELECT 2, 1002, 100, 1, 'a', NULL, NULL, '2014-11-04 12:12:00.000' UNION ALL

  SELECT 3, 1003, 100, 1, 'a', NULL, NULL, '2014-11-05 00:04:00.000' UNION ALL

  SELECT 4, 1008, 100, 1, 'a', NULL, NULL, '2014-11-06 00:04:00.000' UNION ALL

  SELECT 5, 1100, 255, 2, 'a', NULL, NULL, '2014-11-03 00:04:00.000' UNION ALL

  SELECT 6, 1145, 255, 2, 'a', NULL, NULL, '2014-11-04 00:04:00.000' UNION ALL

  SELECT 7, 1147, 255, 2, 'a', NULL, NULL, '2014-11-05 00:04:00.000' UNION ALL

  SELECT 8, 1150, 255, 2, 'b', NULL, NULL, '2014-11-06 00:04:00.000' UNION ALL

  SELECT 9, 1151, 255, 3, 'b', NULL, NULL, '2014-11-07 00:04:00.000'

  ) Q (PK, InvoiceNum, customer, terminal, prod, vendor, price, InvoiceDate )

  ;

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  January 5, 2015 at 3:56 pm

  #1767893

  legeboka (1/5/2015)

  ---

  Hi,

  I need help with update statement. I have 2 tables. One table (#Market) stores pricing by customer, terminal, vendor and date and second table (#invoice) stores invoices. I need to update Invoice table based on product and terminal and right date. Price should be applied from matching record and previous date. For example

  invoice number 1002 will have price=0.02 and vendor=555

  invoice Number 1003 will have price = 0.01 and vendor = 123

  Tables have over million records. Thank you

  SELECT customer, terminal, prod, vendor, price, StartDate

  INTO #Market

  FROM (

  SELECT 100, 1, 'a', 123, '0.01', '2014-11-03 00:04:00.000' UNION ALL

  SELECT 100, 1, 'a', 555, '0.02', '2014-11-04 00:04:00.000' UNION ALL

  SELECT 100, 1, 'a', 123, '0.03', '2014-11-05 00:04:00.000' UNION ALL

  SELECT 100, 1, 'a', 123, '0.04', '2014-11-06 00:04:00.000' UNION ALL

  SELECT 255, 2, 'a', 123, '0.01', '2014-11-03 00:04:00.000' UNION ALL

  SELECT 255, 2, 'a', 123, '0.02', '2014-11-04 00:04:00.000' UNION ALL

  SELECT 255, 2, 'a', 123, '0.03', '2014-11-05 00:04:00.000' UNION ALL

  SELECT 255, 2, 'b', 444, '0.06', '2014-11-06 00:04:00.000' UNION ALL

  SELECT 255, 3, 'b', 444, '0.03', '2014-11-07 00:04:00.000'

  ) d ( customer, terminal, prod, vendor, price, StartDate)

  ;

  SELECT PK, InvoiceNum, customer, terminal, prod, vendor, price, InvoiceDate

  INTO #Invoice

  FROM (

  SELECT 1, 1001, 100, 1, 'a', NULL, NULL, '2014-11-03 00:00:00.000' UNION ALL

  SELECT 2, 1002, 100, 1, 'a', NULL, NULL, '2014-11-04 12:12:00.000' UNION ALL

  SELECT 3, 1003, 100, 1, 'a', NULL, NULL, '2014-11-05 00:04:00.000' UNION ALL

  SELECT 4, 1008, 100, 1, 'a', NULL, NULL, '2014-11-06 00:04:00.000' UNION ALL

  SELECT 5, 1100, 255, 2, 'a', NULL, NULL, '2014-11-03 00:04:00.000' UNION ALL

  SELECT 6, 1145, 255, 2, 'a', NULL, NULL, '2014-11-04 00:04:00.000' UNION ALL

  SELECT 7, 1147, 255, 2, 'a', NULL, NULL, '2014-11-05 00:04:00.000' UNION ALL

  SELECT 8, 1150, 255, 2, 'b', NULL, NULL, '2014-11-06 00:04:00.000' UNION ALL

  SELECT 9, 1151, 255, 3, 'b', NULL, NULL, '2014-11-07 00:04:00.000'

  ) Q (PK, InvoiceNum, customer, terminal, prod, vendor, price, InvoiceDate )

  ;

  This is what I came up with, there may be other solutions.

  select

  inv.InvoiceNum,

  inv.customer,

  inv.terminal,

  inv.prod,

  ca1.vendor,

  ca1.price,

  inv.InvoiceDate,

  ca1.StartDate

  from

  dbo.Invoice inv

  cross apply(select top (1) * from dbo.Market mrk

  where mrk.customer = inv.customer and

  mrk.terminal = inv.terminal and

  mrk.prod = inv.prod and

  mrk.StartDate <= inv.InvoiceDate

  order by mrk.StartDate desc)ca1