SQL Server, merging Views

I have been working a lengthy custom project revolving around custom VB apps and a SQL database.

I’m nearing the end and I have hit a brick wall.

I have created two views with data I need to populate my final report with.

The first view is Aquisition records, the second is disposition records.

For simplicity, each view has  columns as follows

Aquisition View
Serial_Number, Aquire_From, Aquire_Date

Disposition View
Serial_Number, Disposed_To, Disposed_Date

These views keep track of our Serialized Product during the production process.  A serial number can travel several times between the plant, vendors, or customer returns. So there can be multiple entries in each view for the same number.

I would like to be able to merge these two views into a report as in the attached sample.

AS you see a serial number can have multiple transactions before being sold.  If a serial number hasn’t been sold yet or no disposition has been made against it yet, the disposition is blank and is considered in our inventory.

Every transaction has a date associated with it (date/time to the millisecond). I haven’t been able to match records up to achieve this report yet.

Please let me know if more info is required.
Thank you.
Who is Participating?
mossmisConnect With a Mentor Author Commented:
I think I might have figured it out, however, I don't know if it will be efficient.
It's a little hard to explain, but maybe you can see in this code:

select MA.serial_number,
MA.transaction_date as ACQ_DATE, 
MA.T_From as ACQ_FROM, 
(select top 1 MD.transaction_date from DISP_VIEW MD where MD.serial_number = MA.serial_number
and MD.transaction_date > MA.transaction_date order by md.transaction_date) as DISP_DATE,
(select top 1 MD.TRANSFER_TO from DISP_VIEW MD where MD.serial_number = MA.serial_number
and MD.transaction_date > MA.transaction_date order by md.transaction_date) as DISP_To

Open in new window

Anthony PerkinsCommented:
Something like this perhaps:
SELECT  a.Serial_Number,
FROM    Aquisition a
        LEFT JOIN Disposition d ON a.Serial_Number = d.Serial_Number

Open in new window

mossmisAuthor Commented:

If it were only that easy. I need to match transactions based on sequence. For every acquisition I need to find the next disposition in date sequence that matches with that serial number. That query you gave would have worked if there were only one transaction per serial number in each of the views, but there are multiple transactions per serial number.
Anthony PerkinsCommented:
I am afraid, I am not following you.
mossmisAuthor Commented:
my select statement I posted seems to work. I did have an issue with one of the views that I fixed, but other than that, it seems OK.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.