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
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
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
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.
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.
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:
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial