asked on

getting count from left join

I need to get the the count of the number of records that the left join returns.

example data:

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.

	SELECT 
		so.ItemNumber
	,	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
	FROM 
		@SalesOrders so
	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)
	GROUP BY
		so.ItemNumber
	,	fp.FiscalPeriodCode

Open in new window

PortletPaul

Mmmm, I'm not sure this query will be good

When you join those 2 tables 1 record in @SalesOrders will be multiplied by the number of periods that the contract dates span.

Therefore this sum will not be accurate:
, SUM(so.LineOpenQuantity_Prorated) AS LineOpenQuantity_Prorated
as it will be multiplied by the number of rows concerned. Only if that number of rows is 1 would this be ok.

Why not correct the count of fiscal periods?

try:

, count(distinct fp.StartDate)

metropia

ASKER

when i add , count(distinct fp.StartDate) to the query, then i get 1 per row.

metropia

ASKER

if the number of rows that the join of @SalesOrders to DW.dim.FiscalPeriod is 3, 4, 5, 6, I need that to be my number of perdiod for each row

in the screen shot i showed, the number instead of the 4 should be 3 because that is the number of records that found match. i am not concerned about the quantity multiplying 3 times, or what ever many per row, right now i am just trying to get all the data and the number of periods is what is messing me up :/

PortletPaul

Sorry, yes if you count distinct into that query you would get one. My bad.

Can you provide sample data for the 2 tables invloved?

metropia

ASKER

thank you Paul
table-so-sample-data.xlsx
table-fiscal-period-sample-data.xlsx

metropia

ASKER

did i put enough data?

PortletPaul

yes, plenty thanks. I'll be busy for a few hours I'm afraid but others might join in. Shouldn't be too hard now with data to play with.

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

metropia

ASKER

Thank you Paul.

I have a question, which would be your preferred approach?

I want to try the example of sub-query but I get an error message when I input the sub-query into my select

Msg 8120, Level 16, State 1, Procedure SalesForecast_Pivot, Line 319
Column '@SalesOrders.Contract_NetStartDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Procedure SalesForecast_Pivot, Line 319
Column '@SalesOrders.Contract_NetEndDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Procedure SalesForecast_Pivot, Line 320
Column '@SalesOrders.Contract_NetStartDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Procedure SalesForecast_Pivot, Line 320
Column '@SalesOrders.Contract_NetEndDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

PortletPaul

The subquery wasn't designed for the group by, and I'm not sure you need it either. idi you see the results above?

Using the subquery was suggested if you wanted one row but with the count.

Otherwise I would use the analytic COUNT() OVER () and I don't believe you need the group by.

If you are still having trouble provide the actual query you are using.

metropia

ASKER

here is my stored proc.

it contains more fields that the one i originally posted.
mySP.txt

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

metropia

ASKER

Thank you very much.