June 30, 2005 at 11:30 am
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
  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]
  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]
  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]
  WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_RODetails_RepairOrderID] FOREIGN KEY
(
[RepairOrderID]
  REFERENCES [RepairOrders] (
[RepairOrderID]
 
) ON [PRIMARY]
GO
June 30, 2005 at 11:56 am
We can't help you without sample data.
What results are you getting from this query. What results should you be getting?
June 30, 2005 at 12:12 pm
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.
June 30, 2005 at 12:25 pm
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.
June 30, 2005 at 2:40 pm
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.
June 30, 2005 at 2:47 pm
This will kill the performance if not written right... Can we see the code?
July 1, 2005 at 7:41 am
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
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply