Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • Last Modified:

getting count from left join

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

example data:

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

0
metropia
Asked:
metropia
  • 7
  • 6
2 Solutions
 
PortletPaulCommented:
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)
0
 
metropiaAuthor Commented:
when i add , count(distinct fp.StartDate) to the query, then i get 1 per row.
0
 
metropiaAuthor Commented:
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 :/
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
PortletPaulCommented:
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?
0
 
metropiaAuthor Commented:
0
 
metropiaAuthor Commented:
did i put enough data?
0
 
PortletPaulCommented:
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.
0
 
PortletPaulCommented:
Here are 2 query variants - they produce different results but they should let you choose  the approach you want
SELECT
		so.ItemNumber
	--,	so.ItemDescription AS ItemDescription
	,	so.ContractStart AS ContractStart
	,	so.ContractEnd AS ContractEnd
	,	so.Contract_NetStartDate AS Contract_NetStartDate
	,	so.Contract_NetEndDate AS Contract_NetEndDate
	,	(
          SELECT count(fp.FiscalPeriodKey)
          FROM FiscalPeriod fp
          WHERE
           (fp.StartDate BETWEEN so.Contract_NetStartDate AND so.Contract_NetEndDate) OR
           (fp.EndDate BETWEEN so.Contract_NetStartDate AND so.Contract_NetEndDate)
        )
        AS Contract_NetNumberOfPeriods
	FROM
		SalesOrders so

WHERE itemnumber = 11132 --<< purely as example
;




SELECT
		so.ItemNumber
	--,	so.ItemDescription AS ItemDescription
	,	so.ContractStart AS ContractStart
	,	so.ContractEnd AS ContractEnd
	,	so.Contract_NetStartDate AS Contract_NetStartDate
	,	so.Contract_NetEndDate AS Contract_NetEndDate
	,	count(fp.FiscalPeriodKey) over (partition BY so.ItemNumber) AS Contract_NetNumberOfPeriods
	FROM
		SalesOrders so
	LEFT OUTER JOIN
		FiscalPeriod fp ON
	(fp.StartDate BETWEEN so.Contract_NetStartDate AND so.Contract_NetEndDate) OR
	(fp.EndDate BETWEEN so.Contract_NetStartDate AND so.Contract_NetEndDate)

WHERE itemnumber = 11132--<< purely as example
	

Open in new window

The results are as follows:
| ITEMNUMBER |                CONTRACTSTART |                     CONTRACTEND |          CONTRACT_NETSTARTDATE |         CONTRACT_NETENDDATE | CONTRACT_NETNUMBEROFPERIODS |
|------------|------------------------------|---------------------------------|--------------------------------|-----------------------------|-----------------------------|
|      11132 | April, 06 2013 00:00:00+0000 | December, 31 2014 00:00:00+0000 | January, 01 2014 00:00:00+0000 | June, 01 2014 00:00:00+0000 |                           6 |
		
| ITEMNUMBER |                CONTRACTSTART |                     CONTRACTEND |          CONTRACT_NETSTARTDATE |         CONTRACT_NETENDDATE | CONTRACT_NETNUMBEROFPERIODS |
|------------|------------------------------|---------------------------------|--------------------------------|-----------------------------|-----------------------------|
|      11132 | April, 06 2013 00:00:00+0000 | December, 31 2014 00:00:00+0000 | January, 01 2014 00:00:00+0000 | June, 01 2014 00:00:00+0000 |                           6 |
|      11132 | April, 06 2013 00:00:00+0000 | December, 31 2014 00:00:00+0000 | January, 01 2014 00:00:00+0000 | June, 01 2014 00:00:00+0000 |                           6 |
|      11132 | April, 06 2013 00:00:00+0000 | December, 31 2014 00:00:00+0000 | January, 01 2014 00:00:00+0000 | June, 01 2014 00:00:00+0000 |                           6 |
|      11132 | April, 06 2013 00:00:00+0000 | December, 31 2014 00:00:00+0000 | January, 01 2014 00:00:00+0000 | June, 01 2014 00:00:00+0000 |                           6 |
|      11132 | April, 06 2013 00:00:00+0000 | December, 31 2014 00:00:00+0000 | January, 01 2014 00:00:00+0000 | June, 01 2014 00:00:00+0000 |                           6 |
|      11132 | April, 06 2013 00:00:00+0000 | December, 31 2014 00:00:00+0000 | January, 01 2014 00:00:00+0000 | June, 01 2014 00:00:00+0000 |                           6 |


Partial data only
see:
http://sqlfiddle.com/#!3/6fd66/12

Open in new window

0
 
metropiaAuthor Commented:
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.
0
 
PortletPaulCommented:
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.
0
 
metropiaAuthor Commented:
here is my stored proc.

it contains more fields that the one i originally posted.
mySP.txt
0
 
PortletPaulCommented:
Try the following please, I don't believe you need a group by. Also there is no point in running the correlated subquery and join the fiscal periods table too; the analytic form of count() over() will be much more efficient.
SELECT
-- GROUP
      [t].[ItemNumber]
    , [t].[ItemDescription]
    , [t].[ItemCategoryCode]
    , [t].[ItemFamilyGroupCode]
    , [t].[ItemFamilyTypeCode]
    , [t].[ItemSweetenerGroupCode]
    , [t].[ItemProductGroupCode]
    , [t].[ItemDefaultProductionFacility]
-- GROSS DATES
    , [t].[ContractStart]
    , [t].[ContractEnd]
-- NET DATES
    , [t].[Contract_NetStartDate]
    , [t].[Contract_NetEndDate]
    , [t].[FiscalPeriodStartDate]
    , [t].[FiscalPeriodEndDate]
    , [t].[Contract_NetNumberOfPeriods]
    , [t].[Contract_FiscalPeriodCode]
-- DATA AGGREGATION FIELDS	
    , ISNULL([t].[LineOpenQuantity] / NULLIF([t].[Contract_NetNumberOfPeriods], 0), 0) AS [LineOpenQuantity_Prorated]
FROM (
            SELECT
            -- GROUP
                  bsot.ItemNumber                    AS [ItemNumber]
                , bsot.ItemDescription               AS [ItemDescription]
                , bsot.ItemCategoryCode              AS [ItemCategoryCode]
                , bsot.ItemFamilyGroupCode           AS [ItemFamilyGroupCode]
                , bsot.ItemFamilyTypeCode            AS [ItemFamilyTypeCode]
                , bsot.ItemSweetenerGroupCode        AS [ItemSweetenerGroupCode]
                , bsot.ItemProductGroupCode          AS [ItemProductGroupCode]
                , bsot.ItemDefaultProductionFacility AS [ItemDefaultProductionFacility]
            -- GROSS DATES
                , bsot.ContractStart                 AS [ContractStart]
                , bsot.ContractEnd                   AS [ContractEnd]
            -- NET DATES
                , bsot.Contract_NetStartDate         AS [Contract_NetStartDate]
                , bsot.Contract_NetEndDate           AS [Contract_NetEndDate]
                , fp.StartDate                       AS [FiscalPeriodStartDate]
                , fp.EndDate                         AS [FiscalPeriodEndDate]
             	, count(fp.FiscalPeriodKey) over (partition BY so.ItemNumber)
                                                     AS Contract_NetNumberOfPeriods
                , fp.FiscalPeriodCode                AS [Contract_FiscalPeriodCode]
            -- DATA AGGREGATION FIELDS
                , bsot.LineOpenQuantity              AS [LineOpenQuantity]
            FROM @BlanketSalesOrdersTable bsot
                  LEFT OUTER JOIN DW.dim.FiscalPeriod fp
                        ON (fp.StartDate BETWEEN bsot.Contract_NetStartDate AND bsot.Contract_NetEndDate)
                              OR (fp.EndDate BETWEEN bsot.Contract_NetStartDate AND bsot.Contract_NetEndDate)
      ) t

Open in new window

except for the count of periods this query should give the same results as the one you provided as mySP.txt
0
 
metropiaAuthor Commented:
Thank you very much.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now