September 18, 2009 at 5:56 am
I'm trying to perform a count of the number of records created within a specifed date range (every six months) from the two tables shown using the following script, the only problem is i'm falling short in the resultset. If i amend date on the script to capture all records created since 01-01-1900 the qty returned is 40,818 but doing a search via the front end the result is 46,453 i'm know i'm missing something obvious but can't see it!!
Any help or advice would be most welcomed
SELECT officename AS 'Office' ,count(*) AS 'Count'
FROM contact c LEFT JOIN clientSupplier cs ON c.contactid = CS.Contactid
LEFT JOIN office O ON ((C.officeId = O.OfficeId) OR (CS.OfficeId = O.OfficeId))
WHERE ((c.dateCreated BETWEEN '2005-07-01' AND '2005-12-31') OR ((CS.dateCreated BETWEEN '2005-07-01' AND '2005-12-31') AND CS.ClientSupplierType IN (1,2,3) AND ((CS.Closed IS NULL) OR CS.Closed IS NOT NULL)))
GROUP BY officeName
ORDER BY officeName
/****** Object: Table [dbo].[Contact] Script Date: 09/18/2009 12:40:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Contact](
[ContactID] [int] IDENTITY(1,1) NOT NULL,
[ContactTypeID] [int] NOT NULL,
[Pref] [varchar](50) NULL,
[FName] [varchar](50) NULL,
[LName] [varchar](60) NULL,
[Tel] [varchar](120) NULL,
[Fax] [varchar](120) NULL,
[Mobile] [varchar](120) NULL,
[Address1] [varchar](50) NULL,
[Address2] [nvarchar](50) NULL,
[Address3] [varchar](50) NULL,
[Town] [varchar](50) NULL,
[County] [varchar](50) NULL,
[PostCode] [varchar](50) NULL,
[Country] [varchar](50) NULL,
[EMail] [varchar](120) NULL,
[Notes] [varchar](1000) NULL,
[MasterMailshot] [bit] NOT NULL CONSTRAINT [DF_Contact_MasterMailshot] DEFAULT ((-1)),
[CurrentMailshot] [bit] NOT NULL CONSTRAINT [DF_Contact_CurrentMailshot] DEFAULT ((-1)),
[Salutation] [nvarchar](50) NULL,
[CDSCode] [varchar](10) NULL,
[CreatedBy] [int] NULL,
[DateCreated] [smalldatetime] NULL,
[Mname] [varchar](50) NULL,
[Suff] [varchar](50) NULL,
[Sex] [varchar](1) NULL,
[DateOfBirth] [smalldatetime] NULL,
[BirthPlace] [varchar](50) NULL,
[ContactLastModified] [datetime] NULL DEFAULT (getdate()),
[DepartmentId] [int] NULL,
[OfficeId] [int] NULL,
[RespNotes] [varchar](1000) NULL,
[DateOfDeath] [smalldatetime] NULL,
[ExternalUse] [int] NOT NULL DEFAULT (0),
[CompanyId] [smallint] NULL,
[zid] [int] NULL,
[MailingName] [varchar](255) NULL,
[Initials] [varchar](50) NULL,
[RowGuid] [uniqueidentifier] ROWGUIDCOL NULL CONSTRAINT [DF_Contact_RowGuid] DEFAULT (newid()),
[NINumber] [varchar](255) NULL,
[UTR] [varchar](10) NULL,
[PPSN] [varchar](10) NULL,
[RestrictedClientTeamAccess] [bit] NOT NULL CONSTRAINT [DF_Contact_RestrictedClientTeamAccess] DEFAULT (0),
[LNameFName] AS (rtrim([LName]) + ' ' + isnull(rtrim([FName]),' ')),
[OurRef] [varchar](255) NULL,
CONSTRAINT [PK_Contact] PRIMARY KEY CLUSTERED
(
[ContactID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Contact] WITH CHECK ADD CONSTRAINT [FK_Contact_Company] FOREIGN KEY([CompanyId])
REFERENCES [dbo].[Company] ([CompanyId])
GO
ALTER TABLE [dbo].[Contact] CHECK CONSTRAINT [FK_Contact_Company]
GO
ALTER TABLE [dbo].[Contact] WITH CHECK ADD CONSTRAINT [FK_Contact_ContactTypeID] FOREIGN KEY([ContactTypeID])
REFERENCES [dbo].[ContactType] ([ContactTypeId])
GO
ALTER TABLE [dbo].[Contact] CHECK CONSTRAINT [FK_Contact_ContactTypeID]
GO
ALTER TABLE [dbo].[Contact] WITH CHECK ADD CONSTRAINT [FK_Contact_Department] FOREIGN KEY([DepartmentId])
REFERENCES [dbo].[Department] ([DepartmentID])
GO
ALTER TABLE [dbo].[Contact] CHECK CONSTRAINT [FK_Contact_Department]
GO
ALTER TABLE [dbo].[Contact] WITH CHECK ADD CONSTRAINT [FK_Contact_Employee] FOREIGN KEY([CreatedBy])
REFERENCES [dbo].[Employee] ([EmployeeID])
GO
ALTER TABLE [dbo].[Contact] CHECK CONSTRAINT [FK_Contact_Employee]
GO
ALTER TABLE [dbo].[Contact] WITH CHECK ADD CONSTRAINT [FK_Contact_Office] FOREIGN KEY([OfficeId])
REFERENCES [dbo].[Office] ([OfficeID])
GO
ALTER TABLE [dbo].[Contact] CHECK CONSTRAINT [FK_Contact_Office]
/****** Object: Table [dbo].[ClientSupplier] Script Date: 09/18/2009 12:41:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ClientSupplier](
[ClientID] [int] IDENTITY(1,1) NOT NULL,
[ContactID] [int] NOT NULL,
[CreatedDate] [smalldatetime] NOT NULL CONSTRAINT [DF_Client_CreatedDate] DEFAULT (getdate()),
[ParentClientID] [int] NULL,
[DefaultRecoveryRate] [float] NOT NULL CONSTRAINT [DF_Client_DefaultRecoveryRate] DEFAULT (1),
[Notes] [varchar](1000) NULL,
[OfficeID] [int] NOT NULL CONSTRAINT [DF_Client_OfficeID] DEFAULT (1),
[DepartmentID] [int] NOT NULL CONSTRAINT [DF_Client_DepartmentID] DEFAULT (1),
[ClientCode] [varchar](50) NOT NULL,
[PeriodEndDate] [smalldatetime] NULL,
[Vattable] [bit] NOT NULL CONSTRAINT [DF_Client_Vattable] DEFAULT (1),
[CliFileID] [int] NOT NULL CONSTRAINT [DF_Client_CliFileID] DEFAULT (0),
[Internal] [bit] NOT NULL CONSTRAINT [DF_Client_Internal] DEFAULT (0),
[ClientVATTypeID] [tinyint] NOT NULL CONSTRAINT [DF_Client_ClientVATTypeID] DEFAULT (1),
[ZID] [int] NULL CONSTRAINT [DF_Client_ZID] DEFAULT (0),
[CreatedBy] [int] NULL,
[DateCreated] [smalldatetime] NULL,
[DefaultCurrency] [int] NULL,
[ClientSupplierType] [tinyint] NOT NULL DEFAULT (1),
[TermsTypeID] [tinyint] NULL,
[TermsDay] [smallint] NULL,
[PaymentTypeID] [smallint] NULL,
[CreditLimit] [money] NULL,
[DefaultNominalID] [smallint] NULL,
[AccountStatusID] [smallint] NULL,
[BankAccountName] [varchar](50) NULL,
[BankAccountNum] [varchar](50) NULL,
[BankSortCode] [varchar](50) NULL,
[BACSRef] [varchar](50) NULL,
[ApprovalEmpID] [int] NULL,
[DefaultContactAssoc] [int] NULL,
[Closed] [smalldatetime] NULL DEFAULT (null),
[CompanyRegistrationNo] [varchar](50) NULL,
[CompanyTaxReference] [varchar](50) NULL,
[RowGuid] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ClientSupplier_RowGuid] DEFAULT (newid()),
CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
(
[ClientID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[ClientSupplier] WITH NOCHECK ADD CONSTRAINT [FK_Client_ClientVATType] FOREIGN KEY([ClientVATTypeID])
REFERENCES [dbo].[ClientVATType] ([ClientVATTypeID])
GO
ALTER TABLE [dbo].[ClientSupplier] CHECK CONSTRAINT [FK_Client_ClientVATType]
GO
ALTER TABLE [dbo].[ClientSupplier] WITH CHECK ADD CONSTRAINT [FK_Client_Contact] FOREIGN KEY([ContactID])
REFERENCES [dbo].[Contact] ([ContactID])
GO
ALTER TABLE [dbo].[ClientSupplier] CHECK CONSTRAINT [FK_Client_Contact]
GO
ALTER TABLE [dbo].[ClientSupplier] WITH CHECK ADD CONSTRAINT [FK_Client_Currency] FOREIGN KEY([DefaultCurrency])
REFERENCES [dbo].[Currency] ([CurrID])
GO
ALTER TABLE [dbo].[ClientSupplier] CHECK CONSTRAINT [FK_Client_Currency]
GO
ALTER TABLE [dbo].[ClientSupplier] WITH CHECK ADD CONSTRAINT [FK_Client_Department] FOREIGN KEY([DepartmentID])
REFERENCES [dbo].[Department] ([DepartmentID])
GO
ALTER TABLE [dbo].[ClientSupplier] CHECK CONSTRAINT [FK_Client_Department]
GO
ALTER TABLE [dbo].[ClientSupplier] WITH CHECK ADD CONSTRAINT [FK_Client_Employee] FOREIGN KEY([CreatedBy])
REFERENCES [dbo].[Employee] ([EmployeeID])
GO
ALTER TABLE [dbo].[ClientSupplier] CHECK CONSTRAINT [FK_Client_Employee]
GO
ALTER TABLE [dbo].[ClientSupplier] WITH CHECK ADD CONSTRAINT [FK_Client_Office] FOREIGN KEY([OfficeID])
REFERENCES [dbo].[Office] ([OfficeID])
GO
ALTER TABLE [dbo].[ClientSupplier] CHECK CONSTRAINT [FK_Client_Office]
GO
ALTER TABLE [dbo].[ClientSupplier] WITH CHECK ADD CONSTRAINT [FK_ClientSupplier_AccountStatus] FOREIGN KEY([AccountStatusID])
REFERENCES [dbo].[AccountStatus] ([AccountStatusID])
GO
ALTER TABLE [dbo].[ClientSupplier] CHECK CONSTRAINT [FK_ClientSupplier_AccountStatus]
GO
ALTER TABLE [dbo].[ClientSupplier] WITH CHECK ADD CONSTRAINT [FK_ClientSupplier_ClientSupplierType] FOREIGN KEY([ClientSupplierType])
REFERENCES [dbo].[ClientSupplierType] ([ClientSupplierTypeID])
GO
ALTER TABLE [dbo].[ClientSupplier] CHECK CONSTRAINT [FK_ClientSupplier_ClientSupplierType]
GO
ALTER TABLE [dbo].[ClientSupplier] WITH CHECK ADD CONSTRAINT [FK_ClientSupplier_Employee] FOREIGN KEY([ApprovalEmpID])
REFERENCES [dbo].[Employee] ([EmployeeID])
GO
ALTER TABLE [dbo].[ClientSupplier] CHECK CONSTRAINT [FK_ClientSupplier_Employee]
GO
ALTER TABLE [dbo].[ClientSupplier] WITH CHECK ADD CONSTRAINT [FK_ClientSupplier_ParentClientID] FOREIGN KEY([ParentClientID])
REFERENCES [dbo].[ClientSupplier] ([ClientID])
GO
ALTER TABLE [dbo].[ClientSupplier] CHECK CONSTRAINT [FK_ClientSupplier_ParentClientID]
GO
ALTER TABLE [dbo].[ClientSupplier] WITH CHECK ADD CONSTRAINT [FK_ClientSupplier_PaymentType] FOREIGN KEY([PaymentTypeID])
REFERENCES [dbo].[PaymentType] ([PaymentTypeID])
GO
ALTER TABLE [dbo].[ClientSupplier] CHECK CONSTRAINT [FK_ClientSupplier_PaymentType]
GO
ALTER TABLE [dbo].[ClientSupplier] WITH CHECK ADD CONSTRAINT [FK_ClientSupplier_TermsType] FOREIGN KEY([TermsTypeID])
REFERENCES [dbo].[TermsType] ([TermsTypeID])
GO
ALTER TABLE [dbo].[ClientSupplier] CHECK CONSTRAINT [FK_ClientSupplier_TermsType]
GO
ALTER TABLE [dbo].[ClientSupplier] WITH CHECK ADD CONSTRAINT [FK_ClientSupplier_VPMNominal] FOREIGN KEY([DefaultNominalID])
REFERENCES [dbo].[VpmNominal] ([NominalId])
GO
ALTER TABLE [dbo].[ClientSupplier] CHECK CONSTRAINT [FK_ClientSupplier_VPMNominal]
GO
ALTER TABLE [dbo].[ClientSupplier] WITH CHECK ADD CONSTRAINT [FK_Contact_Client] FOREIGN KEY([DefaultContactAssoc])
REFERENCES [dbo].[Contact] ([ContactID])
GO
ALTER TABLE [dbo].[ClientSupplier] CHECK CONSTRAINT [FK_Contact_Client]
October 2, 2009 at 3:10 pm
Try this. Note that criteria in your WHERE clause applies to your entire query. Moving it to the left join stops these rows from being filtered.
SELECT officename AS 'Office', COUNT(*) AS 'Count'
FROM contact c
LEFT JOIN clientSupplier cs ON c.contactid = CS.Contactid
AND CS.ClientSupplierType IN (1, 2, 3)
AND CS.dateCreated BETWEEN '2005-07-01' AND '2005-12-31'
LEFT JOIN office O ON C.officeId = O.OfficeId
OR S.OfficeId = O.OfficeId
WHERE c.dateCreated BETWEEN '2005-07-01' AND '2005-12-31'
GROUP BY officeName
ORDER BY officeName
October 5, 2009 at 5:26 am
Many thanks for the heads up Seth, that gives me the result set i was expecting/hoping to see! 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply