CODEWITHSUNDEEP
sqlsql-server
Kyle Vbcrlf Rickaby
Kyle Vbcrlf Rickaby

Reputation: 87

Grabbing the cost with the max date grouping on number openquery

I am trying to select information from another server, and insert it into a table through open query... Here is where I am at so far:

INSERT INTO smallprojects..PhyInv_310QADLockedDet (MasterRecid, location, partnum, qty) 
             SELECT @@IDENTITY, ld_loc, ld_part, ld_qty_oh 
             FROM OPENQUERY(LANSRHQAD, 'SELECT ld_loc,ld_part,ld_qty_oh FROM PUB.ld_det as a left outer join PUB.pt_mstr as b on a.ld_part = b.pt_part where pt_status <> ''OB'' and ld_part not like ''S%'' and ld_part not like ''N%'' and ld_loc = ''310'' ')

But this will insert multiple part numbers if the PUB.ld_det has multiple entries for that part, sort of like the example below:

Here is the data (PUB.ld_det):

Part | Date  | Qty
-------------------
1000 | 10-02 | 0
1000 | 10-03 | 2
1001 | 10-2  | 0
1001 | 10-2  | 2

I would like my result to be a insert into a table as:

Part | Qty
-------------------
1000 | 2
1001 | 2

Currently it is returning as:

Part | Qty
-----------
1000 | 0
1000 | 2
1001 | 0
1001 | 2

So when I go back to update this table I just have to hope it finds the right row.

How can I avoid bringing in the multiples and only bring it in with the highest date? The open query thing messes with me so much

Upvotes: 1

Views: 53

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270733

Here is one simple method is you want one row per part:

INSERT INTO smallprojects..PhyInv_310QADLockedDet (MasterRecid, location, partnum, qty) 
     SELECT TOP (1) WITH TIES @@IDENTITY, ld_loc, ld_part, ld_qty_oh 
     FROM OPENQUERY(LANSRHQAD, 'SELECT ld_loc,ld_part,ld_qty_oh FROM PUB.ld_det as a left outer join PUB.pt_mstr as b on a.ld_part = b.pt_part where pt_status <> ''OB'' and ld_part not like ''S%'' and ld_part not like ''N%'' and ld_loc = ''310'' ') 
     ORDER BY ROW_NUMBER() OVER (PARTITION BY ld_part ORDER BY ld_qty_oh DESC);

Use RANK() if you want duplicates when there are ties.

Upvotes: 1

Related Questions