Solved

getting count from left join

Posted on 2014-03-25
13
339 Views
Last Modified: 2014-04-02
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
Comment
Question by:metropia
  • 7
  • 6
13 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39954629
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
 

Author Comment

by:metropia
ID: 39954636
when i add , count(distinct fp.StartDate) to the query, then i get 1 per row.
0
 

Author Comment

by:metropia
ID: 39954644
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 39954671
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
 

Author Comment

by:metropia
ID: 39954681
0
 

Author Comment

by:metropia
ID: 39954862
did i put enough data?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39954930
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
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39955103
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
 

Author Comment

by:metropia
ID: 39956167
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39957478
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
 

Author Comment

by:metropia
ID: 39959453
here is my stored proc.

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

Assisted Solution

by:PortletPaul
PortletPaul earned 500 total points
ID: 39960292
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
 

Author Closing Comment

by:metropia
ID: 39972915
Thank you very much.
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question