User Profile
ImaDAXman
Frequent Visitor
Joined 9 years ago
User Widgets
Contributions
Aggregate $Amt from fact table based on dynamic date range from a different fact table
I've been struggling with this one for a while. I know the solution is going to be fairly easy and I'll be kicking myself for having spent such a long time blanking on this. Here is what I'm trying to accomplish: Calculate the total 'recon' (reconditioning) spend by vehicle. For each Stock #, I need the total RO $Amt from type 'Internal' only from FactServHist but only include RO's that fall between the Recon Start and Recon End dates found in FactInventory (inclusive of start/end dates). I have 5 tables involved in this example: Date (includes Date, DateSK in text format, day, year, month, qtr, etc) DimInventory (houses stock#, dealer that owns it, and all info regarding the vehicle. Stock # is unique for this table) FactInventory (houses workflow-driven data including process start and complete dates) FactServHistory (houses every Repair Order - RO# $Amt, date complete, Stock#, etc) RO Ref (reference table created from FactServHist - Stock# lookup by RO#- this example doesn't show it but in the source data the RO detail is stored by line item and so there are several rows per RO)Re: Aggregate $Amt from fact table based on dynamic date range from a different fact table
I've been struggling with this one for a while. I know the solution is going to be fairly easy and I'll be kicking myself for having spent such a long time blanking on this. Here is what I'm trying to accomplish: Calculate the total 'recon' (reconditioning) spend by vehicle. For each Stock #, I need the total RO $Amt from type 'Internal' only from FactServHist but only include RO's that fall between the Recon Start and Recon End dates found in FactInventory (inclusive of start/end dates). I have 5 tables involved in this example: Date (includes Date, DateSK in text format, day, year, month, qtr, etc) DimInventory (houses stock#, dealer that owns it, and all info regarding the vehicle. Stock # is unique for this table) FactInventory (houses workflow-driven data including process start and complete dates) FactServHistory (houses every Repair Order - RO# $Amt, date complete, Stock#, etc) RO Ref (reference table created from FactServHist - Stock# lookup by RO#- this example doesn't show it but the source data is more complex and RO# in FactServHist is not unique so I had to create and index to use as PK/FK
