update based on date column

  • 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 )

    ;

  • 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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply