locked
update based upon rule RRS feed

  • Question

  • I need a process that will read from my Hdr and Dtl tables and when it finds records in the Cost table apply that cost to a field on the Dtl table. I bolded the PK to the tables.

     Hdr:
     Loc char(3)
     CustNbr char(12)
     InvoiceNbr
     InvoiceDate

     Dtl:
     Loc char(3)
     InvoiceNbr
     InvoiceLineNbr

     MtlType
     Price

     Cost:
     CustNbr
     Pricesuff  char(4)  -- 'regl' 'spcl'
     MtlType char(2) -- 'ro' ml'
     StartDate
     EndDate
     price


     
     The process needs to match the Hdr and Dtl on keys then look at the cost table using these rules to UPDATE Dtl records.

     1) If I find the Custnbr(from Costtbl) and it only has only one cost on the table(Count(Pricesuff) = 1 for that matched customer) 
               then apply price from cost to the correct customer on the Dtl table(all invoices) that fall within the date range for the Invoice.

      date stuff to match:

      (InvoiceDate >= StartDate
     and InVoicedate <= EndDate)



     2) If I find multiple costs( Pricesuff  > 1 for match Cust) that match the criteria from #1 above use cost record that has the min price from the table to apply to all Invoices that match dates.

     3) If I find a match on Customer but can't find a match on dates then use the most current date for in the Cost table for that Cust.

      Say Invoicenbr 123 for customer abc has an invoicedate of 7/15/09 but the last record in the Cost table customer abc has dates of
    6/15/09 - startdate
    6/30/09 - enddate
    0.1111 - price

     then use that record(price) to update all Dtl records for that customer that match. Then write Custnbr,startdate and price to and execption table along with "Using old cost" to a description field. I can then report back that old cost was used and needs updated.


     Thanks very much for solutions.
    Wednesday, July 15, 2009 5:25 PM

Answers

  • Awesome!!!!!


    One more request if I may...

    If I find the match on Hdr/Dtl but Customer doesn't exist on Cost can it write those records to the Execption table also with Desc of " No Cost record found"  with CustNbr and the Invoicedates found.


     THANKS very much !!!!!!
    • Marked as answer by Loganjh Friday, July 17, 2009 3:37 AM
    Friday, July 17, 2009 3:28 AM
  • Try this:

    ;WITH Ranked AS (
    SELECT H.Custnbr, H.InvoiceNbr, D.InvoiceLineNbr, D.Loc, D.MtlType, D.price,
           COALESCE(C1.price, C2.price) AS new_price, 
           COALESCE(C1.StartDate, C2.StartDate) AS start_date, C1.StartDate,
           ROW_NUMBER() OVER(PARTITION BY H.Custnbr, D.MtlType, D.InvoiceNbr, D.InvoiceLineNbr
                             ORDER BY CASE WHEN C1.price IS NOT NULL 
                                           THEN 1
                                           ELSE 2
                                      END, COALESCE(C1.price, C2.price)) AS rk                    
    FROM Hdr AS H
    JOIN Dtl AS D
      ON H.InvoiceNbr = D.InvoiceNbr
     AND H.Loc = D.Loc
    LEFT JOIN Cost AS C1
      ON H.Custnbr = C1.Custnbr
     AND D.MtlType = C1.MtlType
     AND H.InvoiceDate BETWEEN C1.StartDate AND C1.EndDate
    LEFT JOIN Cost AS C2
      ON H.Custnbr = C2.Custnbr
     AND D.MtlType = C2.MtlType
     AND H.InvoiceDate >= C2.StartDate
     AND NOT EXISTS(SELECT *
                    FROM Cost AS C3
                    WHERE C3.Custnbr = C2.Custnbr
                      AND C3.MtlType = C2.MtlType
                      AND C3.StartDate > C2.StartDate
                      AND C3.StartDate <= H.InvoiceDate)) 
    INSERT INTO Exceptions (Custnbr, ExecpDesc, TrackDate)                 
    SELECT Custnbr, CASE WHEN start_date IS NULL
                         THEN 'No Cost exists for Customer'
                         ELSE 'Using old cost'
                     END, start_date
    FROM Ranked
    WHERE rk = 1
      AND StartDate IS NULL;

    Plamen Ratchev
    • Marked as answer by Loganjh Saturday, July 18, 2009 11:44 AM
    Friday, July 17, 2009 3:57 AM

All replies

  • Notes:

    1)  Case 1 and 2 are the same thing. In both cases the minimum price is being used. It just so happens that as case 1 has only one price, it is also the minimum price.
    2)   (InvoiceDate >= StartDate and InVoicedate <= EndDate) is equivalent to: InvoiceDate BETWEEN StartDate AND EndDate ; Arguably, the latter is clearer.
    3) It might be helpful if you provide sample data to work with. Especially in the form of temp TABLEs or CTEs.
    4) Have you tried any queries yet? Specifically, UPDATE...FROM?



    Wednesday, July 15, 2009 5:58 PM
  • That sort of my problem not sure how to code the Update when 3 tables involved and can it all be done in one pass?

     

    Thanks.

    Wednesday, July 15, 2009 7:06 PM
  • Possibly. I didn't look at it enough, and i'm about done for the day. :)

    I suggest you look at the documentation for UPDATE, concentrate on the FROM clause. I'm guessing an OUTER JOIN with an aggregate will do the job, assuming that's how it works.

    In any case, please provide sample data (in the form of SQL statements). And the expected results based on that data. That makes it easier for people to help, and therefore more likely.
    Wednesday, July 15, 2009 7:35 PM
  • Here are the tables and sample data that should test each of the rules. If a customer is defined on the Hdr and Dtl but no Cost record exists then update the price Dtl with zero(0) and Then write Custnbr,Invoicedate and price to and execption table along with "Zero cost" to a description field. I can then report back that zero cost was used and needs updated.  

    CREATE

    TABLE Hdr

    (

    Loc Char(3),

    Custnbr Nvarchar(12),

    InvoiceNbr int,

    InvoiceDate DateTime);

    GO

    CREATE

    TABLE DTL

    (

    Loc Char(3),

    InvoiceNbr int,

    InvoiceLineNbr int,

    MtlType char(2),

    price dec(18,5));

    GO

    INSERT

    Hdr

    SELECT

    'unc','1000','2000','06/01/2009'

    UNION ALL

    SELECT

    'unc','1001','2001','06/15/2009'

    UNION ALL

    SELECT

    'unc','1000','1700','05/30/2009'

    UNION ALL

    SELECT

    'bad','1003','1750','05/15/2009'

    GO

    INSERT

    Dtl

    SELECT

    'unc','2000','1','ro',null

    UNION ALL

    SELECT

    'unc','2000','2','ro',null

    UNION ALL

    SELECT

    'unc','2001','1','ml',null

    UNION ALL

    SELECT

    'unc','2001','2','ml',null

    UNION ALL

    SELECT

    'unc','1700','1','ro',null

    UNION ALL

    SELECT

    'unc','1750','1','ro',null

    GO

    CREATE

    TABLE Cost(

    Custnbr Nvarchar(12),

    Pricesuff char(4),

    MtlType char(2),

    StartDate DATETIME,

    EndDate DATETIME,

    price dec(18,5));

    GO

     

    CREATE

    TABLE Execptions(

    Custnbr Nvarchar(12),

    TrackDate DATETIME,

    ExecpDesc Varchar(999));

    GO

    INSERT

    Cost

    SELECT

    '1000','regl','ro','06/01/2009','06/10/2009',1.1245

    UNION ALL

    SELECT

    '1000','spcl','ro','06/01/2009','06/10/2009',1.1211

    UNION ALL

    SELECT

    '1000','regl','ro','04/01/2009','04/10/2009',1.1211

    Union all

    SELECT

    '1000','regl','ml','06/01/2009','06/10/2009',1.1245

    UNION ALL

    SELECT

    '1000','spcl','ml','06/01/2009','06/10/2009',1.1211

    go

     

    Thanks !!!!! hope this helps solve the problem.

    Wednesday, July 15, 2009 9:04 PM
  • any help I could get would be appriciated. Is there anything else I need to add to help solve.


     Thanks!!!
    Thursday, July 16, 2009 3:16 AM
  • Your logic seems a bit too complex to put in UPDATE statement directly. Looks like you need to create a function GetCost and pass CustNmbr and other info.

    This is just an idea, I leave the implementation details to you.
    Thursday, July 16, 2009 3:24 AM
  • Any examples on how to make that work?

    Thanks.

    Thursday, July 16, 2009 10:05 AM
  • I have pieces of it working but having an issue trying to find the last dates if the original search doesn't get a hit.


     If the invoicedate is 5/15/09 but in the cost table the last good price for that customer had a Startdate of 5/1/09 and EndDate of 5/8/09.

     Instead of uisng a zero price how do grab the price from Startdate of 5/1/09 and EndDate of 5/8/09 and record that in the execption table that I used an OLD
    price.


     Thanks.
    Friday, July 17, 2009 12:05 AM
  • Select Cost.CustomerID, Cost.Price, Cost.StartDate, Cost.EndDate from Cost inner join (select C.CustomerID, max(c.EndDate) as LastEndDate from Cost C where c.EndDate <=@PassedDate group by C.CustomerID) LD on Cost.CustomerID = LD.CustomerID and Cost.EndDate = LD.LastEndDate
    Friday, July 17, 2009 2:23 AM
  • How does it know which customer/InvoiceDate from Hdr to apply the correct cost to the Dtl(Invoice)?


     Thanks.


     

    Friday, July 17, 2009 2:45 AM
  • I thought you were going to create it as a function and pass two parameters to it. CustomerID and InvoiceDate
    Friday, July 17, 2009 2:50 AM
  • Do you have a sample of what that would look like --  the function.


     I just joined the Hdr/Dtl an Cost to get 90% of the desired result for the date logic. Didn't know how to make it look at past dates.


     Thanks.
    Friday, July 17, 2009 2:55 AM
  • To update try this:

    ;WITH Ranked AS (
    SELECT H.Custnbr, H.InvoiceNbr, D.InvoiceLineNbr, D.Loc, D.MtlType, D.price,
           COALESCE(C1.price, C2.price) AS new_price, 
           COALESCE(C1.StartDate, C2.StartDate) AS start_date,
           ROW_NUMBER() OVER(PARTITION BY H.Custnbr, D.MtlType, D.InvoiceNbr, D.InvoiceLineNbr
                             ORDER BY CASE WHEN C1.price IS NOT NULL 
                                           THEN 1
                                           ELSE 2
                                      END, COALESCE(C1.price, C2.price)) AS rk                    
    FROM Hdr AS H
    JOIN Dtl AS D
      ON H.InvoiceNbr = D.InvoiceNbr
     AND H.Loc = D.Loc
    LEFT JOIN Cost AS C1
      ON H.Custnbr = C1.Custnbr
     AND D.MtlType = C1.MtlType
     AND H.InvoiceDate BETWEEN C1.StartDate AND C1.EndDate
    LEFT JOIN Cost AS C2
      ON H.Custnbr = C2.Custnbr
     AND D.MtlType = C2.MtlType
     AND H.InvoiceDate >= C2.StartDate
     AND NOT EXISTS(SELECT *
                    FROM Cost AS C3
                    WHERE C3.Custnbr = C2.Custnbr
                      AND C3.MtlType = C2.MtlType
                      AND C3.StartDate > C2.StartDate
                      AND C3.StartDate <= H.InvoiceDate))
    UPDATE Dtl
    SET price = new_price
    FROM Dtl AS D
    JOIN Ranked AS R
      ON D.InvoiceNbr = R.InvoiceNbr
     AND D.InvoiceLineNbr = R.InvoiceLineNbr
     AND D.Loc = R.Loc
     AND D.MtlType = R.MtlType
     AND R.rk = 1
     AND R.start_date IS NOT NULL;

    To insert exceptions:

    ;WITH Ranked AS (
    SELECT H.Custnbr, H.InvoiceNbr, D.InvoiceLineNbr, D.Loc, D.MtlType, D.price,
           COALESCE(C1.price, C2.price) AS new_price, 
           COALESCE(C1.StartDate, C2.StartDate) AS start_date, C1.StartDate,
           ROW_NUMBER() OVER(PARTITION BY H.Custnbr, D.MtlType, D.InvoiceNbr, D.InvoiceLineNbr
                             ORDER BY CASE WHEN C1.price IS NOT NULL 
                                           THEN 1
                                           ELSE 2
                                      END, COALESCE(C1.price, C2.price)) AS rk                    
    FROM Hdr AS H
    JOIN Dtl AS D
      ON H.InvoiceNbr = D.InvoiceNbr
     AND H.Loc = D.Loc
    LEFT JOIN Cost AS C1
      ON H.Custnbr = C1.Custnbr
     AND D.MtlType = C1.MtlType
     AND H.InvoiceDate BETWEEN C1.StartDate AND C1.EndDate
    LEFT JOIN Cost AS C2
      ON H.Custnbr = C2.Custnbr
     AND D.MtlType = C2.MtlType
     AND H.InvoiceDate >= C2.StartDate
     AND NOT EXISTS(SELECT *
                    FROM Cost AS C3
                    WHERE C3.Custnbr = C2.Custnbr
                      AND C3.MtlType = C2.MtlType
                      AND C3.StartDate > C2.StartDate
                      AND C3.StartDate <= H.InvoiceDate)) 
    INSERT INTO Exceptions (Custnbr, ExecpDesc, TrackDate)                 
    SELECT Custnbr, 'Using old cost', start_date
    FROM Ranked
    WHERE rk = 1
      AND StartDate IS NULL
      AND start_date IS NOT NULL;

    Plamen Ratchev
    • Proposed as answer by Naomi N Friday, July 17, 2009 3:35 AM
    Friday, July 17, 2009 3:07 AM
  • Awesome!!!!!


    One more request if I may...

    If I find the match on Hdr/Dtl but Customer doesn't exist on Cost can it write those records to the Execption table also with Desc of " No Cost record found"  with CustNbr and the Invoicedates found.


     THANKS very much !!!!!!
    • Marked as answer by Loganjh Friday, July 17, 2009 3:37 AM
    Friday, July 17, 2009 3:28 AM
  • Thanks very much !!!!!!!

     How difficult would be it to write execptions when customer is linked from Hdr/Dtl but no Cost record exists?


       Add CustNbr InvoiceDate and Desc of "No Cost exists for Customer"



     Many thanks !!!!
    Friday, July 17, 2009 3:50 AM
  • Try this:

    ;WITH Ranked AS (
    SELECT H.Custnbr, H.InvoiceNbr, D.InvoiceLineNbr, D.Loc, D.MtlType, D.price,
           COALESCE(C1.price, C2.price) AS new_price, 
           COALESCE(C1.StartDate, C2.StartDate) AS start_date, C1.StartDate,
           ROW_NUMBER() OVER(PARTITION BY H.Custnbr, D.MtlType, D.InvoiceNbr, D.InvoiceLineNbr
                             ORDER BY CASE WHEN C1.price IS NOT NULL 
                                           THEN 1
                                           ELSE 2
                                      END, COALESCE(C1.price, C2.price)) AS rk                    
    FROM Hdr AS H
    JOIN Dtl AS D
      ON H.InvoiceNbr = D.InvoiceNbr
     AND H.Loc = D.Loc
    LEFT JOIN Cost AS C1
      ON H.Custnbr = C1.Custnbr
     AND D.MtlType = C1.MtlType
     AND H.InvoiceDate BETWEEN C1.StartDate AND C1.EndDate
    LEFT JOIN Cost AS C2
      ON H.Custnbr = C2.Custnbr
     AND D.MtlType = C2.MtlType
     AND H.InvoiceDate >= C2.StartDate
     AND NOT EXISTS(SELECT *
                    FROM Cost AS C3
                    WHERE C3.Custnbr = C2.Custnbr
                      AND C3.MtlType = C2.MtlType
                      AND C3.StartDate > C2.StartDate
                      AND C3.StartDate <= H.InvoiceDate)) 
    INSERT INTO Exceptions (Custnbr, ExecpDesc, TrackDate)                 
    SELECT Custnbr, CASE WHEN start_date IS NULL
                         THEN 'No Cost exists for Customer'
                         ELSE 'Using old cost'
                     END, start_date
    FROM Ranked
    WHERE rk = 1
      AND StartDate IS NULL;

    Plamen Ratchev
    • Marked as answer by Loganjh Saturday, July 18, 2009 11:44 AM
    Friday, July 17, 2009 3:57 AM
  • Just wanted to say thanks again!!!! 

     This did exactly what I wanted.
    Saturday, July 18, 2009 11:44 AM