RESOVED! How to average 2 of 3 scores depending on

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

General SQL Server Forums

New to SQL Server Programming

RESOVED! How to average 2 of 3 scores depending on

Author

Topic

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2011-07-28 : 12:04:09

Any ideas how I could average a individual's last 2 scores ignoring only the last score IF it's a zero?

I have a system where scores are recorded quarterly. DATEPART(q,date) returns teh correct quarter (1-4) based on the date

I need to average the last 2 scores recorded in a 6 month period but some 6 month periods have 3 quarters. (August has Q1-Q3)

I need
Q1=100 Q2=100 Q3=0 -- I need to average Q1 and Q2 (Result 100)
Q1=100 Q2=100 Q3=50 -- I need to average Q2 and Q3 (Result 75)
Q1=0 Q2=0 Q3=100 -- I need to average Q2 and Q3 (Result 50)

Any ideas?

Thanks!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-28 : 12:48:42

The query one would write depends on how the data is stored and whether there are only three possible quarters - Q1, Q2, and Q3. If you can post the table schema and sample data people on the forum may be able to offer more specific suggestions, but based on what I know from your description, you should be able to do something like this:


SELECT
	CASE 
		WHEN Q3 = 0 THEN 0.5*(Q1+Q2)
		WHEN Q2 = 0 THEN 0.5*(Q1+Q3)
		ELSE 0.5*(Q2+Q3)
	END

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2011-07-28 : 13:36:55

Original Data:


CREATE TABLE #userdata (
  userid INT,
  date DATETIME,
  score INT
)

INSERT INTO #userdata (userid, date, score) VALUES (1,'1/1/2011',0)
INSERT INTO #userdata (userid, date, score) VALUES (1,'2/1/2011',80)
INSERT INTO #userdata (userid, date, score) VALUES (1,'3/1/2011',85)
INSERT INTO #userdata (userid, date, score) VALUES (1,'4/1/2011',0)
INSERT INTO #userdata (userid, date, score) VALUES (1,'5/1/2011',95)
INSERT INTO #userdata (userid, date, score) VALUES (1,'6/1/2011',0)
INSERT INTO #userdata (userid, date, score) VALUES (1,'7/1/2011',0)
INSERT INTO #userdata (userid, date, score) VALUES (1,'8/1/2011',100)

The correct score is the last one entered in a given quarter. In the data above, Q1 (Jab-Mar) the score is 85. The 80 would be ignored/overwritten.

I wrote 2 functions to help but maybe someone has a better way?:

QScore([date],[userid])
QScore('1/1/2011', 1)

returns the last entered score in the quarter faling with in the given date. In this example, it returns 85.

QuarterDate([Quarter],[year])
QuarterDate(2,2011)

will return the first day in Quarter 1, 2011 (In this example, it returns 4/1/2011)

Given a user ID and specific date, I need to determine the average of the last 2 scores with in a rolling 6 month period.

This month, 7/1 the rolling scores are in Q1(Jan, Feb, Mar) and Q2(Apr, May, June).

Next month, 8/1 the rollings cores are in Q1(Feb, Mar), Q2 (Apr-Jun) and Q3 (July only)

If there is not score for Q3 yet, then Q1 and Q2 should be averaged.
If there is a score in Q3, then Q2 and Q3 need to be averaged.

If there are no scores at all during a quarter, the "quarter" score should be a 0 and that should be averaged in. If there's a score in all three quarters (if 7/1/2011 had a 100), then just the last two should be averaged.

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2011-07-28 : 13:46:03

Here's the QuarterDate function:


CREATE FUNCTION QuarterDate (@q INTEGER, @y VARCHAR(4)) 
RETURNS datetime
AS 
BEGIN
  DECLARE @startDate varchar(20)
  SELECT @startDate = CASE @q 
    WHEN 1 THEN '1/1/' + @y
    WHEN 2 THEN '4/1/' + @y
    WHEN 3 THEN '7/1/' + @y
    WHEN 4 THEN '10/1/' + @y
    ELSE 'Error'
    END 
  RETURN CONVERT(datetime,@startDate)
END

and the QScore Function:


CREATE FUNCTION QScore(@date DATETIME, @userid INT)
RETURNS INTEGER
AS
BEGIN
  DECLARE @score INTEGER
  DECLARE @quarter INTEGER
  SET @quarter - DATEPART(q,@date)
  SELECT TOP 1 @score = CASE WHEN score IS NULL THEN '0' ELSE score END 
  FROM #userdata 
  WHERE userid = @userid 
    AND [date] >= dbo.QuarterDate(@quarter,year(@date)) 
    AND [date] < DATEADD(m,3,dbo.QuarterDate(@quarter,year(@date))) 
    AND score > 0
  ORDER BY [date] DESC

  RETURN CASE WHEN @score IS NULL THEN 0 ELSE @score END
END

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2011-07-28 : 14:45:27

Sunitabeck,

You had me look at the problem in a different way which greatly simplified things. I resolved the issue with the following code:


DECLARE @q3 FLOAT
DECLARE @q2 FLOAT
DECLARE @q1 FLOAT

SET @q1 = QScore(QuarterDate(DatePart(q,DateAdd(m,-7,@date)),DatePart(year,DateAdd(m,-7,@date))),@userid)
SET @q2 = QScore(QuarterDate(DatePart(q,DateAdd(m,-4,@date)),DatePart(year,DateAdd(m,-4,@date))),@userid)
SET @q3 = QScore(QuarterDate(DatePart(q,DateAdd(m,-1,@date)),DatePart(year,DateAdd(m,-1,@date))),@userid)

SELECT
 CASE 
  WHEN @date = QuarterDate(DatePart(q,DateAdd(m,0,@date)),DatePart(year,DateAdd(m,0,@date)))
  THEN (@q2 + @q3)*.5
  ELSE 
	CASE 
	  WHEN (@q3 = 0) 
	  THEN (@q1 + @q2)*.5 
	  ELSE (@q2 + @q3)*.5 
	END
  END

When the current date was the start of a new quarter, no need to go back three quarters. When I did have to go back three quarters, check if there's a score for Q3. If not, use the first 2. So much simpler than what I was trying to do!

Thank you so much!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-28 : 17:29:50

quote:
Originally posted by Jaypoc

Sunitabeck,

You had me look at the problem in a different way which greatly simplified things. I resolved the issue with the following code:

DECLARE @q3 FLOAT
DECLARE @q2 FLOAT
DECLARE @q1 FLOAT

SET @q1 = QScore(QuarterDate(DatePart(q,DateAdd(m,-7,@date)),DatePart(year,DateAdd(m,-7,@date))),@userid)
SET @q2 = QScore(QuarterDate(DatePart(q,DateAdd(m,-4,@date)),DatePart(year,DateAdd(m,-4,@date))),@userid)
SET @q3 = QScore(QuarterDate(DatePart(q,DateAdd(m,-1,@date)),DatePart(year,DateAdd(m,-1,@date))),@userid)

SELECT
CASE
WHEN @date = QuarterDate(DatePart(q,DateAdd(m,0,@date)),DatePart(year,DateAdd(m,0,@date)))
THEN (@q2 + @q3)*.5
ELSE
CASE
WHEN (@q3 = 0)
THEN (@q1 + @q2)*.5
ELSE (@q2 + @q3)*.5
END
END

When the current date was the start of a new quarter, no need to go back three quarters. When I did have to go back three quarters, check if there's a score for Q3. If not, use the first 2. So much simpler than what I was trying to do!

Thank you so much!

I didn't do much for you, but glad you were able to make it work

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-07-28 : 20:30:48

Every SQL forum or newsgroup expects that you will post DDL, sample data and clear specifications which might might include output. This is explained in the FAQ section. We can not read minds; what would we need to know to do your job for you?

Tables must have keys and should have DRI, constraints, and all the basic features of a schema. You should know use ISO-8601 Standards for temporal data, avoid needless dialect, basic data modeling and use ISO-11179 Standards for data element names. Please tell us if you can change the DDL.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources