What kind of join - Left Join?

Question

Post reply

What kind of join - Left Join?

Kbrown

SSCommitted

Points: 1794
More actions
June 30, 2005 at 11:30 am

#102618

Hello,
Hoping one of you tsql experts can give me some advice. I'm trying to put a query together which will tell me how soon after a vehicle was purchased a customer was in for an oil change. The timeframes are categorized by 0-6 months, after six months, or no oil change.
The way I have the query structured, I have two records coming back for a customer who had an oil change. Due to my left join, I believe it is pulling the record for the match and a NULL record. In that case I only want the match not the NULL. Perhaps I should be doing something other than the left join?
The table which stores a purchase is fiwip. The table which stores oil change is repairorders. I'll list out the query I have written and the create on the two tables FYI.
Thanks in advance for you help. Kim
The Query:
select dealnumber,neworused,dealstore,min(ro.closedate), --min(closedate)
CASE WHEN datediff(mm,min(ro.closedate),fiwip.contractdate4) <= 6
THEN '6 months'
ELSE
CASE WHEN datediff (mm,min(ro.closedate),fiwip.contractdate4) > 6
  THEN '> 6 months'
  ELSE 'No Oil Change'
END
END
from
(select fiwip.dealnumber1 as dealnumber,
fiwip.neworused52 as neworused,
fiwip.storeid as dealstore,
ro.storeid as repairstore,
ro.ronumber as ronumber,
ro.closedate
from fiwip
left join repairorders ro on fiwip.customerid = ro.customerid
  and fiwip.vehicleid = ro.vehicleid
  and fiwip.vin41 = ro.vin
  and ro.closedate > fiwip.contractdate4
left join rodetail on ro.vin=rodetail.vin
  and ro.ronumber=rodetail.ronumber
  and rodetail.opcode in ('ELOF','ELOFDSL','HLOF','ESOCP1495','ESOCP1995')
where neworused52 in ('NEW','USED')
and contractdate4 >='05/01/2005'
and fiwip.dealtype in ('C','E','L','R','F')
and dealnumber1=6229
group by fiwip.dealnumber1,fiwip.neworused52,fiwip.storeid,ro.storeid,ro.ronumber,ro.closedate
&nbsp as roInfo
inner join fiwip on roInfo.dealnumber = fiwip.dealnumber1
left join repairorders ro on ro.vin=fiwip.vin41
and ro.vehicleid = fiwip.vehicleid
and ro.customerid=fiwip.customerid
group by dealnumber,neworused,dealstore,fiwip.contractdate4
order by dealnumber
The results:
6229 Used 3 NULL No Oil Change
6229 Used 3 2005-05-09 00:00:00 6 months
(truncated for simplicity)
CREATE TABLE [FIWIP] (
[DealNumber1] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SALESPERSON] [int] NOT NULL ,
[ADPSalesperson5] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SALESPERSON2] [int] NOT NULL ,
[ADPSalesperson2158] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SalesManager3402] [int] NULL ,
[FIManager3401] [int] NULL ,
[ClosingManager3403] [int] NULL ,
  [CUSTOMERID] [int] NOT NULL ,
[ADPCustomerID7] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustomerName8] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [VEHICLEID] [int] NOT NULL ,
[StockNumber33] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NeworUsed52] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [ContractDate4] [smalldatetime] NULL ,
  [StoreID] [int] NOT NULL ,
  [DealType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [dealtypesource] [bit] NOT NULL CONSTRAINT [DF_FIWIP_dealtypesource] DEFAULT (0),
CONSTRAINT [PK_FIWIP] PRIMARY KEY CLUSTERED
([DealNumber1],[StoreID]
&nbsp WITH FILLFACTOR = 90 ON [PRIMARY] ,
  CONSTRAINT [FK_FIWIP_VEHICLE] FOREIGN KEY
([VEHICLEID]) REFERENCES [VEHICLE] ([pkid])
) ON [PRIMARY]
GO
CREATE TABLE [RepairOrders] (
[customerid] [int] NOT NULL ,
[vehicleid] [int] NOT NULL ,
[VIN] [varchar] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RONUMBER] [decimal](10, 0) NOT NULL ,
[OpenDATE] [smalldatetime] NULL ,
[CLOSEDATE] [smalldatetime] NOT NULL ,
[MILEAGE] [int] NOT NULL ,
[StoreID] [int] NOT NULL ,
[RepairOrderID] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [PK_RepairOrders] PRIMARY KEY CLUSTERED
(
  [RepairOrderID]
&nbsp WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [RODetail] (
[VIN] [varchar] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RONUMBER] [decimal](10, 0) NOT NULL ,
[TYPE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Advisor] [int] NOT NULL ,
[Tech] [int] NOT NULL ,
[LABORSALE] [smallmoney] NOT NULL ,
[PARTSALE] [smallmoney] NOT NULL ,
[MISCSALE] [smallmoney] NOT NULL ,
[LineCode] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OpCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OpCodeDesc] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RODetailID] [int] IDENTITY (1, 1) NOT NULL ,
[RepairOrderID] [int] NULL ,
CONSTRAINT [PK_RODetail] PRIMARY KEY CLUSTERED
(
  [RODetailID]
&nbsp WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_RODetails_RepairOrderID] FOREIGN KEY
(
  [RepairOrderID]
&nbsp REFERENCES [RepairOrders] (
  [RepairOrderID]
&nbsp
) ON [PRIMARY]
GO

Viewing 7 posts - 1 through 6 (of 6 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

We can't help you without sample data.

What results are you getting from this query. What results should you be getting?

Kbrown SSCommitted Points: 1794 More actions · Answer 2

Well, so much for trying to be complete in my initial post..thought I covered all the bases. Not sure what you want for sample data...just for me to list it out here in text?

FIWIP:

DealNumber1=6229, Customerid = 246370, neworused=Used, dealtype='R',

vehicleid= 147694, VIN='1G1ND52FX4M576274'

RepairOrders (customerid,vehicleid,vin,ronumber,closedate,mileage,storeid,repairorderid)246370, 147694, '1G1ND52FX4M576274', 196929, 2005-05-09 00:00:00 2005-05-09, 00:00:00 28967 1 424068

246370, 147694, '1G1ND52FX4M576274', 197149, 2005-05-10 00:00:00 2005-05-11 00:00:00 29028, 1, 424551

I had listed the results after the query in my original post:

The results (customerid, neworused,storeid,closedate,type )

6229 Used 3 NULL No Oil Change

6229 Used 3 2005-05-09 00:00:00 6 months

So, What I really want is the second record. THe customer had an oil change on 5/9. I didn't want to see the first record with NULL in the closedate. Is wouldn't be possible to not have an oil change and have an oil change.

Let me know if there is something else I can provide to clarify.

Thanks.

Farrell Keough SSCoach Points: 17414 More actions · Answer 3

I am shooting in the breeze here - you have a lot of table/fields with no data to populate.

You are in fact using an INNER join outside the LEFT join. I have found in the past that this will give you the NULL returns when you have partial data in a table, (i.e., the PK's match, but that is all...).

You may want to SELECT TOP 100 and try playing with the different kinds of joins you have to see what results it returns and that should give you an idea of which table and join configuration is causing the problem.

When in doubt, a sledge hammer will often yield results - even if the results are a splinter wooden stake...

I wasn't born stupid - I had to study.

Kbrown SSCommitted Points: 1794 More actions · Answer 4

I decided to use a user defined function to determine the first oil change. If there wasn't one, it just returns null. I get the right results and the sql looks cleaner.

Thanks.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

This will kill the performance if not written right... Can we see the code?

Kbrown SSCommitted Points: 1794 More actions · Answer 6

I might mention this is for a report which will be pulled by users to check on success of service dept. Run maybe twice a month.

Here is the sql statement:

select neworused52 as neworused,ds.name as dealstore,

CASE WHEN datediff(mm,ro.closedate,fiwip.contractdate4) <= 6

THEN '6 months'

ELSE

CASE WHEN datediff(mm,ro.closedate,fiwip.contractdate4) > 6

THEN '> 6 months'

ELSE 'No Oil Change'

END

END as oiltype,

CASE WHEN datediff(mm,ro.closedate,fiwip.contractdate4) IS NOT NULL

THEN

CASE WHEN rodetail.opcode = 'ELOF' THEN 'Pit Row Service'

ELSE CASE when rodetail.opcode = 'ELOFDSL' THEN 'Pit Row Service'

ELSE 'Other Service'

END

ELSE NULL

END as servicestore

from fiwip

left join repairorders ro on ro.vin=fiwip.vin41

and ro.vehicleid = fiwip.vehicleid

and ro.customerid=fiwip.customerid

and ro.closedate = dbo.fn_GetClosedateByDeal(fiwip.dealnumber1,fiwip.storeid)

left join rodetail on ro.ronumber = rodetail.ronumber

and ro.vin = rodetail.vin

inner join stores ds on fiwip.storeid = ds.pkid

left join stores rs on ro.storeid = rs.pkid

where neworused52 in ('NEW','USED')

and contractdate4 >='06/01/2004'

and fiwip.dealtype in ('C','E','L','R','F') --Commercial,Empl,Lease,Retail,Fleet

Here is the udf:

ALTER FUNCTION dbo.fn_GetClosedateByDeal

(

@dealnumber varchar(25),

@storeid int

)

RETURNS varchar(1000)

AS

BEGIN

DECLARE @closedate varchar(1000)

DECLARE x_cursor CURSOR FOR

select min(ro.closedate)

from fiwip

left join repairorders ro on fiwip.customerid = ro.customerid

and fiwip.vehicleid = ro.vehicleid

and fiwip.vin41 = ro.vin

and ro.closedate > fiwip.contractdate4

inner join rodetail on ro.vin=rodetail.vin

and ro.ronumber=rodetail.ronumber

and rodetail.opcode in ('ELOF','ELOFDSL','HLOF','ESOCP1495','ESOCP1995')

where dealnumber1=@dealnumber

and fiwip.storeid = @storeid

OPEN x_cursor

FETCH NEXT FROM x_cursor

INTO @closedate

RETURN @closedate

END