update table with mulitple inputs for selection

Question

question

bruin asked Apr 18, '10

update table with mulitple inputs for selection

I had another requirement added to an existing update query and looking for a solution. The Plist table has a new field added Region anda NEW Cust table with the Region field

I need to make sure the initial update pulls the most current plist for the customer. If the Customer has 2 Plist entries that could meet the criteria make sure we grab the most current customer record and sdate that's not a "Base record"for that customer. If we only select the "BASE Record" when no other Plist record meets the criteria.

Here was the previous and I need to add another table to the mix.

USE tempdb ;
GO
CREATE
 TABLE Plist
    (
      custcode VARCHAR(12),
      mtlcode CHAR(2),
      price DEC(18, 4),
      region CHAR(3),      --- NEW FIELD ADDED
      plist VARCHAR(12),  -- name 
      sdate DATETIME,
      edate DATETIME,
      PRIMARY
 KEY ( custcode, mtlcode, plist, sdate )
    ) ;
INSERT  INTO PList
        SELECT  'dwade',
                'sr',
                1.200,
                'usd',
                'mtla',
                '03/01/2010',
                'null'
        UNION ALL
INSERT    INTO    PList 
        SELECT  'dwade',
                'sr',
                1.200,
                'usd',
                'mtla',
                '04/01/2010',
                '12/31/2010'
        UNION
 ALL
        SELECT  'dwade',
                'sr',
                1.100,
                'usd',
                'mtlb',
                '02/01/2010',
                '02/28/2010'
        UNION
 ALL
        SELECT  'dwade',
                'sr',
                1.000,
                'usd',
                'mtlc',
                '01/01/2010',
                '01/31/2010'
        UNION
 ALL
        SELECT  'basecode',
                'br',
                1.000,
                'usd',
                'cbase',
                '01/01/2010',
                NULL
        UNION
 ALL
        SELECT  'basecode',
                'br',
                1.500,
                'usd' 'cbase',
                '12/01/2009',
                NULL

CREATE
 TABLE Shdr
    (
      Custcode VARCHAR(12),
      InvoiceNbr NCHAR(10)
    ) 
INSERT  INTO Shdr
        SELECT  'dwade',
                '123456'
        UNION
 ALL
        SELECT  'jimray',
                '224466' ;
CREATE
 TABLE Sdtl
    (
      InvoiceNbr VARCHAR(12),
      InvLine INT,
      Invdate DATETIME,
      Mtlcode CHAR(2),
      saddr DEC(18, 4)
    ) 
CREATE
 TABLE SdtlLOG
    (
      InvoiceNbr VARCHAR(12),
      InvLine INT,
      Invdate DATETIME,
      Mtlcode CHAR(2),
      saddr DEC(18, 4),
      LogDate DATETIME DEFAULT ( GETDATE() )
    )
INSERT  INTO Sdtl
        SELECT  '123456',
                1,
                '4/12/2010',
                'sr',
                NULL
        UNION
 ALL
        SELECT  '224466',
                1,
                '3/2/2010',
                'sr',
                NULL

 -- NEW TABLE ADDED TO THE Criteria for LOOKUP the make sure correct Customer  
 -- selected along with correct region and to make sure valid customer
CREATE
 TABLE Cust
    (
      Custcode VARCHAR(12),
      Region CHAR(3)
    ) 
INSERT  INTO Cust
        SELECT  'dwade',
                'usd'
        UNION
 ALL
        SELECT  'blanier',
                'can' 

SELECT  *
FROM    plist
 -- NOTE 
 -- This works but doesn't seem to pull most current if 2 Plist's meet criteria
UPDATE  d
SET     d.saddr = pl.Price
OUTPUT  inserted.*
        INTO
 SdtlLOG ( InvoiceNbr, InvLine, Invdate, Mtlcode, saddr )
FROM    PList pl
        INNER
 JOIN Shdr sh ON pl.CustCode = sh.CustCode
        INNER
 JOIN Sdtl d ON sh.InvoiceNbr = d.InvoiceNbr
                AND d.InvDate >= pl.Sdate
                AND ( pl.Edate IS
 NULL
                      OR d.InvDate <= pl.edate
                    )
                AND pl.Mtlcode = d.Mtlcode 
-- Do we have 2 update queries to do catch the BASE records or  
-- can it be combined with the query above
UPDATE  d
SET     d.saddr = l.price
OUTPUT  inserted.*
        INTO
 SdtlLOG ( InvoiceNbr, InvLine, Invdate, Mtlcode, saddr )
FROM    plist l
        INNER
 JOIN ( SELECT  sdate = MAX(sdate)
        FROM    PList pl
        WHERE   custcode = 'basecode'
      ) b ON l.sdate = b.sdate
        INNER
 JOIN sdtl d ON l.mtlcode = d.mtlcode
WHERE   saddr IS
 NULL

GO
SELECT  *
FROM    sdtl
/*
InvoiceNbr  InvLine Invdate Mtlcode saddr
123456  1   2010-03-15 00:00:00.000 sr  1.2000
224466  1   2010-03-02 00:00:00.000 sr  1.0000
*/
SELECT  *
FROM    sdtlLOG
/*
InvoiceNbr  InvLine Invdate Mtlcode saddr   LogDate
123456  1   2010-03-15 00:00:00.000 sr  1.2000  2010-03-30 02:19:14.050
224466  1   2010-03-02 00:00:00.000 sr  1.0000  2010-03-30 02:19:14.060
*/
GO 
GO 
DROP TABLE
 plist ;
DROP TABLE
 shdr ;
DROP TABLE
 sdtl ;
DROP TABLE
 sdtlLOG ;

Thanks Some more info: It seems when a base record exists for a customer than in first update it choose that record.

data to add to test that.

UNION
ALL

SELECT  'dwade',
        'sr',
        1.000,
        'usd',
        'cbase',
        '04/01/2010',
        NULL

Thanks again

t-sql update

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack ♦♦ commented · Apr 19, 2010 at 05:23 AM

OK, formatted now but thats 10 minutes of my life I wont get back.

3 ·

Matt Whitfield ♦♦ commented · Apr 19, 2010 at 03:21 AM

I'm sorry, I usually try and format questions, but... wow. Try formatting your script a bit better and editing your question, you'll probably get better answers like that.

1 ·

Matt Whitfield ♦♦ commented · Apr 19, 2010 at 05:42 AM

@Fatherjack - good effort! I would +1 if I could...

0 ·

Answer 1 · 2010-04-19T08:38:48Z

Grant Fritchey answered Apr 19, '10

Wading through it all, if I understand what you're asking, the derived table here is giving you a problem:

 INNER JOIN 
( SELECT  sdate = MAX(sdate)        
FROM    PList pl        
WHERE   custcode = 'basecode'      ) b ON l.sdate = b.sdate

I suspect because you're not using the entire PK of the PList table. Presumably, the difference is simply one of of the sdate column, to get the latest value, and since you're only supplying a single part of the key you may be getting multiple values.

Something more like this might work:

FROM  sdtl d
INNER JOIN  plist l 
ON l.mtlcode = d.mtlcode
and l.sdate = (SELECT TOP 1 sdate 
FROM    PList pl        
WHERE   pl.custcode = l.costode  
and pl.mtlcode = l.mtlcode
and pl.plist = l.plist
ORDER BY pl.sdate DESC    )

But even that's more work than it seems like you need. You're capturing the output of the creation of new plist entries in the statement above. Why not just use that output to present the data for the next insert?

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack ♦♦ commented · Apr 19, 2010 at 09:22 AM

+1 indeed .

1 ·

Matt Whitfield ♦♦ commented · Apr 19, 2010 at 08:47 AM

+1 - for dedication!

0 ·

Grant Fritchey ♦♦ commented · Apr 19, 2010 at 10:06 AM

WEll, even my vague guess wouldn't have been possible without @Fatherjack reformatting it.

0 ·

Answer 2 · 2010-04-19T21:38:40Z

Daniel Ross answered Apr 19, '10

That's the right answer, using cross apply will speed up the query;

FROM sdtl d INNER JOIN plist l on l.mtlcode=d.mtlcode CROSS APPLY ( SELECT TOP 1 z.sdate FROM plist z WHERE z.custcode=l.custcode AND z.mtlcode=l.mtlcode AND z.plist=l.plist ORDER BY z.sdate DESC ) q where q.sdate=l.sdate

I don't know why using cross apply is faster, but it is. Very handy indeed.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question