Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

getting count from left join

Posted on 2014-03-25
13
Medium Priority
?
344 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
Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

 
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 2000 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 2000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

715 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