• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 599
  • Last Modified:

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.
sqlmerge.pdf
0
mossmis
Asked:
mossmis
  • 3
  • 2
1 Solution
 
Anthony PerkinsCommented:
Something like this perhaps:
SELECT  a.Serial_Number,
        a.Aquire_From,
        a.Aquire_Date,
        d.Disposed_To,
        d.Disposed_Date
FROM    Aquisition a
        LEFT JOIN Disposition d ON a.Serial_Number = d.Serial_Number

Open in new window

0
 
mossmisAuthor Commented:
acperkins,

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.
0
 
Anthony PerkinsCommented:
I am afraid, I am not following you.
0
 
mossmisAuthor 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, 
MA.NOTES as ACQ_NOTES,
(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
from ACQ_VIEW MA

Open in new window

0
 
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.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now