Solved

getting count from left join

Posted on 2014-03-25
13
343 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 49

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
Cloud Training Guides

FREE GUIDES: In-depth and hand-crafted Linux, AWS, OpenStack, DevOps, Azure, and Cloud training guides created by Linux Academy instructors and the community.

 
LVL 49

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: 39954862
did i put enough data?
0
 
LVL 49

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 49

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 49

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 49

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

617 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