Metrics for fiscal usage stats

[Sketchin]

I have an equipment reservation database that is used to create reservations for multiple pieces of equipment for various time periods. I have a metric for "Equipment days used this fiscal year" and feel that the way I am doing my calculations is insane. I basically have 5 queries that each select unique reservations based on when they begin and end. Kind of like Pbaldy's overlapping record solution here:
http://www.baldyweb.com/OverLap.htm

Those queries are then joined in a Uni query with this resulting SQL code:

SELECT tblReservation_details.DateOut, tblReservation_details.DateIn, tblBOM_Master.BOMDescription, weekdays([Forms]![New Main Form]![txtFiscalYearStart],[datein]) AS WorkingDays, Sum(DateDiff("d",[Forms]![New Main Form]![txtFiscalYearStart],[DateIn])+1) AS [Total Usage Days], tblReservations.ReservationNumber, tblCompanies.Category, tblReservations.CategoryID, tblBOM_Master.PurchasePrice, FormatCurrency(((([purchasePrice]/3)*0.026385)*[Total Usage Days]),2) AS IndustryLeaseRate
FROM tblCompanies INNER JOIN (tblReservations INNER JOIN (tblBOM_Master INNER JOIN tblReservation_details ON tblBOM_Master.BOMID = tblReservation_details.BOMNumber) ON tblReservations.ReservationID = tblReservation_details.ReservationID) ON tblCompanies.CompanyID = tblReservations.CompanyID
GROUP BY tblReservation_details.DateOut, tblReservation_details.DateIn, tblBOM_Master.BOMDescription, weekdays([Forms]![New Main Form]![txtFiscalYearStart],[datein]), tblReservations.ReservationNumber, tblCompanies.Category, tblReservations.CategoryID, tblBOM_Master.PurchasePrice, tblReservations.FiscalDateOut, tblReservations.FiscalDateIn, tblBOM_Master.BOMID, tblReservations.ReservationStatus
HAVING (((tblReservation_details.DateOut)<=[Forms]![New Main Form]![txtFiscalYearstart] And (tblReservation_details.DateOut)<=[Forms]![New Main Form]![txtFiscalYearEnd] And (tblReservation_details.DateOut)<=Date()) AND ((tblReservation_details.DateIn)>=[Forms]![New Main Form]![txtFiscalYearStart] And (tblReservation_details.DateIn)<=Date()) AND ((tblReservations.ReservationStatus) In (2,3)))


UNION ALL

SELECT tblReservation_details.DateOut, tblReservation_details.DateIn, tblBOM_Master.BOMDescription, weekdays([DateOut],Date()) AS WorkingDays, Sum(DateDiff("d",[DateOut],Date())+1) AS [Total Usage Days], tblReservations.ReservationNumber, tblCompanies.Category, tblReservations.CategoryID, tblBOM_Master.PurchasePrice, FormatCurrency(((([purchasePrice]/3)*0.026385)*[Total Usage Days]),2) AS IndustryLeaseRate
FROM tblCompanies INNER JOIN (tblReservations INNER JOIN (tblBOM_Master INNER JOIN tblReservation_details ON tblBOM_Master.BOMID = tblReservation_details.BOMNumber) ON tblReservations.ReservationID = tblReservation_details.ReservationID) ON tblCompanies.CompanyID = tblReservations.CompanyID
GROUP BY tblReservation_details.DateOut, tblReservation_details.DateIn, tblBOM_Master.BOMDescription, weekdays([DateOut],Date()), tblReservations.ReservationNumber, tblCompanies.Category, tblReservations.CategoryID, tblBOM_Master.PurchasePrice, tblReservations.FiscalDateOut, tblReservations.FiscalDateIn, tblBOM_Master.BOMID, tblReservations.ReservationStatus
HAVING (((tblReservation_details.DateOut)>=[Forms]![New Main Form]![TxtFiscalYearStart] And (tblReservation_details.DateOut)>Date()) AND ((tblReservation_details.DateIn)<=[Forms]![New Main Form]![txtFiscalYearEnd] And (tblReservation_details.DateIn)>=Date()) AND ((tblReservations.ReservationStatus) In (2,3)))



UNION ALL

SELECT tblReservation_details.DateOut, tblReservation_details.DateIn, tblBOM_Master.BOMDescription, weekdays([DateOut],[DateIn]) AS WorkingDays, Sum(DateDiff("d",[DateOut],[DateIn])+1) AS [Total Usage Days], tblReservations.ReservationNumber, tblCompanies.Category, tblReservations.CategoryID, tblBOM_Master.PurchasePrice, FormatCurrency(((([purchasePrice]/3)*0.026385)*[Total Usage Days]),2) AS IndustryLeaseRate
FROM tblLookup_Reservation_Status INNER JOIN (tblCompanies INNER JOIN (tblReservations INNER JOIN (tblBOM_Master INNER JOIN tblReservation_details ON tblBOM_Master.BOMID = tblReservation_details.BOMNumber) ON tblReservations.ReservationID = tblReservation_details.ReservationID) ON tblCompanies.CompanyID = tblReservations.CompanyID) ON tblLookup_Reservation_Status.ReservationStatusID = tblReservations.ReservationStatus
GROUP BY tblReservation_details.DateOut, tblReservation_details.DateIn, tblBOM_Master.BOMDescription, weekdays([DateOut],[DateIn]), tblReservations.ReservationNumber, tblCompanies.Category, tblReservations.CategoryID, tblBOM_Master.PurchasePrice, tblReservations.FiscalDateOut, tblReservations.FiscalDateIn, tblBOM_Master.BOMID, tblLookup_Reservation_Status.ReservationStatusID
HAVING (((tblReservation_details.DateOut)>=Forms![New Main Form]!txtFiscalYearStart And (tblReservation_details.DateOut)<=Date()) And ((tblReservation_details.DateIn)<=Date() And (tblReservation_details.DateIn)>=Forms![New Main Form]!txtFiscalYearStart) And ((tblLookup_Reservation_Status.ReservationStatusID) In (2,3)))


UNION ALL
SELECT tblReservation_details.DateOut, tblReservation_details.DateIn, tblBOM_Master.BOMDescription, weekdays([DateOut],Date()) AS WorkingDays, Sum(DateDiff("d",[DateOut],Date())+1) AS [Total Usage Days], tblReservations.ReservationNumber, tblCompanies.Category, tblReservations.CategoryID, tblBOM_Master.PurchasePrice, FormatCurrency(((([purchasePrice]/3)*0.026385)*[Total Usage Days]),2) AS IndustryLeaseRate
FROM tblCompanies INNER JOIN (tblReservations INNER JOIN (tblBOM_Master INNER JOIN tblReservation_details ON tblBOM_Master.BOMID = tblReservation_details.BOMNumber) ON tblReservations.ReservationID = tblReservation_details.ReservationID) ON tblCompanies.CompanyID = tblReservations.CompanyID
GROUP BY tblReservation_details.DateOut, tblReservation_details.DateIn, tblBOM_Master.BOMDescription, weekdays([DateOut],Date()), tblReservations.ReservationNumber, tblCompanies.Category, tblReservations.CategoryID, tblBOM_Master.PurchasePrice, tblReservations.FiscalDateOut, tblReservations.FiscalDateIn, tblBOM_Master.BOMID, tblReservations.ReservationStatus
HAVING (((tblReservation_details.DateOut)>=[Forms]![New Main Form]![TxtFiscalYearStart] And (tblReservation_details.DateOut)<Date()) AND ((tblReservation_details.DateIn)>=[Forms]![New Main Form]![txtFiscalYearEnd] And (tblReservation_details.DateIn)>=Date()) AND ((tblReservations.ReservationStatus) In (2,3)))


UNION ALL 

SELECT tblReservation_details.DateOut, tblReservation_details.DateIn, tblBOM_Master.BOMDescription, weekdays([Forms]![New Main Form]![txtFiscalYearStart],[dateinreq]) AS WorkingDays, Sum(DateDiff("d",[Forms]![New Main Form]![txtFiscalYearStart],Date())+1) AS [Total Usage Days], tblReservations.ReservationNumber, tblCompanies.Category, tblReservations.CategoryID, tblBOM_Master.PurchasePrice, FormatCurrency(((([purchasePrice]/3)*0.026385)*[Total Usage Days]),2) AS IndustryLeaseRate
FROM tblCompanies INNER JOIN (tblReservations INNER JOIN (tblBOM_Master INNER JOIN tblReservation_details ON tblBOM_Master.BOMID = tblReservation_details.BOMNumber) ON tblReservations.ReservationID = tblReservation_details.ReservationID) ON tblCompanies.CompanyID = tblReservations.CompanyID
GROUP BY tblReservation_details.DateOut, tblReservation_details.DateIn, tblBOM_Master.BOMDescription, weekdays([Forms]![New Main Form]![txtFiscalYearStart],[dateinreq]), tblReservations.ReservationNumber, tblCompanies.Category, tblReservations.CategoryID, tblBOM_Master.PurchasePrice, tblReservations.FiscalDateOut, tblReservations.FiscalDateIn, tblBOM_Master.BOMID, tblReservations.ReservationStatus
HAVING (((tblReservation_details.DateOut)<=[Forms]![New Main Form]![txtFiscalYearstart] And (tblReservation_details.DateOut)<=[Forms]![New Main Form]![txtFiscalYearEnd] And (tblReservation_details.DateOut)<=Date()) AND ((tblReservation_details.DateIn)>=[Forms]![New Main Form]![txtFiscalYearEnd]) AND ((tblReservations.ReservationStatus) In (2,3)))

UNION ALL SELECT tblReservation_details.DateOut, tblReservation_details.DateIn, tblBOM_Master.BOMDescription, weekdays([Forms]![New Main Form]![txtFiscalYearStart],Date()) AS WorkingDays, Sum(DateDiff("d",[Forms]![New Main Form]![txtFiscalYearStart],Date())+1) AS [Total Usage Days], tblReservations.ReservationNumber, tblCompanies.Category, tblReservations.CategoryID, tblBOM_Master.PurchasePrice, (([purchasePrice]/3)*0.026385)*[workingdays] AS IndustryLeaseRate
FROM tblCompanies INNER JOIN (tblReservations INNER JOIN (tblBOM_Master INNER JOIN tblReservation_details ON tblBOM_Master.BOMID = tblReservation_details.BOMNumber) ON tblReservations.ReservationID = tblReservation_details.ReservationID) ON tblCompanies.CompanyID = tblReservations.CompanyID
GROUP BY tblReservation_details.DateOut, tblReservation_details.DateIn, tblBOM_Master.BOMDescription, weekdays([Forms]![New Main Form]![txtFiscalYearStart],Date()), tblReservations.ReservationNumber, tblCompanies.Category, tblReservations.CategoryID, tblBOM_Master.PurchasePrice, tblReservations.FiscalDateOut, tblReservations.FiscalDateIn, tblBOM_Master.BOMID, tblReservations.ReservationStatus
HAVING (((tblReservation_details.DateOut)<=[Forms]![New Main Form]![txtFiscalYearstart] And (tblReservation_details.DateOut)<=Date()) AND ((tblReservation_details.DateIn)>=[Forms]![New Main Form]![txtFiscalYearStart] And (tblReservation_details.DateIn)>=Date() And (tblReservation_details.DateIn)<=[Forms]![New Main Form]![txtFiscalYearEnd]) AND ((tblReservations.ReservationStatus) In (2,3)));

Now this is where I ask if there is a simpler way to do this? Or is this simply the way that you would normally do this type of calculation?

I probably left out some critical information, so please feel free to ask for clarification.

 

[Rx_]

The Union All is very powerful. I use it often. It has the advantages of being able to evaluate each Select statement for troubleshooting. It also can be added to with new select statements.

It can be difficult to maintain if for example, a new field needs to be added to the return.
I would venture to guess that the performance is better than building a group of queries that chain information together.

 

[plog]

My gut says yes, but my eyes say this is too tedious to work through to find the solution. I suspect you can move most of the criteria in the HAVING clause to the SELECT clause to make this a unionless query. Actually, I think this might most efficiently be done with one sub-query and then a main query that produces your result.

A few questions though:

1. Why are you grouping data? I suspect some of your INNER JOINS are causing duplicates to appear so you made it an aggregate query to eliminate them. This is where I think a sub-query would help.

2. What's the difference between the 5 different queries that comprise that? Try not to use any database jargon, talk to me like I'm a sixth grader.