Obtaining one customer instance

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)

Obtaining one customer instance

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-01-11 : 04:16:44

Morning all

I have the following tables:


CycleInstance
CycleInstanceID PK
CycleInstanceDate

Customer		EBill		BounceBack
CustID PK	        EbillID	PK	BounceID PK
AccNumber		CustID	FK	EbillID FK
TelNumber		InvNumber	BounceDate
CustName		EmailAdd		
Address1		CycleInstanceID FK		
Address2				
Address3				
Address4				
Postcode

A cycleInstance can have many ebills. An ebill is for one customer only, and a customer can have many EBills. An EBill can bounce many times.

We need to extract the fields from all the tables (except the ids), but only one time per customer. For example, a customer may have been sent many EBills over a period of time, and these ebills have bounced numerous times or not at all. However, we only need to extract one instance of a customer for the ebills they have been sent, but have bounced (for example, the invoice number and email address of the latest ebill to have bounced). How can I do this?? I cannot use distinct or group by, as the invoice numbers and cycleinstanceDate will be different, so will be included?

Thankyou

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-01-11 : 04:38:30

God, I keep doing this! Its not that I don't try to find a solution, but it appears that at the end of the day, the brain functions at about 75% less efficiency so when I come in the morning, ask the question that I was working on for hours......as it is morning....I come up with the solution immediately afterwards!! Doh doh and DOH!!!

Anyway, I can use HAVING!!!!!!


SELECT  E.CustomerID
	, InvoiceNumber
	, EmailAddress
	, AccountNumber
	, TelephoneNumber
	, CustomerName
	, AddressLine1
	, AddressLine2
	, AddressLine3
	, AddressLine4
	, Postcode 
FROM EBill AS E
INNER JOIN Customer AS C ON C.CustomerID = E.CustomerID
INNER JOIN BounceBack AS B ON E.EBillID = B.EBillID
WHERE BounceDate >= dbo.fn_CalculateStartDate(@ScheduleID) 
AND BounceDate < dbo.fn_CalculateEndDate(@ScheduleID)
GROUP BY E.CustomerID
	, InvoiceNumber
	, EmailAddress
	, AccountNumber
	, TelephoneNumber
	, CustomerName
	, AddressLine1
	, AddressLine2
	, AddressLine3
	, AddressLine4
	, Postcode 
HAVING COUNT(E.CustomerID) > 1

Sorry for wasting time!

Hearty head pats

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-11 : 04:52:13

Good for you. I was staring at your question for minutes and decide to give up. It's almost end of a work day for me. Yeah you are right about the brain function . . .

Shutting down in T - 8 min

-----------------
'KH'
Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-01-11 : 04:55:32

Once again, here I am being all cocky, yet I have not solved the issue at all! Yes, having reduces the number of customer occurences to 1, but what of those that have only had one occurrence, they are not included?!?!?! I was originally going to do a nested select and join the results of the first search (without a having clause) with the second, but then, back to square one as I have ALL the occurrences in the first select! Oh SUGAR!

So yes, I do need your expertise!!

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-01-11 : 05:03:59

Lol, glad to hear that I am not the only person for that to happen to. Unfortunately, in this scenario, I jumped the gun a little prematurely! Ah well, at least I it is morning.....so functioning at least at ..erm...80%?!?!?

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-01-11 : 05:19:25

Right, I have got this far. I have identified the customers that match the search criteria and have obtained all the customer information. How do I now get only one instance of a customer (and preferably, the invoicenumber of the most recent ebill that was bounced)?


DECLARE @ScheduleID BIGINT
SET @ScheduleID = 103

SELECT AccountNumber
	, TelephoneNumber
	, CustomerName
	, AddressLine1
	, AddressLine2
	, AddressLine3
	, AddressLine4
	, Postcode 
	, CustomerID
FROM Customer WHERE CustomerID IN
	(
	SELECT DISTINCT CustomerID FROM EBill WHERE EBillID IN
		(
		SELECT DISTINCT EBillID FROM BounceBack
		WHERE BounceDate >= dbo.fn_CalculateStartDate(@ScheduleID) 
		AND BounceDate < dbo.fn_CalculateEndDate(@ScheduleID)
		)
	)

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-01-11 : 07:10:27

Hmmm, think I may have it! GOnna test with some dummy data first, but here is the solution as it stands....any comments or suggestions for improvements (if, that is, it works) are welcome!


CREATE   PROCEDURE [dbo].[usp_SELECTBounceBacks] 
(
  @ScheduleID BIGINT
)
AS
SET NOCOUNT ON

SELECT    C.CustomerID
	, InvoiceNumber
	, EmailAddress
	, C.AccountNumber
	, C.TelephoneNumber
	, C.CustomerName
	, C.AddressLine1
	, C.AddressLine2
	, C.AddressLine3
	, C.AddressLine4
	, C.Postcode 
FROM Customer AS C INNER JOIN EBill AS E 
ON C.CustomerID = E.CustomerID 
INNER JOIN 
(
SELECT LatestEBillID FROM 
	(
	SELECT    CustomerID
		, MAX(EBillID)AS LatestEBillID
		FROM EBill WHERE EBillID IN
			(
			SELECT DISTINCT EBillID FROM BounceBack
			WHERE BounceDate >= dbo.fn_CalculateStartDate(@ScheduleID) 
			AND BounceDate < dbo.fn_CalculateEndDate(@ScheduleID)
			)
		GROUP BY CustomerID
	)AS T1
)AS T2 
ON E.EBillID = T2.LatestEBillID
GO

Hearty head pats

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-01-11 : 08:57:08

YOu have an extra derived table in there that you don't need; see below:

quote:

Originally posted by Bex


CREATE   PROCEDURE [dbo].[usp_SELECTBounceBacks] 
(
  @ScheduleID BIGINT
)
AS
SET NOCOUNT ON

SELECT    C.CustomerID
	, InvoiceNumber
	, EmailAddress
	, C.AccountNumber
	, C.TelephoneNumber
	, C.CustomerName
	, C.AddressLine1
	, C.AddressLine2
	, C.AddressLine3
	, C.AddressLine4
	, C.Postcode 
FROM Customer AS C INNER JOIN EBill AS E 
ON C.CustomerID = E.CustomerID 
INNER JOIN 
(
SELECT LatestEBillID FROM 
	(
	SELECT    CustomerID
		, MAX(EBillID)AS LatestEBillID
		FROM EBill WHERE EBillID IN
			(
			SELECT DISTINCT EBillID FROM BounceBack
			WHERE BounceDate >= dbo.fn_CalculateStartDate(@ScheduleID) 
			AND BounceDate < dbo.fn_CalculateEndDate(@ScheduleID)
			)
		GROUP BY CustomerID
	)AS T1
)AS T2 
ON E.EBillID = T2.LatestEBillID
GO

In addition, it may be more efficient to store the EndDate and the StartDate in variables before the select, to be sure that the functions get called only once.

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-01-11 : 11:21:05

Aha, so I have! Thanks for the info, I shall give that a go!

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources