Consider the following scenario (if it helps think Northwind Orders / OrderDetails).
I have two tables LandingHeaders and LandingDetails, that record details about commercial fishing trips. Typically, over the course of a week, a fishing vessel can make several trips to sea, and so will end up with several LandingHeader/LandingDetail records.
At the end of each week the company that purchases the results of these fishing trips need to work out the value of each landing made by each vessel and then pay the owner of that vessel whatever money is due. To add to fun there are some vessels owned by the same person, so the company purchasing the fish would prefer if the value of all the landings from all of the vessels owned by a given individual were amalgamated into a single payment.
Until now the information required to perform this task was spread across more that a simple master-detail table structure and as such it has required several stored procedures (along with the judicious use of dictionaries in the main application doing the work) to achieve the desired end result. External circumstances beyond my control have forced some major database changes and I have taken the opportunity to restructure the LandingHeader table such that it contains all the necessary information that might be needed.
From the landing Header table I need to record the following fields;
- LandingHeaderId of sql type int
- VesselOwnerId of sql type int
- LandingDate (Just used as part of query in reality) of sql type datetime
From the LandingDetails Table I need to record the following fields;
- ProductId of sql type int
- Quantity of sql type decimal (10,2)
- UnitPrice of sql type money
I have been thinking about creating a query that takes as Parameters VesselOwnerID , SartDate and EndDate.
As output I need to know which LandingId's are associated with the owner and the total Quantity for each Distinct ProductId (along with the UnitPrice which will be the same for each ProductId over the selected period) spread over the various landingDetails associated with the LandingHeaders over the given period.
I have been thinking along the lines of output rows that might look a little like this;
Can this sort of thing be done from a standard master - detail type table relationship or will I still need to resort to multiple stored procedures.
A longer term goal is to have a query that could be used to produce xml that could be adapted for use with a web api.
