Having trouble with an inner join

Question

Post reply

Having trouble with an inner join

Scott Bernardi

SSChasing Mays

Points: 630
More actions
November 21, 2006 at 1:29 pm

#114751

I'm having some difficulty with an inner join. It's part of a billing routine
where I am seeing which orders have items that are subject to rental charges.
Items become subject to rental charges when they have been in the field longer
than an initial rental period (like 30 days or 90 days), and then every 30
days thereafter.
When a rent is charged, the order record is updated with the last rent date.
Here the tables, slighty pared down:
CREATE TABLE [dbo].[Orders] (
[ORDER_ID] [int] IDENTITY (150, 1) NOT NULL ,
[CUSTID] [int] NULL ,
[ORDER_DATE] [datetime] NULL ,
[CURR_STATUS] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BILL_DATE] [datetime] NULL ,
[LOCKED] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Orders_LOCKED] DEFAULT (N'N'),
[ACTIVE_STATUS] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Orders_ACTIVE_STATUS] DEFAULT (N'A'),
[LOC_TYPE] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOC_ADDR1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOC_ADDR2] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOC_CITY] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOC_STATE] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOC_ZIP] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[REQUEST_DATE] [datetime] NULL ,
[INSTALL_DATE] [datetime] NULL ,
[SCHED_INSTALL_DATE] [datetime] NULL ,
[PICKUP_DATE] [datetime] NULL ,
[LAST_UPDATED] [datetime] NULL ,
[SCHED_PICKUP_DATE] [datetime] NULL ,
[LAST_RENT_DATE] [datetime] NULL ,
[SUBMITTED] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Orders_SUBMITTED] DEFAULT (N'N'),
[ISTEST] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Orders_ISTEST] DEFAULT ('N'),
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[ORDER_ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[OrderItem] (
[ITEM_ID] [int] IDENTITY (1, 1) NOT NULL ,
[ORDER_ID] [int] NOT NULL ,
[CUSTID] [int] NULL ,
[PRODUCT_CODE] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SUB_CODE] [int] NULL CONSTRAINT [DF_OrderItem_SUB_CODE] DEFAULT (0),
[ITEM_DATE] [datetime] NULL ,
[PRICE] [money] NULL CONSTRAINT [DF_OrderItem_PRICE] DEFAULT (0),
[PLACEMENT_DATE] [datetime] NULL ,
[PICKUP_DATE] [datetime] NULL ,
[QTY] [int] NULL CONSTRAINT [DF_OrderItem_QTY] DEFAULT (1),
[INSTRUCTIONS] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NOTES] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UNIT_PRICE] [money] NULL CONSTRAINT [DF_OrderItem_UNIT_PRICE] DEFAULT (0),
[SCHED_PLACE_DATE] [datetime] NULL ,
[SCHED_PICK_DATE] [datetime] NULL ,
[BILL_DATE] [datetime] NULL ,
[LAST_UPDATED] [datetime] NULL ,
CONSTRAINT [PK_OrderItem] PRIMARY KEY CLUSTERED
(
[ITEM_ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Here's the query:
Typical values for parameters are
@billDate '10/05/2006'
@hiDate '10/01/2006' -- upper range to consider. Ie, look at everything
through 09/30/06
@period -30 (initial rental period, passed as negative)
CREATE PROCEDURE dbo.AddRentsQueryCust
@billDate DATETIME,
@hiDate DATETIME,
@period INT,
@custId INT
AS
SELECT o.ORDER_ID, o.CUSTID, o.ORDER_DATE, o.BILL_DATE, o.ACTIVE_STATUS, o.INSTALL_DATE, o.PICKUP_DATE, o.LOC_ADDR1, o.LOC_CITY, o.LAST_RENT_DATE,
DateAdd(d, (CASE WHEN o.LAST_RENT_DATE IS NULL THEN -1*@period ELSE 30 END), COALESCE(o.LAST_RENT_DATE, o.INSTALL_DATE)) as NewRentDate, *
FROM Orders o INNER JOIN ItemsRentEligible(@billDate, @hiDate, @period, @custId) RI ON o.ORDER_ID = RI.ORDER_ID
WHERE o.ACTIVE_STATUS='A' and o.INSTALL_DATE<@hiDate and o.ISTEST<>'Y'
and (o.LAST_RENT_DATE IS NULL or o.LAST_RENT_DATE<=DateAdd(dd, -30, @hiDate))
AND o.CUSTID=@custId;
GO
And here's the code for ItemRentsEligible:
CREATE FUNCTION [dbo].[ItemsRentEligible]
(@billDate DATETIME, @hiDate DATETIME, @period INT, @custId INT)
RETURNS TABLE AS
RETURN(
SELECT OrderItem.ORDER_ID, OrderItem.PRODUCT_CODE, OrderItem.PLACEMENT_DATE, OrderItem.PICKUP_DATE, OrderItem.CUSTID, Products.RENT_ELIG, OrderItem.QTY, Products.TAXABLE, OrderItem.PRICE, OrderItem.BILL_DATE, Products.PRODUCT_DESC, PRODUCTS.RENT_CHG
FROM OrderItem INNER JOIN Products ON OrderItem.PRODUCT_CODE = Products.PRODUCT_CODE
WHERE OrderItem.PLACEMENT_DATE Is Not Null AND (OrderItem.PICKUP_DATE Is Null or OrderItem.PICKUP_DATE>@hiDate) AND Products.RENT_ELIG='Y' AND OrderItem.PLACEMENT_DATE<=DateAdd(dd, @period, @hiDate)
AND CUSTID=@custId
)
The query above works as shown, but I realized I need to make a change - the date
@hiDate being passed to ItemsRentEligible needs to be the computed "next rental date" .
If you look at the computed column NewRentDate, that's the value I need to have
acted upon by ItemsRentEligible, but the table valued UDF won't take anything with
dynamic or column values as a parameter.
I'm guessing that the query in ItemsRentEligible could be incorporated into the
AddRentsQueryCust stored procedure without a UDF, but I'm not sure how to get the join to work.
I know this is pretty long, but I've seen you guys work miracles on stuff just as complicated. Any suggestions?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Try this for your udf:

CREATE FUNCTION [dbo].[ItemsRentEligible] (

@billDate DATETIME,

@hiDate DATETIME,

@period INT,

@custId INT)

RETURNS TABLE AS

RETURN(

SELECT

OrderItem.ORDER_ID,

OrderItem.PRODUCT_CODE,

OrderItem.PLACEMENT_DATE,

OrderItem.PICKUP_DATE,

OrderItem.CUSTID,

Products.RENT_ELIG,

OrderItem.QTY,

Products.TAXABLE,

OrderItem.PRICE,

OrderItem.BILL_DATE,

Products.PRODUCT_DESC,

PRODUCTS.RENT_CHG

FROM

dbo.Orders

inner join dbo.OrderItem

on (Orders.ORDER_ID = OrderItem.ORDER_ID)

INNER JOIN dbo.Products

ON (OrderItem.PRODUCT_CODE = Products.PRODUCT_CODE)

WHERE

OrderItem.PLACEMENT_DATE Is Not Null

AND (OrderItem.PICKUP_DATE Is Null or OrderItem.PICKUP_DATE > DateAdd(d, (CASE WHEN o.LAST_RENT_DATE IS NULL THEN -1 * @period ELSE 30 END),

COALESCE(o.LAST_RENT_DATE, o.INSTALL_DATE))) -- @hiDate)

AND Products.RENT_ELIG = 'Y'

AND OrderItem.PLACEMENT_DATE <= DateAdd(dd, @period, @hiDate)

AND CUSTID = @custId

)

John Beggs SSCommitted Points: 1921 More actions · Answer 2

I don't know the full use of your UDF, so I don't know if it is safe to make a change to the UDF itself. If it is safe, I would tend to stay away from using a UDF that went back to the same table when you could do the same thing w/o it in normal set based query.

Consider this version of the procedure. I think it will get you pretty close, but with out having full structures and data here, it may need tweeking.

Good Luck!

CREATE PROCEDURE dbo.AddRentsQueryCust

@billDate DATETIME,

@hiDate DATETIME,

@period INT,

@custId INT

AS

SELECT o.ORDER_ID, o.CUSTID, o.ORDER_DATE, o.BILL_DATE, o.ACTIVE_STATUS, o.INSTALL_DATE, o.PICKUP_DATE, o.LOC_ADDR1, o.LOC_CITY, o.LAST_RENT_DATE,

DateAdd(d, (CASE WHEN o.LAST_RENT_DATE IS NULL THEN -1*@period ELSE 30 END), COALESCE(o.LAST_RENT_DATE, o.INSTALL_DATE)) as NewRentDate, *

FROM Orders o

INNER JOIN OrderItem oi

ON o.ORDER_ID = oi.ORDER_ID

AND oi.PLACEMENT_DATE<=DateAdd(d, (CASE WHEN o.LAST_RENT_DATE IS NULL THEN -1*@period ELSE 30 END), COALESCE(o.LAST_RENT_DATE, o.INSTALL_DATE))

AND (oi.PICKUP_DATE Is Null or oi.PICKUP_DATE > DateAdd(d, (CASE WHEN o.LAST_RENT_DATE IS NULL THEN -1*@period ELSE 30 END), COALESCE(o.LAST_RENT_DATE, o.INSTALL_DATE)))

INNER JOIN Products p

ON oi.PRODUCT_CODE = p.PRODUCT_CODE

AND p.RENT_ELIG='Y'

WHERE o.ACTIVE_STATUS='A' and o.INSTALL_DATE<@hiDate and o.ISTEST<>'Y'

and (o.LAST_RENT_DATE IS NULL or o.LAST_RENT_DATE<=DateAdd(dd, -30, @hiDate))

AND o.CUSTID=@custId;

GO

Scott Bernardi SSChasing Mays Points: 630 More actions · Answer 3

Hey John, thanks - I had to make a couple of minor tweaks, but your method worked. That's just what I was looking for! Thanks again.