June 4, 2015 at 2:16 pm
I'm using SQL Server 2012 and with the first query I get back 713 records and if I isolate the subquery I get 699 records. What I'm trying to do is return only those records in the main query that are NOT in the subquery results. What am I doing wrong here? I'm getting NO records:
SELECT c.PeopleID
FROM [HSBCP_DB].[dbo].[certs] c with (NOLOCK)
where c.[statusID] = 1 and c.[certificationExpireDate] = '2015-07-31 00:00:00.000'
and NOT exists
(SELECT * FROM [HSBCP_DB].[dbo].[Billing] b with (NOLOCK)
where b.[billYear] = 2015 and b.[billMonth] = 7)
June 4, 2015 at 2:29 pm
Your subquery isn't related to your main query in any way, so if it returns any record it will remove all results from the final result.
June 4, 2015 at 2:34 pm
That subquery is not correlated in any way with the outer query. So long as there is at least 1 row in the Billing table where the year is 2015 and the month is 7, the query will not return any rows.
I'm assuming you actually want to relate the two sets to each other somehow, but without DDL and sample data it's tough to say exactly what change needs to be made.
Most likely there's some column(s) in common between the two tables, and you would want to add to the subquery's WHERE clause a criterion like b.[common column]=c.[common column] for the common columns that identify matching rows.
Also, unless you have amazingly well-tested reasons for using NOLOCK and getting incorrect results or errors don't matter, you might want to consider removing those hints. NOLOCK is very rarely an appropriate choice.
A decent write-up of why is http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx.
Cheers!
EDIT: Heh, I walked away for a bit while typing this up, and when I posted ZZartin had beaten me to the main point. Consider my post a +1 to his 🙂
June 4, 2015 at 2:36 pm
i could write it like this and it returns 52 rows when I need it to return 14 rows:
SELECT c.PeopleID
FROM [HSBCP_DB].[dbo].[certs] c
where c.[statusID] = 1 and c.[certificationExpireDate] = '2015-07-31 00:00:00.000'
and NOT exists
(SELECT * FROM [HSBCP_DB].[dbo].[Billing] b
where b.[billYear] = 2015 and b.[billMonth] = 7 and b.peopleID = c.PeopleID)
June 4, 2015 at 2:40 pm
June 4, 2015 at 2:59 pm
Hope this helps as far as DDL and what the data looks like:
CREATE TABLE [dbo].[Billing](
[indx] [int] IDENTITY(1,1) NOT NULL,
[peopleId] [int] NULL,
[chargeamount] [money] NULL,
[billMonth] [int] NULL,
[billYear] [int] NULL,
CONSTRAINT [PK_Billing1st] PRIMARY KEY CLUSTERED
CREATE TABLE [dbo].[certs](
[certificationIndex] [int] IDENTITY(1,1) NOT NULL,
[peopleId] [int] NULL,
[statusID] [int] NULL,
[certificationExpireDate] [datetime] NULL,
CONSTRAINT [PK_certs] PRIMARY KEY CLUSTERED
SELECT TOP 10 [indx],[peopleId],[chargeamount],[billMonth],[billYear]
FROM [HSBCP_DB].[dbo].[billing] order by peopleID DESC
indxpeopleIdchargeamountbillMonthbillYear
12576373440.0062015
12575365840.0062015
13275363040.0072015
12544360140.0052015
12543359640.0052015
12574359240.0062015
13274357340.0072015
13273355240.0072015
13272353940.0072015
13271353240.0072015
SELECT TOP 10 [certificationIndex],[peopleId],[statusID],[certificationExpireDate]
FROM [HSBCP_DB].[dbo].[certs] order by peopleID DESC
certificationIndexpeopleIdstatusIDcertificationExpireDate
439646051NULL
439546041NULL
439446031NULL
439346021NULL
438646011NULL
438546001NULL
438445991NULL
438345981NULL
438245971NULL
438145961NULL
June 4, 2015 at 3:09 pm
Thanks! The DDL gets us part of the way there.
Unfortunately that sample data won't illustrate the undesired results you're getting, or your desired results (there's just no overlap between those sets).
Could you post data from each table for the PeopleIDs you're currently getting (the 52) as well as which 14 are the desired results (and why the other 38 should be excluded?)
Once we see that, it should be pretty simple to help you out.
Cheers!
EDIT: Also, if possible it's helpful to have the data posted INSERTable form, as in this link: http://www.sqlservercentral.com/articles/Best+Practices/61537/
That helps us easily test potential solutions.
June 4, 2015 at 3:22 pm
briancampbellmcad (6/4/2015)
Hope this helps as far as DDL and what the data looks like:CREATE TABLE [dbo].[Billing](
[indx] [int] IDENTITY(1,1) NOT NULL,
[peopleId] [int] NULL,
[chargeamount] [money] NULL,
[billMonth] [int] NULL,
[billYear] [int] NULL,
CONSTRAINT [PK_Billing1st] PRIMARY KEY CLUSTERED
CREATE TABLE [dbo].[certs](
[certificationIndex] [int] IDENTITY(1,1) NOT NULL,
[peopleId] [int] NULL,
[statusID] [int] NULL,
[certificationExpireDate] [datetime] NULL,
CONSTRAINT [PK_certs] PRIMARY KEY CLUSTERED
SELECT TOP 10 [indx],[peopleId],[chargeamount],[billMonth],[billYear]
FROM [HSBCP_DB].[dbo].[billing] order by peopleID DESC
indxpeopleIdchargeamountbillMonthbillYear
12576373440.0062015
12575365840.0062015
13275363040.0072015
12544360140.0052015
12543359640.0052015
12574359240.0062015
13274357340.0072015
13273355240.0072015
13272353940.0072015
13271353240.0072015
SELECT TOP 10 [certificationIndex],[peopleId],[statusID],[certificationExpireDate]
FROM [HSBCP_DB].[dbo].[certs] order by peopleID DESC
certificationIndexpeopleIdstatusIDcertificationExpireDate
439646051NULL
439546041NULL
439446031NULL
439346021NULL
438646011NULL
438546001NULL
438445991NULL
438345981NULL
438245971NULL
438145961NULL
That isn't even complete ddl. You have partially defined tables. You seem to adding a primary key to columns that don't exist. Then of course the create table statement isn't even finished for either table. The point here is to allow us to create these on our system so we can test our queries.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 4, 2015 at 4:05 pm
Hope this is what is needed:
USE [HSBCPcopy_DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[certs](
[certificationIndex] [int] IDENTITY(1,1) NOT NULL,
[peopleId] [int] NULL,
[statusID] [int] NULL,
[certificationExpireDate] [datetime] NULL,
CONSTRAINT [PK_certs] PRIMARY KEY CLUSTERED
(
[certificationIndex] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [HSBCPcopy_DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Billing](
[indx] [int] IDENTITY(1,1) NOT NULL,
[peopleId] [int] NULL,
[chargeamount] [int] NULL,
[billMonth] [int] NULL,
[billYear] [int] NULL,
CONSTRAINT [PK_Billing1st] PRIMARY KEY CLUSTERED
(
[indx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (704,700,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (703,699,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (701,697,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (700,696,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (699,695,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (697,693,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (694,690,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (690,686,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (689,685,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (682,678,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (681,677,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (680,676,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (673,669,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (671,667,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (670,666,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (668,664,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (667,663,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (666,662,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (662,658,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (661,657,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (660,656,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (659,655,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (652,648,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (651,647,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (650,646,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (649,645,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (647,643,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (645,641,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (644,640,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (643,639,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (641,637,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (640,636,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (638,634,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (637,633,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (636,632,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (631,627,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (628,624,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (623,619,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (622,618,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (616,612,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (615,611,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (612,608,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (605,601,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (604,600,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (603,599,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (602,598,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (599,595,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (595,591,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (593,589,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (586,582,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (584,580,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (579,575,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (577,573,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (574,570,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (572,568,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (568,564,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (562,558,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (557,553,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (556,552,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (555,551,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (554,550,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (551,547,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (550,546,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (549,545,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (546,542,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (545,541,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (543,539,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (542,538,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (540,536,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (536,532,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (534,530,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (530,526,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (528,524,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (526,522,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (525,521,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (524,520,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (522,518,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (518,514,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (515,511,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (512,508,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (511,507,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (509,505,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (508,504,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (505,501,1,'2015-07-31 00:00:00.000')
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12856,700,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12855,699,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12854,697,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12853,696,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12852,695,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12851,693,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12850,690,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12849,686,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12848,685,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12847,678,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12846,676,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12845,669,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12844,666,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12843,664,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12842,663,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12841,662,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12840,658,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12839,657,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12838,656,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12837,655,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12836,648,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12835,647,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12834,646,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12833,645,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12832,643,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12831,641,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12830,640,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12829,639,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12828,637,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12827,636,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12826,634,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12825,633,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12824,632,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12823,627,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12822,619,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12821,618,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12820,612,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12819,611,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12818,608,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12817,601,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12816,598,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12815,595,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12814,591,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12813,589,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12812,582,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12811,580,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12810,575,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12809,573,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12808,570,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12807,564,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12806,558,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12805,556,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12804,553,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12803,552,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12802,551,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12801,550,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12800,547,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12799,546,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12798,545,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12797,542,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12796,541,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12795,539,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12794,538,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12793,536,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12792,532,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12791,530,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12790,526,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12789,524,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12788,522,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12787,521,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12786,520,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12785,518,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12784,514,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12783,511,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12782,508,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12781,507,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12780,505,40,7,2015)
INSERT INTO [HSBCPcopy_DB].[dbo].[certs]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12779,501,40,7,2015)
June 4, 2015 at 4:19 pm
That sample data returns 7 rows(and the table name on the second insert section is wrong) with the query you posted earlier.
SELECT c.PeopleID
FROM [certs] c
where c.[statusID] = 1 and c.[certificationExpireDate] = '2015-07-31 00:00:00.000'
and NOT exists
(SELECT * FROM [Billing] b
where b.[billYear] = 2015 and b.[billMonth] = 7 and b.peopleID = c.PeopleID)
Which rows should not be included or which rows aren't being included?
June 4, 2015 at 4:30 pm
Ok, thanks! A couple notes. The table name was the same for both batches of inserts, so I changed the second batch to billing. You're also attempting to explicitly insert identity values, so for the script to work we need to SET IDENTITY_INSERT ON for each table when we're inserting.
It's also using a specific database that most of us probably don't have, so I've switched it to tempdb.
Once that's all said and done, we have the following code:
USE [tempdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[certs](
[certificationIndex] [int] IDENTITY(1,1) NOT NULL,
[peopleId] [int] NULL,
[statusID] [int] NULL,
[certificationExpireDate] [datetime] NULL,
CONSTRAINT [PK_certs] PRIMARY KEY CLUSTERED
(
[certificationIndex] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [tempdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Billing](
[indx] [int] IDENTITY(1,1) NOT NULL,
[peopleId] [int] NULL,
[chargeamount] [int] NULL,
[billMonth] [int] NULL,
[billYear] [int] NULL,
CONSTRAINT [PK_Billing1st] PRIMARY KEY CLUSTERED
(
[indx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT certs ON
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (704,700,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (703,699,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (701,697,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (700,696,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (699,695,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (697,693,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (694,690,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (690,686,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (689,685,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (682,678,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (681,677,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (680,676,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (673,669,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (671,667,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (670,666,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (668,664,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (667,663,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (666,662,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (662,658,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (661,657,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (660,656,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (659,655,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (652,648,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (651,647,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (650,646,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (649,645,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (647,643,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (645,641,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (644,640,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (643,639,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (641,637,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (640,636,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (638,634,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (637,633,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (636,632,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (631,627,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (628,624,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (623,619,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (622,618,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (616,612,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (615,611,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (612,608,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (605,601,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (604,600,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (603,599,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (602,598,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (599,595,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (595,591,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (593,589,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (586,582,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (584,580,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (579,575,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (577,573,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (574,570,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (572,568,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (568,564,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (562,558,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (557,553,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (556,552,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (555,551,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (554,550,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (551,547,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (550,546,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (549,545,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (546,542,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (545,541,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (543,539,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (542,538,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (540,536,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (536,532,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (534,530,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (530,526,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (528,524,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (526,522,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (525,521,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (524,520,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (522,518,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (518,514,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (515,511,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (512,508,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (511,507,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (509,505,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (508,504,1,'2015-07-31 00:00:00.000')
INSERT INTO [tempdb].[dbo].[certs]([certificationIndex],[peopleId],[statusID],[certificationExpireDate]) VALUES (505,501,1,'2015-07-31 00:00:00.000')
SET IDENTITY_INSERT certs OFF
SET IDENTITY_INSERT billing ON
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12856,700,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12855,699,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12854,697,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12853,696,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12852,695,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12851,693,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12850,690,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12849,686,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12848,685,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12847,678,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12846,676,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12845,669,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12844,666,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12843,664,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12842,663,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12841,662,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12840,658,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12839,657,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12838,656,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12837,655,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12836,648,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12835,647,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12834,646,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12833,645,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12832,643,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12831,641,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12830,640,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12829,639,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12828,637,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12827,636,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12826,634,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12825,633,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12824,632,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12823,627,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12822,619,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12821,618,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12820,612,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12819,611,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12818,608,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12817,601,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12816,598,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12815,595,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12814,591,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12813,589,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12812,582,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12811,580,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12810,575,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12809,573,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12808,570,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12807,564,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12806,558,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12805,556,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12804,553,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12803,552,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12802,551,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12801,550,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12800,547,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12799,546,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12798,545,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12797,542,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12796,541,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12795,539,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12794,538,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12793,536,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12792,532,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12791,530,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12790,526,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12789,524,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12788,522,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12787,521,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12786,520,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12785,518,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12784,514,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12783,511,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12782,508,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12781,507,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12780,505,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12779,501,40,7,2015)
SET IDENTITY_INSERT billing OFF
--Ok, now the attempt that you say gives incorrect results
SELECT c.PeopleID
FROM [tempdb].[dbo].[certs] c
WHERE c.[statusID] = 1 AND c.[certificationExpireDate] = '2015-07-31 00:00:00.000'
AND NOT EXISTS
(SELECT * FROM [tempdb].[dbo].[Billing] b
WHERE b.[billYear] = 2015 and b.[billMonth] = 7 AND b.peopleID = c.PeopleID)
--Clean up our tables
DROP TABLE certs, billing
The results I get from the query you provided with this data set is this:
PeopleID
-----------
504
568
599
600
624
667
677
Is this result the desired result for this set? If so, then it seems this data set doesn't exhibit the problem you're encountering, and we'll need the problematic data (you could get the data from both tables for the PeopleIDs you're having returned from the query but don't want).
If this isn't the desired result, what is the desired result?
Thanks!
June 5, 2015 at 9:16 am
[font="Comic Sans MS"]While this does not answer your question, I suggest you use from now on (does not invalidate what you have done, just easier) the following to ease your work in filling up the tables with test data:[/font]
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear)
SELECT VALUES (12856,700,40,7,2015) UNION ALL
SELECT VALUES (12855,699,40,7,2015) UNION ALL
SELECT VALUES (12854,697,40,7,2015)...
[font="Comic Sans MS"] or simpler yet - not possible in SS2K5[/font]
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear)
VALUES
(12856,700,40,7,2015),
(12855,699,40,7,2015),
(12854,697,40,7,2015)...
[font="Comic Sans MS"] Instead of [/font]
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12856,700,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12855,699,40,7,2015)
INSERT INTO [tempdb].[dbo].[billing]([indx],[peopleId],[chargeAmount],[billMonth],billYear) VALUES (12854,697,40,7,2015)
June 5, 2015 at 9:46 am
Yes thanks for the tips on several points... I got the same results so my initial query wasn't that bad... but I did discover that the data in general was questionable... the Billing table had 699 and the Certs had 713, but my query came back with 52 because more than just 14 records had mismatches between the two tables... so this actually was good as it exposed a flaw in the data that existed.
June 5, 2015 at 9:49 am
briancampbellmcad (6/5/2015)
Yes thanks for the tips on several points... I got the same results so my initial query wasn't that bad... but I did discover that the data in general was questionable... the Billing table had 699 and the Certs had 713, but my query came back with 52 because more than just 14 records had mismatches between the two tables... so this actually was good as it exposed a flaw in the data that existed.
Never be too quick to blame your query when the answer can just as easily be the people giving you the data 😀
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply