SQL Server 7.0
If I run the following in Query Analyzer I get no records returned:
exec GetLeadsOutcome _Dealer '1/1/2003','12/2/2003',10, '176, 183'
If, however, I run either :
exec GetLeadsOutcome _Dealer '1/1/2003','12/2/2003',10, '176'
or
exec GetLeadsOutcome _Dealer '1/1/2003','12/2/2003',10, '183'
I get a single record returned in each case (which is what I would
expect).
The SQL for GetLeadsOutcome _Dealer is:
CREATE PROCEDURE GetLeadsOutcome _Dealer
@FromDate smalldatetime,
@ToDate smalldatetime,
@OutcomeTypeID integer,
@DealerCode varchar(8000)
AS
DECLARE @TotalLeads integer
BEGIN
SELECT @TotalLeads=COU NT(fldLeadID) FROM tblLeads WHERE
(tblLeads.fldDe alerID IS NOT NULL)
SELECT DISTINCT (dbo.tblDealers .fldDealerName) ,
COUNT(dbo.tblLe ads.fldLeadID) as LeadCount,@Tota lLeads AS TotalLeads
FROM tblLeads LEFT OUTER JOIN
tblOutcome ON tblLeads.fldOut comeID =
tblOutcome.fldO utcomeID RIGHT OUTER JOIN
tblDealers RIGHT OUTER JOIN
tblRegion ON tblDealers.fldR egionID =
tblRegion.fldRe gionID ON tblLeads.fldDea lerID = tblDealers.fldD ealerID
WHERE (((dbo.tblLeads .fldEntered) BETWEEN @FromDate And @ToDate)) AND
tblOutcome.fldO utcomeID=@Outco meTypeID AND
(dbo.tblDealers .fldDealerCode in (@DealerCode))
GROUP BY dbo.tblDealers. fldDealerName
ORDER BY dbo.tblDealers. fldDealerName
END
GO
However, if I open EM, open a table in query view, and paste this into
the SQL window,
SELECT DISTINCT tblDealers.fldD ealerName, COUNT(tblLeads. fldLeadID) AS
LeadCount
FROM tblLeads LEFT OUTER JOIN
tblOutcome ON tblLeads.fldOut comeID =
tblOutcome.fldO utcomeID RIGHT OUTER JOIN
tblDealers RIGHT OUTER JOIN
tblRegion ON tblDealers.fldR egionID =
tblRegion.fldRe gionID ON tblLeads.fldDea lerID = tblDealers.fldD ealerID
WHERE (tblLeads.fldEn tered BETWEEN '1/1/2003' AND '12/2/2003') AND
(tblOutcome.fld OutcomeID = 10) AND (tblDealers.fld DealerCode IN (176,
183))
GROUP BY tblDealers.fldD ealerName
ORDER BY tblDealers.fldD ealerName
I get two records returned.
What is happening?
TIA
Edward
TABLE DEFS:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblLeads_tbl Dealers]') and OBJECTPROPERTY( id,
N'IsForeignKey' ) = 1)
ALTER TABLE [dbo].[tblLeads] DROP CONSTRAINT FK_tblLeads_tbl Dealers
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblSystemUse rs_tblDealers]') and
OBJECTPROPERTY( id, N'IsForeignKey' ) = 1)
ALTER TABLE [dbo].[tblSystemUsers] DROP CONSTRAINT
FK_tblSystemUse rs_tblDealers
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblLeadNotes _tblLeads]') and OBJECTPROPERTY( id,
N'IsForeignKey' ) = 1)
ALTER TABLE [dbo].[tblLeadNotes] DROP CONSTRAINT
FK_tblLeadNotes _tblLeads
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblDealers_t blRegion]') and OBJECTPROPERTY( id,
N'IsForeignKey' ) = 1)
ALTER TABLE [dbo].[tblDealers] DROP CONSTRAINT FK_tblDealers_t blRegion
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblDealers]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
drop table [dbo].[tblDealers]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblLeads]') and OBJECTPROPERTY( id, N'IsUserTable')
= 1)
drop table [dbo].[tblLeads]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblOutcome]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
drop table [dbo].[tblOutcome]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblRegion]') and OBJECTPROPERTY( id, N'IsUserTable')
= 1)
drop table [dbo].[tblRegion]
GO
CREATE TABLE [dbo].[tblDealers] (
[fldDealerID] [int] IDENTITY (1, 1) NOT NULL ,
[fldDealerCode] [varchar] (50) NOT NULL ,
[fldDealerName] [varchar] (50) NULL ,
[fldDealerTel] [varchar] (20) NULL ,
[fldDealerEmail] [varchar] (100) NULL ,
[fldDealerContac t] [varchar] (50) NULL ,
[fldRegionID] [int] NULL ,
[fldDealerActive] [smallint] NOT NULL ,
[fldHeadOffice] [smallint] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblLeads] (
[fldLeadID] [int] IDENTITY (1, 1) NOT NULL ,
[fldAccountNo] [varchar] (50) NULL ,
[fldDealerID] [int] NULL ,
[fldStatusID] [int] NOT NULL ,
[fldOutcomeID] [int] NOT NULL ,
[fldContacted] [smallint] NOT NULL ,
[fldDateContacte d] [smalldatetime] NULL ,
[fldAppointment] [smallint] NULL ,
[fldShowRoom] [smallint] NOT NULL ,
[fldTestDrive] [smallint] NOT NULL ,
[fldSalesPersonI D] [int] NULL ,
[fldCustomerName] [varchar] (50) NULL ,
[fldCHouseNo] [varchar] (50) NULL ,
[fldCStreet] [varchar] (50) NULL ,
[fldCDistrict] [varchar] (50) NULL ,
[fldCTown] [varchar] (40) NULL ,
[fldCCounty] [varchar] (50) NULL ,
[fldCPostcode] [varchar] (50) NULL ,
[fldPhoneInd] [varchar] (20) NULL ,
[fldCTel] [varchar] (50) NULL ,
[fldNewBusinessD ate] [smalldatetime] NULL ,
[fldAgreementTyp e] [varchar] (50) NULL ,
[fldCashPrice] [smallmoney] NULL ,
[fldBalanceFin] [smallmoney] NULL ,
[fldCustRate] [varchar] (10) NULL ,
[fldOrigTerm] [int] NULL ,
[fldPPPType] [varchar] (10) NULL ,
[fldBalloonValue] [smallmoney] NULL ,
[fldMonthlyInsta l] [smallmoney] NULL ,
[fldRegNo] [varchar] (10) NULL ,
[fldRegDate] [smalldatetime] NULL ,
[fldModel] [varchar] (50) NULL ,
[fldDescription] [varchar] (50) NULL ,
[fldTheoPIFDate] [smalldatetime] NULL ,
[fldMonthsToGo] [int] NULL ,
[fldBalanceOS] [smallmoney] NULL ,
[fldLeadPrinted] [smallint] NULL ,
[fldMergeFile] [varchar] (255) NULL ,
[fldTPS] [varchar] (10) NULL ,
[fldMPS] [varchar] (10) NULL ,
[fldUpdated] [smalldatetime] NULL ,
[fldUpdatedBy] [int] NULL ,
[fldEntered] [smalldatetime] NULL ,
[fldReasonLeadNo tProgressed] [varchar] (5000) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblOutcome] (
[fldOutcomeID] [int] IDENTITY (1, 1) NOT NULL ,
[fldOutcomeCode] [int] NULL ,
[fldOutcome] [varchar] (100) NULL ,
[fldConvertedSal es] [smallint] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblRegion] (
[fldRegionID] [int] IDENTITY (1, 1) NOT NULL ,
[fldRegionCode] [varchar] (10) NULL ,
[fldRegion] [varchar] (50) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblDealers] WITH NOCHECK ADD
CONSTRAINT [PK_tblDealers] PRIMARY KEY CLUSTERED
(
[fldDealerID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblLeads] WITH NOCHECK ADD
CONSTRAINT [PK_tblLeads] PRIMARY KEY CLUSTERED
(
[fldLeadID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblOutcome] WITH NOCHECK ADD
CONSTRAINT [PK_tblOutcome] PRIMARY KEY CLUSTERED
(
[fldOutcomeID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblRegion] WITH NOCHECK ADD
CONSTRAINT [PK_tblArea] PRIMARY KEY CLUSTERED
(
[fldRegionID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblDealers] WITH NOCHECK ADD
CONSTRAINT [DF_tblDealers_f ldActive] DEFAULT ((-1)) FOR
[fldDealerActive],
CONSTRAINT [DF_tblDealers_f ldHeadOffice] DEFAULT (0) FOR
[fldHeadOffice],
CONSTRAINT [IX_tblDealers] UNIQUE NONCLUSTERED
(
[fldDealerCode]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblLeads] WITH NOCHECK ADD
CONSTRAINT [DF_tblLeads_fld OutcomeID] DEFAULT (10) FOR
[fldOutcomeID],
CONSTRAINT [DF_tblLeads_fld Contacted] DEFAULT (0) FOR [fldContacted],
CONSTRAINT [DF_tblLeads_fld Appointment] DEFAULT (0) FOR
[fldAppointment],
CONSTRAINT [DF_tblLeads_fld ShowRoom] DEFAULT (0) FOR [fldShowRoom],
CONSTRAINT [DF_tblLeads_fld TestDrive] DEFAULT (0) FOR [fldTestDrive],
CONSTRAINT [DF_tblLeads_fld LeadPrinted] DEFAULT (0) FOR
[fldLeadPrinted],
CONSTRAINT [DF_tblLeads_fld TPSMatch] DEFAULT ('NO') FOR [fldTPS],
CONSTRAINT [DF_tblLeads_fld MPSMatch] DEFAULT ('NO') FOR [fldMPS]
GO
ALTER TABLE [dbo].[tblOutcome] WITH NOCHECK ADD
CONSTRAINT [DF_tblOutcome_f ldConvertedSale s] DEFAULT (0) FOR
[fldConvertedSal es]
GO
ALTER TABLE [dbo].[tblRegion] WITH NOCHECK ADD
CONSTRAINT [IX_tblRegion] UNIQUE NONCLUSTERED
(
[fldRegionCode]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblDealers] ADD
CONSTRAINT [FK_tblDealers_t blRegion] FOREIGN KEY
(
[fldRegionID]
) REFERENCES [dbo].[tblRegion] (
[fldRegionID]
)
GO
ALTER TABLE [dbo].[tblLeads] ADD
CONSTRAINT [FK_tblLeads_tbl Dealers] FOREIGN KEY
(
[fldDealerID]
) REFERENCES [dbo].[tblDealers] (
[fldDealerID]
)
GO
alter table [dbo].[tblLeads] nocheck constraint
[FK_tblLeads_tbl Dealers]
GO
