Solved

getting count from left join

Posted on 2014-03-25
13
337 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
 
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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 combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now