CODEWITHSUNDEEP
sqlsql-server
DPool34
DPool34

Reputation: 85

Need help linking an ID column with another ID column for an insert

I'm generating a dataset from a stored procedure. There's an ID column from a different database that I need to link to my dataset. Going from one database to the other isn't the issue. The problem is I'm having trouble linking an ID column that's in one database and not the other.

There's a common ID between the two columns: CustomerEventID. I know that'll be my link, but here's the issue. The CustomerEventID is a unique ID for whenever a customer purchases something in a single encounter.

The column I need to pull in is more discrete. It creates a unique ID (CustomerPurchaseID) for each item purchased from the encounter. However, I only need to pull in the CustomerPurchaseID for the last item purchased in that encounter. Unfortunately there's no time stamp associated with the CustomerPurchaseID.

It's basically like the CustomerEventID is a unique ID for a customer's receipt, whereas the CustomerPurchaseID is a unique ID for each item purchased on that receipt.

What would be the best way to pull the last CustomerPurchaseID from the CustomerEventID (I only need the last item on the receipt in my dataset)? I'll be taking my stored procedure with the dataset (from database A), using an SSIS package to put the dataset into a table on database B, then inserting the CustomerPurchaseID into that table.

I'm not sure if it helps, but here's the query from the stored procedure that will be sent to the other database (the process will run every 2 weeks to send it to Database B):

SELECT
    ce.CustomerEventID,
    ce.CustomerName,
    ce.CustomerPhone,
    ce.CustomerEventDate
FROM 
    CustomerEvent ce
WHERE 
    DATEDIFF(d, ce.CustomerEventDate, GETDATE()) < 14

Thanks for taking the time to read this wall of text. :)

Example

Upvotes: 0

Views: 130

Answers (1)

sallushan
sallushan

Reputation: 1147

If CustomerPurchaseID field is increasing (as you mentioned) then you can do Order by Desc on that field while picking up the line. This can be done using a sub-query in parent query or by doing a Outer Apply or Cross Apply if you need all the fields from CustomerPurchase table. Check below example.

declare @customerEvent table(CustomerEventID int not null primary key identity
                                , EventDate datetime)
declare @customerPurchase table(CustomerPurchaseID int not null primary key identity
                                , CustomerEventID int, ItemID varchar(100))

insert into @customerEvent(EventDate)
    values ('2018-01-01'), ('2018-01-02'), ('2018-01-03'), ('2018-01-04')
insert into @customerPurchase(CustomerEventID, ItemID)
    values (1, 1), (1, 2), (1, 3)
            , (2, 3), (2, 4), (2, 10)
            , (3, 1), (3, 2)
            , (4, 1)

-- if you want all the fields from CustomerPurchase Table
select  e.CustomerEventID
        , op.CustomerPurchaseID
from    @customerEvent as e
        outer apply (select top 1 p.* from @customerPurchase as p where p.CustomerEventID = e.CustomerEventID
                        order by CustomerPurchaseID desc) as op

-- if you want only the last CustomerPurchaseID from CustomerPurchase table
select  e.CustomerEventID
        , (select top 1 CustomerPurchaseID from @customerPurchase as p where p.CustomerEventID = e.CustomerEventID
                order by CustomerPurchaseID desc)
            as LastCustomerPurchaseID
from    @customerEvent as e

Upvotes: 1

Related Questions