stored procs - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

stored procs

Author

Topic

dogtag1
Starting Member

4 Posts

Posted - 2005-08-05 : 12:42:24

Hi guys, I'm relatively new to SQL, and I've been working on a stored procedure to pull a bunch of records from 2 separate tables in a database and put them together. The way it works is that one table is a contact info table, the other is a billing table. They have a common field called contactID. what i need to do is pull all the records where the persons contract date (in the contact table) is between 2 dates, and also any payments they made between those dates (in the billing table). I had a stored procedure written that sort of worked, but it was missing a bunch of records. Also, after I pull this info, what i was thinking of doing was something like:

 
create table #contactinfo 
( 
contactid int, 
contractdate datetime, 
paymentdate datetime 
) 

insert into #contactinfo 
select contactid, contractdate, null 
from contacts 
where contractdate >= @begindate and contractdate <= @Enddate 

insert into #contactinfo 
select contactid, null, paymentdate 
from billing 
where contractdate >= @begindate and contractdate <= @Enddate

after i do this, i need to get rid of any duplicate contact IDs (which should have one or the other of a contract date or payment date) and make them one entry, with both a conractdate and paymentdate...this is where i'm stumped. after that, it should be easy enough. any thoughts? (and am i making this too difficult)

thanks,
Jeff

Kristen
Test

22859 Posts

Posted - 2005-08-05 : 13:18:51

Hi dogtag1, Welcome to SQL Team!

Does this do the job?


CREATE TABLE contacts
(
	contactid	int,
	contractdate	datetime
)
GO
CREATE TABLE billing
(
	contactid	int,
	paymentdate	datetime
)
GO
INSERT INTO contacts
SELECT 1, '01-Aug-2005' UNION ALL
SELECT 2, '02-Aug-2005' UNION ALL
SELECT 4, '04-Aug-2005' UNION ALL
SELECT 31, '31-Dec-2005'
GO
INSERT INTO billing
SELECT 1, '01-Aug-2005' UNION ALL
SELECT 2, '02-Sep-2005' UNION ALL
SELECT 3, '03-Sep-2005' UNION ALL
SELECT 31, '31-Dec-2005'
GO

SELECT	[contactid] = COALESCE(C.contactid, B.contactid),
	C.contractdate,
	B.paymentdate
FROM
	(
		SELECT	contactid, contractdate
		FROM	contacts
		WHERE	    contractdate > '01-Aug-2005'
			AND contractdate < '31-Dec-2005'
	) C
	FULL OUTER JOIN
	(
		SELECT	contactid, paymentdate
		FROM	billing
		WHERE	    paymentdate > '01-Aug-2005'
			AND paymentdate < '31-Dec-2005'
	) B
		ON B.contactid = C.contactid
ORDER BY B.contactid
GO
DROP TABLE contacts
GO
DROP TABLE billing
GO

NOTE: The last line in your second query refers to contractdate, I've used paymentdate from the billing table as I wasn't sure yours was a typo. If there IS a contractdate in the billing table then it needs a little modification to the "criteria" stuff, but should otherwise be about the same.

And IF there is a contractdate in the billing table is it really reasonable that you can have a billing record, with a contractdate, but not corresponding record in the contacts table? (If the answer is NO then you just need a LEFT OUTER JOIN and not all this complex FULL OUTER JOIN stuff

)

Kristen

dogtag1
Starting Member

4 Posts

Posted - 2005-08-05 : 14:03:53

ah crap, just wrote a long response but forgot to put my user name and password in. anyway the short version is that yes that was a typo, it was supposed to be paymentdate, and that
a) contacts table is basically the center of the database meaning that there wont be any contactIDs in the billing table that arent in the contacts table because all records get added there before anything else can be done w/ them. i'm still new to sql, and although i understand how the embedded select statements you wrote in there work, it took me a second to visualize it all.

ty,
-jeff

dogtag1
Starting Member

4 Posts

Posted - 2005-08-05 : 14:14:14

you know what, i was just relooking at this and there's another part. i need to pull contracts from that day without payments on the same day (i.e. a downpayment when they signed the contract), a contract with a downpayment, and payments on old contracts (and the contract date so it can be listed) from that day. makes more sense than how i described it originally

Kristen
Test

22859 Posts

Posted - 2005-08-05 : 14:18:16

OK, we can go with the simpler LEFT OER JOIN - provided that

:

What happens if the payment date is within range, but the corresponding contract date is NOT - does the contract date show up, or does it need to be NULL?

The method below will include the out-of-range contract date


DROP TABLE contacts
GO
DROP TABLE billing
GO
CREATE TABLE contacts
(
	contactid	int,
	contractdate	datetime
)
GO
CREATE TABLE billing
(
	contactid	int,
	paymentdate	datetime
)
GO
INSERT INTO contacts
SELECT 1, '01-Aug-2005' UNION ALL	-- All too old
SELECT 2, '02-Jan-2005' UNION ALL	-- Too old, but billing OK
SELECT 3, '03-Aug-2005' UNION ALL	-- OK, but billing not OK
SELECT 4, '04-Aug-2005' UNION ALL	-- Both OK
SELECT 31, '31-Dec-2005'		-- All too new
GO
INSERT INTO billing
SELECT 1, '01-Aug-2005' UNION ALL	-- Too old
SELECT 2, '02-Sep-2005' UNION ALL
SELECT 3, '03-Jan-2005' UNION ALL	-- Too old
SELECT 31, '31-Dec-2005'		-- Too new
GO

SELECT	C.contactid,
	C.contractdate,
	B.paymentdate
FROM	contacts C
	LEFT OUTER JOIN billing B
		 ON B.contactid = C.contactid
		AND paymentdate > '01-Aug-2005'
		AND paymentdate < '31-Dec-2005'
WHERE	   B.contactid IS NOT NULL	-- Got an OK Billing record
	OR
	(
		    contractdate > '01-Aug-2005'
		AND contractdate < '31-Dec-2005'
	)
ORDER BY B.contactid
GO
DROP TABLE contacts
GO
DROP TABLE billing
GO

Kristen

dogtag1
Starting Member

4 Posts

Posted - 2005-08-05 : 14:43:09

Thanks, that makes sense.


SELECT	C.contactid,
	C.contractdate,
	B.paymentdate
FROM	contacts C
	LEFT OUTER JOIN billing B
		 ON B.contactid = C.contactid
		AND paymentdate > '01-Aug-2005'
		AND paymentdate < '31-Dec-2005'
WHERE	   B.contactid IS NOT NULL	-- Got an OK Billing record
	OR
	(
		    contractdate > '01-Aug-2005'
		AND contractdate < '31-Dec-2005'
	)
ORDER BY B.contactid

just for the sake of understanding this, how is the "AND paymentdate >...." before the where statement? and what does this do? Thanks again for the help.

-Jeff

Kristen
Test

22859 Posts

Posted - 2005-08-06 : 02:47:47

Putting the AND stuff in the LEFT OUTER JOIN means that only those rows are satisfied. If the ON / AND stuff in the JOIN doesn't match any rows then the "billing" columns come back NULL.

This is just a bit more efficient, and easier, than messing around with it in the WHERE clause.

So basically we get All contacts that
EITHER have a ContractDate in the range
OR have a Billing Record in range ("B.contactid IS NOT NULL" indicates we found a billing record and, ipso facto, it must have a valid payment date)

In my previous example I was trying to get things where either of the tables had a date within the range and I was also assuming that the corresponding record in the other table might, or might not

, exist - bit more complicated that one!

If you try to do it all in the WHERE clause you would wind up with something along the lines of:

Select it:
If the Contract Date is in Range
Or there exists a Billing record, and it has a PaymentDate in Range

Actually not that hard now I've written it! But my guess would be that the SQL optimiser might miss the significance of the OR in that WHERE and might restort to a table scan, rather than using indexes, whereas doing it this way tells the query optimised to only hit on Billing records in the date range.

Anyway, thats how I *think* it works! You could try it both ways and see what the Query Plan is - might be interesting to know if the Query Planner comes up with the exact same plan!


SELECT	C.contactid,
	C.contractdate,
	B.paymentdate
FROM	contacts C
	LEFT OUTER JOIN billing B
		 ON B.contactid = C.contactid
WHERE	(
		    B.contactid IS NOT NULL	-- Got a Billing record
		AND paymentdate > '01-Aug-2005'
		AND paymentdate < '31-Dec-2005'
	)
	OR
	(
		    contractdate > '01-Aug-2005'
		AND contractdate < '31-Dec-2005'
	)
ORDER BY B.contactid

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources