Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 OUTER JOINS ON UDF

Author  Topic 

Marka
Starting Member

5 Posts
Posted - 2012-10-09 : 05:44:31
Hi

I am having some difficulties creating a data set as I need it and the reason is that an outer join on a table-valued function is not returning all the rows from the UDF.

Basic scenario
I need to create a dataset of total volume of sales by product by month. I still want to see the months where the volume was zero or NULL.

The UDF creates the list of the periods (weeks) between 2 dates but joining the UDF to the sales table only gives me matching records.

Am I missing something about functions? Is there a better way of doing this?

Thanks for the help in advance, much appreciated.


Here is script of function

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GETPERIODS] (@STARTDATE AS DATETIME,@ENDDATE AS DATETIME, @PERIOD AS VARCHAR(10), @INTERVAL AS INT)
RETURNS @DATEPERIODS TABLE (PERIOD VARCHAR(10) NOT NULL)

AS
BEGIN

--Obtain list of required periods
IF @PERIOD = 'MINUTE'
WITH Periods(Date) AS (SELECT @STARTDATE AS Date UNION ALL SELECT CAST(DATEADD(MINUTE, @INTERVAL, [Date]) AS DATETIME) FROM Periods WHERE CAST(DATEADD(MINUTE, 1, [Date]) AS DATETIME) <= @ENDDATE)
INSERT INTO @DATEPERIODS SELECT CONVERT(VARCHAR(5), Date, 114) AS Period FROM Periods OPTION (MAXRECURSION 0)
IF @PERIOD = 'HOUR'
WITH Periods(Date) AS (SELECT @STARTDATE AS Date UNION ALL SELECT CAST(DATEADD(HOUR, @INTERVAL, [Date]) AS DATETIME) FROM Periods WHERE CAST(DATEADD(HOUR, 1, [Date]) AS DATETIME) <= @ENDDATE)
INSERT INTO @DATEPERIODS SELECT CONVERT(VARCHAR(5), Date, 114) AS Period FROM Periods OPTION (MAXRECURSION 0)
IF @PERIOD = 'DAY'
WITH Periods(Date) AS (SELECT @STARTDATE AS Date UNION ALL SELECT CAST(DATEADD(DAY, @INTERVAL, [Date]) AS DATETIME) FROM Periods WHERE CAST(DATEADD(DAY, 1, [Date]) AS DATETIME) <= @ENDDATE)
INSERT INTO @DATEPERIODS SELECT CONVERT(VARCHAR(10), Date, 111) AS Period FROM Periods OPTION (MAXRECURSION 0)
IF @PERIOD = 'WEEK'
WITH Periods(Date) AS (SELECT @STARTDATE AS Date UNION ALL SELECT CAST(DATEADD(WEEK, @INTERVAL, [Date]) AS DATETIME) FROM Periods WHERE CAST(DATEADD(WEEK, 1, [Date]) AS DATETIME) <= @ENDDATE)
INSERT INTO @DATEPERIODS SELECT CONVERT(VARCHAR(4), YEAR(Date)) + RIGHT('00' + CONVERT(VARCHAR(2), DATEPART(WEEK,Date)), 2) AS Period FROM Periods OPTION (MAXRECURSION 0)
IF @PERIOD = 'MONTH'
WITH Periods(Date) AS (SELECT @STARTDATE AS Date UNION ALL SELECT CAST(DATEADD(MONTH, @INTERVAL, [Date]) AS DATETIME) FROM Periods WHERE CAST(DATEADD(MONTH, 1, [Date]) AS DATETIME) <= @ENDDATE)
INSERT INTO @DATEPERIODS SELECT CONVERT(VARCHAR(4), YEAR(Date)) + RIGHT('00' + CONVERT(VARCHAR(2), DATEPART(MONTH,Date)), 2) AS Period FROM Periods OPTION (MAXRECURSION 0)
IF @PERIOD = 'QUARTER'
WITH Periods(Date) AS (SELECT @STARTDATE AS Date UNION ALL SELECT CAST(DATEADD(QUARTER, @INTERVAL, [Date]) AS DATETIME) FROM Periods WHERE CAST(DATEADD(QUARTER, 1, [Date]) AS DATETIME) <= @ENDDATE)
INSERT INTO @DATEPERIODS SELECT CONVERT(VARCHAR(4), YEAR(Date)) + RIGHT('00' + CONVERT(VARCHAR(2), DATEPART(QUARTER,Date)), 2) AS Period FROM Periods OPTION (MAXRECURSION 0)
IF @PERIOD = 'YEAR'
WITH Periods(Date) AS (SELECT @STARTDATE AS Date UNION ALL SELECT CAST(DATEADD(YEAR, @INTERVAL, [Date]) AS DATETIME) FROM Periods WHERE CAST(DATEADD(YEAR, 1, [Date]) AS DATETIME) <= @ENDDATE)
INSERT INTO @DATEPERIODS SELECT CONVERT(VARCHAR(4), YEAR(Date)) AS Period FROM Periods OPTION (MAXRECURSION 0)
RETURN

END
GO


Here is the query trying to run

SELECT TOP (100) PERCENT Periods.Period AS Period, DataTable.ProductClass, DataTable.Weight
FROM (SELECT TOP (100) PERCENT CONVERT(VARCHAR(4), YEAR(ORDERMASTER.Load_From)) + RIGHT('00' + CONVERT(VARCHAR(2),
MONTH(ORDERMASTER.Load_From)), 2) AS Period, PRODUCTCLASS.PRODUCTCLASS AS ProductClass, SUM(ORDERMASTER.Order_Weight)
AS Weight
FROM MD_PLATO.dbo.PF_P2_OM_ORDERMASTER AS ORDERMASTER INNER JOIN
MD_PLATO.dbo.PF_P2_OM_ORDERDETAIL AS ORDERDETAIL ON ORDERMASTER.OrderID = ORDERDETAIL.OrderID INNER JOIN
MD_PLATO.dbo.PRODUCT AS PRODUCT ON ORDERDETAIL.ProductID = PRODUCT.PRODUCTID INNER JOIN
MD_PLATO.dbo.PRODUCTCLASS AS PRODUCTCLASS ON PRODUCT.PRODUCTCLASSID = PRODUCTCLASS.PRODUCTCLASSID
GROUP BY CONVERT(VARCHAR(4), YEAR(ORDERMASTER.Load_From)) + RIGHT('00' + CONVERT(VARCHAR(2), MONTH(ORDERMASTER.Load_From)), 2),
PRODUCTCLASS.PRODUCTCLASS) AS DataTable LEFT OUTER JOIN
(SELECT Period FROM PF_FOUNDATION.dbo.GETPERIODS('2012/01/01', '2013/03/31', 'MONTH', 1) AS Period) AS Periods ON Periods.Period = DataTable.Period
WHERE (DataTable.ProductClass = 'FRESH MILK')
ORDER BY Period, ProductClass

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2012-10-09 : 07:23:18
The way you have written it, you should use a RIGHT OUTER JOIN instead of a LEFT OUTER JOIN. Also, move the filter in the where clause into the JOIN condition. Instead of using the right join, you can swap the order and use a left join as in the query below:
SELECT TOP(100) PERCENT Periods.Period  AS Period,
       DataTable.ProductClass,
       DataTable.Weight
FROM   
		(
                SELECT Period
                FROM   PF_FOUNDATION.dbo.GETPERIODS('2012/01/01', '2013/03/31', 'MONTH', 1) AS 
                       Period
        )  AS Periods  
       LEFT OUTER JOIN
		(
           SELECT TOP(100) PERCENT CONVERT(VARCHAR(4), YEAR(ORDERMASTER.Load_From)) 
                  + RIGHT(
                      '00' + CONVERT(VARCHAR(2), MONTH(ORDERMASTER.Load_From)),
                      2
                  ) AS Period,
                  PRODUCTCLASS.PRODUCTCLASS AS ProductClass,
                  SUM(ORDERMASTER.Order_Weight) AS WEIGHT
           FROM   MD_PLATO.dbo.PF_P2_OM_ORDERMASTER AS ORDERMASTER
                  INNER JOIN MD_PLATO.dbo.PF_P2_OM_ORDERDETAIL AS ORDERDETAIL
                       ON  ORDERMASTER.OrderID = ORDERDETAIL.OrderID
                  INNER JOIN MD_PLATO.dbo.PRODUCT AS PRODUCT
                       ON  ORDERDETAIL.ProductID = PRODUCT.PRODUCTID
                  INNER JOIN MD_PLATO.dbo.PRODUCTCLASS AS PRODUCTCLASS
                       ON  PRODUCT.PRODUCTCLASSID = PRODUCTCLASS.PRODUCTCLASSID
           GROUP BY
                  CONVERT(VARCHAR(4), YEAR(ORDERMASTER.Load_From)) + RIGHT(
                      '00' + CONVERT(VARCHAR(2), MONTH(ORDERMASTER.Load_From)),
                      2
                  ),
                  PRODUCTCLASS.PRODUCTCLASS
       )  AS DataTable
            ON  Periods.Period = DataTable.Period
            AND (DataTable.ProductClass = 'FRESH MILK')
ORDER BY
       Period,
       ProductClass
Go to Top of Page

Marka
Starting Member

5 Posts
Posted - 2012-10-09 : 07:39:08
quote:
Originally posted by sunitabeck

The way you have written it, you should use a RIGHT OUTER JOIN instead of a LEFT OUTER JOIN. Also, move the filter in the where clause into the JOIN condition. Instead of using the right join, you can swap the order and use a left join as in the query below:
SELECT TOP(100) PERCENT Periods.Period  AS Period,
       DataTable.ProductClass,
       DataTable.Weight
FROM   
		(
                SELECT Period
                FROM   PF_FOUNDATION.dbo.GETPERIODS('2012/01/01', '2013/03/31', 'MONTH', 1) AS 
                       Period
        )  AS Periods  
       LEFT OUTER JOIN
		(
           SELECT TOP(100) PERCENT CONVERT(VARCHAR(4), YEAR(ORDERMASTER.Load_From)) 
                  + RIGHT(
                      '00' + CONVERT(VARCHAR(2), MONTH(ORDERMASTER.Load_From)),
                      2
                  ) AS Period,
                  PRODUCTCLASS.PRODUCTCLASS AS ProductClass,
                  SUM(ORDERMASTER.Order_Weight) AS WEIGHT
           FROM   MD_PLATO.dbo.PF_P2_OM_ORDERMASTER AS ORDERMASTER
                  INNER JOIN MD_PLATO.dbo.PF_P2_OM_ORDERDETAIL AS ORDERDETAIL
                       ON  ORDERMASTER.OrderID = ORDERDETAIL.OrderID
                  INNER JOIN MD_PLATO.dbo.PRODUCT AS PRODUCT
                       ON  ORDERDETAIL.ProductID = PRODUCT.PRODUCTID
                  INNER JOIN MD_PLATO.dbo.PRODUCTCLASS AS PRODUCTCLASS
                       ON  PRODUCT.PRODUCTCLASSID = PRODUCTCLASS.PRODUCTCLASSID
           GROUP BY
                  CONVERT(VARCHAR(4), YEAR(ORDERMASTER.Load_From)) + RIGHT(
                      '00' + CONVERT(VARCHAR(2), MONTH(ORDERMASTER.Load_From)),
                      2
                  ),
                  PRODUCTCLASS.PRODUCTCLASS
       )  AS DataTable
            ON  Periods.Period = DataTable.Period
            AND (DataTable.ProductClass = 'FRESH MILK')
ORDER BY
       Period,
       ProductClass

Go to Top of Page

Marka
Starting Member

5 Posts
Posted - 2012-10-09 : 07:40:13
Fantastic - works and thanks for the quick response - Much appreciated.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2012-10-09 : 07:43:07
You are welcome .)
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site