| Author |
 Topic  |
|
|
Marka
Starting Member
5 Posts |
 Posted - 10/09/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/09/2012 : 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 |
Edited by - sunitabeck on 10/09/2012 07:25:03 |
 |
|
|
Marka
Starting Member
5 Posts |
Posted - 10/09/2012 : 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
|
|
|
|
Marka
Starting Member
5 Posts |
Posted - 10/09/2012 : 07:40:13
|
| Fantastic - works and thanks for the quick response - Much appreciated. |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/09/2012 : 07:43:07
|
| You are welcome .) |
|
|
| |
Topic |
|
|
|
OUTER JOINS ON UDF
