Need help with a 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

General SQL Server Forums

New to SQL Server Programming

Need help with a query

Author

Topic

royc
Yak Posting Veteran

53 Posts

Posted - 2010-05-12 : 17:14:58

Hello,

I have a table that keeps a record of points given to users in a forum.
The table has many fields but the ones relevant for my case are these three fields:

UserID - The user unique ID
Points - The number of points given, an integer between 1 to 10.
Timestamp - The time-stamp in milliseconds that the points were awarded, for example 1273698569

Of course each user can have one or more entries in this table, one for each time points have been awarded to him.

I need to come up with a query that will generate a list of UserIDs who received a sum of 250 points or more between the beginning of the year and 10 days earlier than the current date, so for today the time limit will be: 1/1/2010 - 2/5/2010.

Thank you in advance for your help.

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-12 : 17:42:48

Use this:


SELECT  UserID, SUM(Points) AS TotalPoints
  FROM UserPoints AS UP
 WHERE DATEADD(MILLISECOND, 1273698569, '20100101') >= CAST('20100101' AS DATETIME)
   AND DATEADD(MILLISECOND, 1273698569, '20100101') < DATEADD(DAY, -9, DATEADD(DAY, DATEDIFF(DAY, '20090101', GETDATE()), '20090101'))
 GROUP BY UserID
HAVING SUM(Points) >= 250

You did not specify from which start point you count your timestamp, So I assumed that it start from the first of the year '20100101'.
Another assumption was the start of year is '20100101'. But if you wish you can calculate it based on GETDATE().

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-13 : 10:35:59

usually base date considered is from '19000101'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

royc
Yak Posting Veteran

53 Posts

Posted - 2010-05-14 : 11:24:17

quote:
Originally posted by malpashaa

Use this:

SELECT UserID, SUM(Points) AS TotalPoints
FROM UserPoints AS UP
WHERE DATEADD(MILLISECOND, 1273698569, '20100101') >= CAST('20100101' AS DATETIME)
AND DATEADD(MILLISECOND, 1273698569, '20100101') < DATEADD(DAY, -9, DATEADD(DAY, DATEDIFF(DAY, '20090101', GETDATE()), '20090101'))
GROUP BY UserID
HAVING SUM(Points) >= 250

You did not specify from which start point you count your timestamp, So I assumed that it start from the first of the year '20100101'.
Another assumption was the start of year is '20100101'. But if you wish you can calculate it based on GETDATE().

Thanks but I was looking for something more dynamic in the WHERE clause.
The query should identify the current year on the server, rollback 10 days and use this as a reference for the period.
So, suppose it's 10/4/2010 today, the WHERE clause should check between 1/1/2010-1/4/2010
However, suppose it's 5/1/2010 today, then the WHERE clause should check between 1/1/2009-26/12/2010 since when rolling 10 days backwards it is still 2009.

And I believe our base date is indeed 19000101

Rgds,
Roy

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-14 : 11:37:33

[code]WHERE <datefield> BETWEEN DateAdd(year,DateDiff(year,0,dateadd(day,-10,@mydate)),0) AND dateadd(day,-10,dateadd(d, datediff(d, 0, @mydate), 0))[/code]
Replace @mydate with your date value.

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-14 : 11:47:27

Based on your clarifications then you need this:


SELECT  UserID, SUM(Points) AS TotalPoints
  FROM UserPoints AS UP
 WHERE DATEADD(MILLISECOND, Timestamp, '19000101') >= DATEADD(YEAR, DATEDIFF(YEAR, '19000101', DATEADD(DAY, -10, GETDATE())), '19000101')
   AND DATEADD(MILLISECOND, Timestamp, '19000101') < DATEADD(DAY, -9, DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101'))
 GROUP BY UserID
HAVING SUM(Points) >= 250

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-14 : 12:55:19

quote:
Originally posted by royc

The query should identify the current year on the server, rollback 10 days and use this as a reference for the period.
So, suppose it's 10/4/2010 today, the WHERE clause should check between 1/1/2010-1/4/2010
However, suppose it's 5/1/2010 today, then the WHERE clause should check between 1/1/2009-26/12/2010 since when rolling 10 days backwards it is still 2009.

And I believe our base date is indeed 19000101

Rgds,
Roy

how did you get 26/12/2010 as end date in second case?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

royc
Yak Posting Veteran

53 Posts

Posted - 2010-05-14 : 13:01:00

quote:
Originally posted by visakh16

quote:
Originally posted by royc

The query should identify the current year on the server, rollback 10 days and use this as a reference for the period.
So, suppose it's 10/4/2010 today, the WHERE clause should check between 1/1/2010-1/4/2010
However, suppose it's 5/1/2010 today, then the WHERE clause should check between 1/1/2009-26/12/2009 since when rolling 10 days backwards it is still 2009.

And I believe our base date is indeed 19000101

Rgds,
Roy

how did you get 26/12/2010 as end date in second case?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

It is 26/12/2009 not 2010. Where do you see 26/12/2010?

royc
Yak Posting Veteran

53 Posts

Posted - 2010-05-14 : 13:02:11

quote:

Originally posted by malpashaa

Based on your clarifications then you need this:


SELECT  UserID, SUM(Points) AS TotalPoints
  FROM UserPoints AS UP
 WHERE DATEADD(MILLISECOND, Timestamp, '19000101') >= DATEADD(YEAR, DATEDIFF(YEAR, '19000101', DATEADD(DAY, -10, GETDATE())), '19000101')
   AND DATEADD(MILLISECOND, Timestamp, '19000101') < DATEADD(DAY, -9, DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101'))
 GROUP BY UserID
HAVING SUM(Points) >= 250

I'm getting an error "Missing delimiter: )" on this query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-14 : 13:15:17

sorry i'm not getting any error. can you recheck?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources