help modifying query (changed db design)

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

Transact-SQL (2000)

help modifying query (changed db design)

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-08-31 : 04:57:00

I had help creating the query below, and now I've got to modify it a little but im a little unsure on how to. I have to change it because I changed the database design a little.

tblMemberShips has been broken out into two tables

tblMemberShips, and tblMemberShipType

I can join the tables like below

JOIN tblMemberships.membershipTypeID on tblMemberShipType.membershipTypeID

tblMemberShips is now composed of

transactionID purchasedFor purchasedBy dateStart membershipTypeID
2 5000 5000 2004-08-30 1

tblMemberShipTypes is composed of

membershipTypeID membershipLevel membershipDesc durationMonths

1 10 Basic NULL
2 20 GrandFathered NULL
3 30 Premium 3
4 30 Premium 12
5 30 Premium NULL

A user can have more than one row in the "tblMemberships" table. I need to join onto the newest one with the highest access to set their login level.

Thanks again for any assistance

mike123

CREATE PROCEDURE DBO.select_logon
(
@nameOnline varchar(15),
@password varchar(20)
)
AS SET NOCOUNT ON

IF EXISTS
(SELECT tblUserDetails.userID FROM tblUserDetails
INNER JOIN tblMemberships ON tblMemberships.purchasedFor = tblUserDetails.userID
INNER JOIN (SELECT purchasedFor, Max(dateEnd) as dateEnd FROM tblMemberships GROUP BY purchasedFor) M ON M.purchasedFor = tblMemberships.purchasedFor AND M.dateEnd = tblMemberships.dateEnd
WHERE nameOnline = @nameOnline and password = @password)

SELECT Top 1
u.userID, u.nameOnline, u.active, u.guid,
m.dateEnd, m.membershipType
FROM
tblUserDetails u
LEFT OUTER JOIN
tblMemberships m
ON
m.purchasedFor = u.userID AND
getDate() between m.DateStart and m.DateEnd
WHERE
u.nameOnline = @nameOnline and
u.password = @password
ORDER BY
m.MembershipType DESC, m.DateEnd DESC

ELSE

SELECT tblUserDetails.userID, nameOnline, active, guid, '2010-10-10' as dateEnd, '20' as memberShipType, '20' as memberShipLevel FROM tblUserDetails WHERE nameOnline = @nameOnline and password = @password

GO

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-31 : 06:22:22

try this:


SELECT 	u.userID, u.nameOnline, u.active, u.guid, 
	m.dateEnd, m.membershipType 
FROM 	tblUserDetails u
	LEFT OUTER JOIN	
	tblMemberShips m on m.purchasedFor = u.userID AND
getDate() between m.DateStart and m.DateEnd
	inner join (	select max(membershipTypeID) as membershipTypeID, transactionID 
			from tblMemberShips 
			group by transactionID) t2
	on (m.transactionID = t2.transactionID) and (m.membershipTypeID = t2.membershipTypeID)
WHERE 	u.nameOnline = @nameOnline and 
	u.password = @password
ORDER BY	m.MembershipType DESC, m.DateEnd DESC

Go with the flow & have fun! Else fight the flow :)

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-08-31 : 14:42:40

Hi spirit1,

Thanks for the attempt. The problem is that I changed the database design and there is no "dateEnd" column any longer.

DateEnd is now the datestart column plus the added months of the membership type which is the "tblMembershipType" column. Sorry for any confusion on that part.

Thanks again,
mike123

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-31 : 14:51:31

aha. so you need to join the tblMemberShips once more toitself in the derived table, to get correct durationMonths,
based on max(membershipTypeID). or simply add durationMonths in group by and select in the derived table if it works for u.
and then do this:

getDate() between m.DateStart and dateAdd(m, isnull(t2.durationMonths, 0), m.DateStart)

or i'm i missing something here?

Go with the flow & have fun! Else fight the flow :)

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-08-31 : 14:57:01

Sorry one more thing I forgot. I am not exactly sure how to store an unlimited membership. Currently as shown above I am using "NULL", should I change this to a figure like 255 ?

I'll try your recommended change now.. thanks again :)

mike123

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-08-31 : 15:04:13

I'm getting the following error when running the SPROC as below

Server: Msg 107, Level 16, State 2, Line 1
The column prefix 't2' does not match with a table name or alias name used in the query.

thx again ;)
mike123

SELECT u.userID, u.nameOnline, u.active, u.guid,
m.dateEnd, m.membershipType
FROM tblUserDetails u
LEFT OUTER JOIN
tblMemberShips m on m.purchasedFor = u.userID AND
getDate() between m.DateStart and dateAdd(m, isnull(t2.durationMonths, 0), m.DateStart)
inner join ( select max(membershipTypeID) as membershipTypeID, transactionID
from tblMemberShips
group by transactionID) t2
on (m.transactionID = t2.transactionID) and (m.membershipTypeID = t2.membershipTypeID)
WHERE u.nameOnline = @nameonline and
u.password = @password
ORDER BY m.MembershipType DESC, m.DateEnd DESC

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-31 : 15:04:23

well 255 months is little over 21 years so make it a 1000 (83+ years :))

well adding a number will surly be useful in dateadd, because a null won't be included in your resultset.
so it depens on if u want to include them or not...

Go with the flow & have fun! Else fight the flow :)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-31 : 15:06:39

u didn't add the durationMonths to the derived table :)

inner join ( select max(membershipTypeID) as membershipTypeID, transactionID, durationMonths
from tblMemberShips
group by transactionID, durationMonths)

if this works for you great!
if not you'll have to join it to itself once more like i said before.

Go with the flow & have fun! Else fight the flow :)

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-08-31 : 15:53:08

spirit1, thanks for staying with me on this one... honestly this many joins and derived tables gets a bit over my head but im doing my best to stay with it....

I believe I see a few problems below, which may be due to my explanation so I'll just go over that again.

I don't see anywhere a JOIN onto "tblMembershipTypes" ? I believe we have to join onto this to get the membershiplevel that we need.

I can join the tables like below

JOIN tblMemberships.membershipTypeID on tblMemberShipType.membershipTypeID

Also I believe this part

select max(membershipTypeID) as membershipTypeID

is not needed. I am not looking for a max membershiptypeID, rather I am looking for the highest membershiplevel for the user so I make sure I set their access to the highest level given.

Hope this makes sense, if I can clear anything up please let me know.

Thanks once again

mike123

SELECT u.userID, u.nameOnline, u.active, u.guid,
m.dateEnd, m.membershipType
FROM tblUserDetails u
LEFT OUTER JOIN
tblMemberShips m on m.purchasedFor = u.userID AND
getDate() between m.DateStart and m.DateEnd
inner join ( select max(membershipTypeID) as membershipTypeID, transactionID, durationMonths
from tblMemberShips
group by transactionID, durationMonths) t2
on (m.transactionID = t2.transactionID) and (m.membershipTypeID = t2.membershipTypeID)

WHERE u.nameOnline = @nameonline and
u.password = @password ORDER BY m.MembershipType DESC, m.DateEnd DESC

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-31 : 16:48:37

ok try this:
i replaced null values for 255 for unlimited membership.

SELECT u.userID, u.nameOnline, u.active, u.guid, m.dateEnd, m.membershipType
FROM tblUserDetails u
LEFT OUTER JOIN tblMemberShips m on m.purchasedFor = u.userID
inner join (select max(dateStart) as dateStart,max(membershipLevel) as membershipLevel, purchasedFor, durationMonths
from tblMemberShips m
inner join tblMemberShipTypes t on (m.membershipTypeID = t.membershipTypeID)
group by purchasedFor, durationMonths) t1
on (m.purchasedFor = t1.purchasedFor) and (m.dateStart = t1.dateStart) and
getDate() between m.DateStart and dateAdd(m, isnull(t1.durationMonths, 0), m.dateStart)

if this doesn't work post the create table, insert into statements and desired results.

Go with the flow & have fun! Else fight the flow :)

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-08-31 : 19:29:03

Spirit1, Here is the DDL and DML as requested.

The desired results would be :

userID, nameOnline, active, guid, dateEnd(computed), membershipLevel

1, bob, 1, 'GUIDVALUE', '11/30/2004', '30'

Note: I did my best to make sure the identity columns will match up with the data inserted. I believe it should be ok.

thanks again for your patience :)

mike123

CREATE TABLE [dbo].[tblMemberships] (
[transactionID] [int] IDENTITY (1, 1) NOT NULL ,
[purchasedFor] [int] NOT NULL ,
[purchasedBy] [int] NOT NULL ,
[dateStart] [datetime] NOT NULL ,
[membershipTypeID] [tinyint] NULL ,
[comments] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblMembershipTypes] (
[membershipTypeID] [tinyint] NOT NULL ,
[membershipLevel] [tinyint] NOT NULL ,
[membershipDesc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[durationMonths] [tinyint] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblUserDetails] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[NameOnline] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Password] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Active] [tinyint] NULL ,
[GUID] [uniqueidentifier] NULL
) ON [PRIMARY]
GO

INSERT INTO tblMembershipTypes (membershipTypeID, membershipLevel, membershipDesc, durationMonths) VALUES ('1', '10', 'Basic', NULL)
INSERT INTO tblMembershipTypes (membershipTypeID, membershipLevel, membershipDesc, durationMonths) VALUES ('2', '20', 'GrandFathered', NULL)
INSERT INTO tblMembershipTypes (membershipTypeID, membershipLevel, membershipDesc, durationMonths) VALUES ('3', '30', 'Premium', 3)
INSERT INTO tblMembershipTypes (membershipTypeID, membershipLevel, membershipDesc, durationMonths) VALUES ('4', '30', 'Premium', 12)
INSERT INTO tblMembershipTypes (membershipTypeID, membershipLevel, membershipDesc, durationMonths) VALUES ('5', '30', 'Premium', NULL)

INSERT INTO tblMembershipTypes (membershipTypeID, membershipLevel, membershipDesc, durationMonths) VALUES ('1', '10', 'Basic', NULL)
INSERT INTO tblMembershipTypes (membershipTypeID, membershipLevel, membershipDesc, durationMonths) VALUES ('2', '20', 'GrandFathered', NULL)
INSERT INTO tblMembershipTypes (membershipTypeID, membershipLevel, membershipDesc, durationMonths) VALUES ('3', '30', 'Premium', 3)
INSERT INTO tblMembershipTypes (membershipTypeID, membershipLevel, membershipDesc, durationMonths) VALUES ('4', '30', 'Premium', 12)
INSERT INTO tblMembershipTypes (membershipTypeID, membershipLevel, membershipDesc, durationMonths) VALUES ('5', '30', 'Premium', NULL)

INSERT INTO tblMemberships (purchasedFor,purchasedBy,dateStart,membershipTypeID,comments) VALUES ('1', '1', '08/30/2004', '3', '')
INSERT INTO tblMemberships (purchasedFor,purchasedBy,dateStart,membershipTypeID,comments) VALUES ('2', '2', '08/30/2004', '2', '')
INSERT INTO tblMemberships (purchasedFor,purchasedBy,dateStart,membershipTypeID,comments) VALUES ('3', '3', '08/30/2004', '2', '')
INSERT INTO tblMemberships (purchasedFor,purchasedBy,dateStart,membershipTypeID,comments) VALUES ('4', '4', '08/30/2004', '1', '')
INSERT INTO tblMemberships (purchasedFor,purchasedBy,dateStart,membershipTypeID,comments) VALUES ('5', '5', '08/30/2004', '4', '')
INSERT INTO tblMemberships (purchasedFor,purchasedBy,dateStart,membershipTypeID,comments) VALUES ('6', '6', '08/30/2004', '5', '')

INSERT INTO tblUserDetails(NameOnline,Password,Active) VALUES ('bob', 'password', '1')

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-01 : 08:53:58

well from data u gave this gets the desired result.
i changed null to 255 for unlimited access. if u need data between dates put it in where>

SELECT u.userID, u.nameOnline, u.active, u.guid,
convert(varchar(10), dateAdd(m, isnull(max(t.durationMonths), 0), max(m.dateStart)), 101) as dateEnd,
max(t.membershipLevel) as membershipLevel
FROM tblUserDetails u
LEFT JOIN tblMemberShips m on m.purchasedFor = u.userID
inner join tblMemberShipTypes t on t.membershipTypeId = m.membershipTypeId
WHERE u.active = 1
group by u.userID, u.nameOnline, u.active, u.guid

EDIT: revised version since u said there could be more entries for single user.

Go with the flow & have fun! Else fight the flow :)

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-09-01 : 15:36:14

spirit1,

We are very close, the query works fine there is just one problem. The data brought back is inconsistent with the other row values.

When using the DML for tblMemberships as below I get the following returned.

1 bob 1 "GUIDVALUE" 02/08/2005 40

This column values of the ENDDATE and MEMBERSHIPLEVEL are not values that appear in the same row in the database. The problem with this is that the row that represents the "40" access level is actually expired. I tried adding a WHERE clause like

AND convert(varchar(10), dateAdd(m, isnull(max(t.durationMonths), 0), max(m.dateStart)), 101) > getDate()

but it errors out, and I'm not sure it will stop the problem of miss aligning the rows in the first place. I think it will work for this situation, but not all.

Thanks again for the continued help, I'm going to be thrilled once this is working 100%

mike123

INSERT INTO tblMemberships (purchasedFor,purchasedBy,dateStart,membershipTypeID,comments) VALUES ('1', '1', '2004-08-08', '1', '')
INSERT INTO tblMemberships (purchasedFor,purchasedBy,dateStart,membershipTypeID,comments) VALUES ('1', '1', '2004-08-08', '4', '')
INSERT INTO tblMemberships (purchasedFor,purchasedBy,dateStart,membershipTypeID,comments) VALUES ('1', '1', '2004-08-08', '7', '')

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-02 : 05:11:30

so if i get you correctly 40 is not supposed to show but 30 is?
as u didn't post the DML for 40 in tblMembershipTypes
i'd have to say u should put your condition in having:

having dateAdd(m, isnull(max(t.durationMonths), 0), max(m.dateStart)) > getDate()

Go with the flow & have fun! Else fight the flow :)

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-09-02 : 19:31:20

Hi Spirit,

Sorry for confusion - I meant it worked as in there where no errors in the query, however the wrong data is being brought back.

I added onto the DML to make sure different queries returned the proper values back, however it didnt.

I am using the following DML

INSERT INTO tblMembershipTypes (membershipTypeID, membershipLevel, membershipDesc, durationMonths) VALUES ('1', '10', 'Basic', NULL)
INSERT INTO tblMembershipTypes (membershipTypeID, membershipLevel, membershipDesc, durationMonths) VALUES ('4', '30', 'GrandFathered', NULL)
INSERT INTO tblMembershipTypes (membershipTypeID, membershipLevel, membershipDesc, durationMonths) VALUES ('7', '40', 'Premium', 3)

INSERT INTO tblMemberships (purchasedFor,purchasedBy,dateStart,membershipTypeID,comments) VALUES ('1', '1', '2004-08-08', '1', '')
INSERT INTO tblMemberships (purchasedFor,purchasedBy,dateStart,membershipTypeID,comments) VALUES ('1', '1', '2004-08-08', '4', '')
INSERT INTO tblMemberships (purchasedFor,purchasedBy,dateStart,membershipTypeID,comments) VALUES ('1', '1', '2002-08-08', '7', '')

the data that is returned is

userID / nameonline / guid / dateEnd / membershipLevel
1 bob 1 "GUIDVALUE" 02/08/2005 40

As we can see, a membership level of "40" is returned. However the only membership with a membership level of "40" in the database is

2003-08-08 (membershiptypeID 7)

this is only a 6 month membership and therefor is expired. We do not want to bring back a level of 40 to the web app to set the user to since it is wrong.

Thanks again for your help and patience :), hope this clears up the final thing.

mike123

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-09-03 : 16:11:40

if anyone can help me out here ... greatly appreciated.. i think its almost done ..

thx again
mike123

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-03 : 16:22:31

you said that membershipTypeID 7 is a 6 months membership... i guess then that it has to be
('7', '40', 'Premium', 6)
instead of
('7', '40', 'Premium', 3)
and u say it is expired.

expired from what date?
because in membership ('1', '1', '2004-08-08', '7', '') 2004-08-08 + 6 (or 3) months still falls
under the category of not expired. getdate() is still smaller.
am i wrong?

Go with the flow & have fun! Else fight the flow :)

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-09-03 : 16:37:48

I apologize for the error in my post. You are correct, however when I replace the value for '2002' as I did in the edit, it then brings back the wrong data.

Thanks once again spirit1

mike123

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-05 : 11:51:05

well i think this should be it this time


SELECT 	u.userID, u.nameOnline, u.active, u.guid, 
	max(t.dateEnd) as dateEnd, max(t.membershipLevel) as membershipLevel
FROM	tblUserDetails u
	inner join (select t1.membershipTypeId, t1.membershipLevel, t2.PurchasedFor,
			convert(varchar(10), dateAdd(m, isnull(t1.durationMonths, 0), t2.dateStart), 101) as dateEnd
		    from tblMemberShipTypes t1
			inner join tblMemberShips t2 on t1.membershipTypeId = t2.membershipTypeId
		    group by t1.membershipTypeId, t1.durationMonths, t2.dateStart, t1.membershipLevel, t2.PurchasedFor
		    having dateAdd(m, isnull(max(t1.durationMonths), 0), max(t2.dateStart)) > getDate() 
 		  ) t on u.userId = t.PurchasedFor
WHERE	u.active = 1
group by u.userID, u.nameOnline, u.active, u.guid

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources