Help displaying monthly records for all users

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)

Help displaying monthly records for all users

Author

Topic

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2010-03-17 : 18:28:31

Perhaps someone can assist me with an easier way to query monthly data:

I will describe the project below, but basically I am looking to create a view to load the monthly data for all users in a single query like this:


UserID | Jan | Feb | Mar | Apr ... (headings can be numeric
-------|-----|-----|-----|-----     as they are in the raw data)
 1     |  3  |  3  |  3  |  3  ...
 2     |  3  |  0  |  3  |  3  ...
 3     |  3  |  2  |  3  |  3  ...

Here's the Data I have:


Table: LOG
userid | month  | code
-------|--------|-------
 1     | 1      | 3
 1     | 2      | 3
 1     | 3      | 3
 1     | 4      | 3
 2     | 1      | 3    <-- Note user "2" does not 
 2     | 3      | 3    <-- have an entry for Feb
 2     | 4      | 3    <-- (month 2). This should
 3     | 1      | 3        return a Zero "0".
 3     | 2      | 2
 3     | 3      | 3
 3     | 4      | 3

For each unique user ID in LOG, display the code for each month
For any month there is no code, display a Zero.

- - -

The project is for a contest. It requires generating a number of "tokens" based on the code assigned each month. Code 3 is a win, code 2 (or 0) is a partial loss, and code 1 is a total loss. the first "3" is worth 1 point. The second "3" is worth 3 points a third and subsequent "3"s are worth 5 points. If a user scores a "Code1" or two "Code 2"s in a row, the point assignments reset at 1 for the next "Code3" that they score, though existing points remain.

I can query a list of unique user ID's with codes assigned and then run separate queries for each month for each user ID but that would require executing 1201 queries for each page load. If I can get all the data in one query, and then process the lines individually, it will speed things up tremendously.

Thanks in advance...

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2010-03-17 : 18:30:36

I should add this is for a 1-year period only, so there will not be any need to process more than 12 months

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2010-03-18 : 11:05:50

SOLVED!

Sometimes just revisiting your problem brings the answer! Here's what I came up with:


SELECT DISTINCT a.id AS UserID, 
	COALESCE((SELECT MIN(code) FROM @LOG b WHERE a.id = b.id AND [Month] = 1),0) AS Jan,
	COALESCE((SELECT MIN(code) FROM @LOG b WHERE a.id = b.id AND [Month] = 2),0) AS Feb,
	COALESCE((SELECT MIN(code) FROM @LOG b WHERE a.id = b.id AND [Month] = 3),0) AS Mar,
	COALESCE((SELECT MIN(code) FROM @LOG b WHERE a.id = b.id AND [Month] = 3),0) AS Apr
FROM @LOG a

COALESCE(value,0) converts the NULLs to Zero, and MIN(code) protects me from accidental duplicate entries and gives priority to the lower numbered codes. Plus this executes very quickly. Comments/Tweaks are still welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources