Express server - Tough query - 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 2005 Forums

Express Edition and Compact Edition (2005)

Express server - Tough query

Author

Topic

res0l
Starting Member

4 Posts

Posted - 2008-06-10 : 16:00:52

I have tables [CustomerData] ([CD]) and [MembershipData] ([MD]).

[CD] has
ID - Primary key
FirstName
LastName
Birthdate
Etc

[MD] has
ID - Primary key
Customer - The ID of the customer from [CustomerData]
StartDate - Membership start date
EndDate - Membership end date

Each [CD] can have multiple [MD].

Memberships can be looked at in one of four ways. They are also prioritized (see next paragraph) in this order
Current: Membership started on or before today and ends on or after today.
Future: Membership starts after today.
Past: Membership ended before today and start and end dates do not fall on 1/1/1900
None: Membership starts and ends on 1/1/1900

I have created four Views that return all memberships for each type. I am trying to create a query that selects all [CD] and includes the highest matching membership for each [CD]

I have a working query that will return the highest priority [MD] that a [CD] has when I specify a [CD] in my WHERE.

That query looks like this. (CurrentMembership, FutureMembership, PastMembership and NoMembership are my Views)

IF (EXISTS(SELECT * FROM CurrentMembership WHERE Customer = 452))
	(SELECT TOP 1 * FROM CurrentMembership WHERE Customer = 452)
ELSE
	IF (EXISTS(SELECT * FROM FutureMembership WHERE Customer = 452))
		(SELECT TOP 1 * FROM FutureMembership WHERE Customer = 452)
	ELSE
		IF (EXISTS(SELECT * FROM PastMembership WHERE Customer = 452))
			(SELECT TOP 1 * FROM PastMembership WHERE Customer = 452)
		ELSE
			(SELECT TOP 1 * FROM NoMembership WHERE Customer = 452)

I have it working for one specified individual. I'm at a loss as to how to extend it to work for all individuals.

Any ideas?

TIA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-10 : 16:12:16

[code]SELECT SomeID,
Customer
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY theOrder) AS RecID,
SomeID,
Customer
FROM (
SELECT 1 AS theOrder,
SomeID,
Customer
FROM CurrentMembership

UNION ALL

SELECT 2,
SomeID,
Customer
FROM FutureMembership

UNION ALL

SELECT 3,
SomeID,
Customer
FROM PastMembership

UNION ALL

SELECT 4,
SomeID,
Customer
FROM NoMembership
) AS d
) AS r
WHERE RecID = 1[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-10 : 16:22:00

And without views

SELECT	w.Customer,
	w.FirstName,
	w.LastName,
	w.BirthDate,
	w.theOrder,
	w.StartDate,
	w.EndDate
FROM	(
		SELECT		q.Customer,
				q.FirstName,
				q.LastName,
				q.BirthDate,
				q.theOrder,
				q.StartDate,
				q.EndDate,
				ROW_NUMBER() OVER (PARTITION BY q.Customer ORDER BY q.theOrder) AS RecID
		FROM		(
					SELECT		cd.ID AS cdID,
							cd.FirstName,
							cd.LastName,
							cd.BirthDate,
							CASE
								WHEN md.StartDate > '19000101' AND md.StartDate <= GETDATE() AND md.EndDate >= GETDATE() THEN 1
								WHEN md.StartDate >= GETDATE() AND md.EndDate > '19000101' THEN 2
								WHEN md.StartDate > '19000101' AND md.EndDate > '19000101' AND md.EndDate < GETDATE() THEN 3
								ELSE 4
							END AS theOrder,
							md.StartDate,
							md.EndDate
					FROM		CustomerData AS cd
					INNER JOIN	MembershipData AS md ON md.Customer = cd.ID
				) AS q
	) AS w
WHERE	w.RecID =

E 12°55'05.25"
N 56°04'39.16"

res0l
Starting Member

4 Posts

Posted - 2008-06-10 : 16:24:22

Thanks for your prompt reply. That's way over my head, though. :)

What is SomeID and how do I use it?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-10 : 16:35:14

Have a look at the second example.

E 12°55'05.25"
N 56°04'39.16"

res0l
Starting Member

4 Posts

Posted - 2008-06-10 : 17:04:21

I've nearly got the second one working how I want it. You're a genius, thanks!

One small issue. If there are multiple currents I need the one that started furthest from today. If there are multiple futures I need the one that starts closest to today and if there are multiple pasts I need the one that started closest to today.

I was accomplishing this in the views by ordering by start date in the direction I needed it to. I don't see a simple way of doing that with your second method. Other than that, it works perfectly.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-10 : 17:27:32

[code]SELECT w.Customer,
w.FirstName,
w.LastName,
w.BirthDate,
w.theOrder,
w.StartDate,
w.EndDate
FROM (
SELECT q.Customer,
q.FirstName,
q.LastName,
q.BirthDate,
q.theOrder,
q.StartDate,
q.EndDate,
ROW_NUMBER() OVER (PARTITION BY q.Customer ORDER BY q.theOrder, CASE
WHEN q.theOrder = 1 THEN DATEDIFF(DAY, '19000101', q.StartDate)
WHEN q.theOrder = 2 THEN DATEDIFF(DAY, GETDATE(), q.StartDate)
WHEN q.theOrder = 3 THEN DATEDIFF(DAY, q.StartDate, GETDATE())
WHEN q.theOrder = 4 THEN 0
END
) AS RecID
FROM (
SELECT cd.ID AS cdID,
cd.FirstName,
cd.LastName,
cd.BirthDate,
CASE
WHEN md.StartDate > '19000101' AND md.StartDate <= GETDATE() AND md.EndDate >= GETDATE() THEN 1
WHEN md.StartDate >= GETDATE() AND md.EndDate > '19000101' THEN 2
WHEN md.StartDate > '19000101' AND md.EndDate > '19000101' AND md.EndDate < GETDATE() THEN 3
ELSE 4
END AS theOrder,
md.StartDate,
md.EndDate
FROM CustomerData AS cd
INNER JOIN MembershipData AS md ON md.Customer = cd.ID
) AS q
) AS w
WHERE w.RecID = 1[/code]

E 12°55'05.25"
N 56°04'39.16"

res0l
Starting Member

4 Posts

Posted - 2008-06-10 : 17:39:19

Man, you rock! Thanks a ton!

I thought it was a tough query but you don't seem to have even broken a sweat.

/notworthy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources