SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 OUTER JOINS ON UDF
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Marka
Starting Member

5 Posts

Posted - 10/09/2012 :  05:44:31  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/09/2012 :  07:23:18  Show Profile  Reply with Quote
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

Edited by - sunitabeck on 10/09/2012 07:25:03
Go to Top of Page

Marka
Starting Member

5 Posts

Posted - 10/09/2012 :  07:39:08  Show Profile  Reply with Quote
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 - 10/09/2012 :  07:40:13  Show Profile  Reply with Quote
Fantastic - works and thanks for the quick response - Much appreciated.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/09/2012 :  07:43:07  Show Profile  Reply with Quote
You are welcome .)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000