help with query - total count - 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

Transact-SQL (2000)

help with query - total count

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-07-09 : 00:18:04

I have two tables

tblUserDetails
userID,date

tblMemberships
userID,membershipType,dateend

Each user has a corresponding row in tblMemberships. It's possible that some users have more than one. There are 4 different types of membership.

I want to run a query that returns the number of each types of memberships currently. By "current" I mean where the enddate of their membership is greater than today's date.

Something to the effect of what is below.

level1, level2, level3, level4

123, 232, 366, 474

Any help on this is appreciated. I think i've done something similar to this in the past but I can't remember the approach.

Thanks again
mike123

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-09 : 00:35:25

SELECT
COUNT(CASE WHEN membershipType = 1 THEN 1 ELSE 0 END) AS Type1,
COUNT(CASE WHEN membershipType = 2 THEN 1 ELSE 0 END) AS Type2,
COUNT(CASE WHEN membershipType = 3 THEN 1 ELSE 0 END) AS Type3,
COUNT(CASE WHEN membershipType = 4 THEN 1 ELSE 0 END) AS Type4
FROM tblMemberships
WHERE dateend > GETDATE()

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-07-09 : 01:01:00

Hi derek,

I'm running that code across a table with just 1 row so far.

I am getting results that say 1 for each membership type. What could be causing this data to be off ?

Note: Now that I have 2 rows in my data its bringing 2's across the board for each column

Thanks again
mike123

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-09 : 01:14:44

I'm an idiot. Replace COUNT with SUM. GHEESH!!!!

We need to get one of these where he's shooting himself.

I really need a vacation.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-07-09 : 01:53:33

Hi Derek,

Closer but still wrong data :) .. It's pretty late here I usually just blame it on that. If its early wherever you are then its just too early! lol

I have 5 rows now and the data should come back

1,2,1,1

however I have

0,1,1,1

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-07-09 : 09:30:24

How about some DDL and DML with your data?

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-07-09 : 18:10:11

Here's all the info I have

(TBLMEMBERSHIPS)

userID / dateStart / dateEnd / membershipLevel

500 2001-01-01 2005-05-05 10
500 2001-11-11 2005-05-05 15
500 2001-01-01 2005-05-05 20
500 2002-01-01 2004-05-05 20
500 2002-01-01 2003-05-05 5

This SPROC

CREATE PROCEDURE dbo.select_membershipBreakDown

AS SET NOCOUNT ON

SELECT
SUM(CASE WHEN membershipType = 5 THEN 1 ELSE 0 END) AS 5,
SUM(CASE WHEN membershipType = 10 THEN 1 ELSE 0 END) AS 10,
SUM(CASE WHEN membershipType = 15 THEN 1 ELSE 0 END) AS 15,
SUM(CASE WHEN membershipType = 20 THEN 1 ELSE 0 END) AS 20

FROM tblMemberships

WHERE dateEnd > GETDATE()

Returns this data

5 / 10 / 15/ 20
0,1,1,1

Thanks again
mike123

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-07-09 : 18:38:26

Your filter criteria

WHERE dateEnd > GETDATE()

is preventing 2 records:

500 2002-01-01 2004-05-05 20
500 2002-01-01 2003-05-05 5

from being included.

The query is correct.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-07-09 : 18:44:36

silly me

thanks Ehorn and derrick!

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-07-09 : 19:16:39

No worries..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources