I need to get the the count of the number of records that the left join returns.
In the example the number contract of periods is incorrect because it counts the number of periods based on the contract_netstartdate and contract_netenddate (jan thru apr = 4)
But that is inaccurate because when I join to the Fiscal Period table I get the Fiscal Dates Start and End, the contract dates fall actually on only 3 fiscal calendar dates periods.
A quick way to solve this, I was thinking, would be to get a count of the numbers of records in this example 3, that the left join to the fiscal period table returns. The only problem is that I do not have the technical expertise to come up with this query and I was wondering if any one in this forum would be so kind to lend me a helping hand.
I am posting an example of my actual query, with the left join to the fiscal period table.
, MAX(so.ItemDescription) AS ItemDescription
, MAX(so.ContractStart) AS ContractStart
, MAX(so.ContractEnd) AS ContractEnd
, MAX(so.Contract_NetStartDate) AS Contract_NetStartDate
, MAX(so.Contract_NetEndDate) AS Contract_NetEndDate
, MAX(fp.StartDate) AS FiscalPeriodStartDate
, MAX(fp.EndDate) AS FiscalPeriodEndDate
, MAX(so.Contract_NetNumberOfPeriods) AS Contract_NetNumberOfPeriods
, fp.FiscalPeriodCode AS [Contract_FiscalPeriodCode]
, SUM(so.LineOpenQuantity_Prorated) AS LineOpenQuantity_Prorated
LEFT OUTER JOIN
DW.dim.FiscalPeriod fp ON
(fp.StartDate BETWEEN so.Contract_NetStartDate AND so.Contract_NetEndDate) OR
(fp.EndDate BETWEEN so.Contract_NetStartDate AND so.Contract_NetEndDate)