how to find number of points that user gets in month and year

Question

0.00/5 (No votes)

See more:

HI,
It is my table can i get result for this .
Each user have points,i have to show user points by number of points got in current month,current year

Thanks.

userId points date
1 5 8/25/2013
1 3 8/16/2013
1 2 8/01/2013
1 2 9/25/2013
1 1 9/08/2013
1 3 8/25/2012
1 4 8/25/2011
1 3 8/25/2012
1 5 9/27/2013
1 3 8/25/2011
1 1 8/25/2011
1 5 8/25/2012

result as

userid --TotalNumberofpointsinCurrrentMonth-- TotalNumberofpointsinCurrrentyear
1 ------- ------- 10 ----------------------------- --------------- 18

Posted 29-Aug-13 0:48am

Member 10239935

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

damodara naidu betha · Answer 1 · 2013-08-29T20:05:00

Hi...

try this

SQL

;WITH ResultCTE As (

SELECT UserId,NULL monthPoints,SUM(Points) YearPoints  FROM YourTableName
WHERE YEAR([Date]) = YEAR(GETDATE())
GROUP BY UserId

UNION ALL 

SELECT UserId,SUM(Points) monthPoints,NULL YearPoints  FROM YourTableName
WHERE YEAR([Date]) = YEAR(GETDATE()) AND MONTH([Date]) = MONTH(GETDATE())
GROUP BY UserId
)

SELECT UserId,SUM(monthPoints) monthPoints,SUM(YearPoints) yearPoints 
FROM ResultCTE GROUP BY UserId

Thanks
Happy Coding.. :)

Hitesh Gohel · Answer 2 · 2013-08-29T01:27:00

Solution 1

Hi Member,

Please try this query for Sql Database

SQL

select SUM(Person.points) as TotalNumberofpointsinCurrrentMonth from Person where MONTH(Person.date)= MONTH(GETDATE()) group by userId

select SUM(Person.points) as TotalNumberofpointsinCurrrentyear from Person where YEAR(Person.date)= YEAR(GETDATE()) group by userId

Posted 29-Aug-13 1:27am

Hitesh Gohel

Comments

Member 10239935 29-Aug-13 8:37am

Hi ,
Thanks.
Can i make as single query because it have to bind in grid
as
this manner,separate queries give two table,
userid --TotalNumberofpointsinCurrrentMonth-- TotalNumberofpointsinCurrrentyear
1 ------- ------- 10 ----------------------------- --------------- 18

Member 10239935 29-Aug-13 10:29am

please use this.its solved
SELECT distinct userId,
(SELECT sum(points)
from tbl_points
WHERE PointsDate between '8/1/2013' and '8/31/2013' and userId= X.UserId) AS CurrentMonthPoints,
(SELECT distinct SUM(points)
from tbl_points
WHERE PointsDate between '1/1/2014' and '12/31/2014' and userId = X.UserId) AS CurrentYearPoints
from tbl_user_performance_points X

sp_suresh · Answer 3 · 2013-08-29T20:18:00

Solution 3

here is another query..
select (SELECT SUM(Person.points) from Person where MONTH(Person.date)= MONTH(GETDATE())) as TotalNumberofpointsinCurrrentMonth,
SUM(Person.points) as TotalNumberofpointsinCurrrentyear from Person
group by userId

you can add where clause for outer query for specified year ....

Posted 29-Aug-13 20:18pm

sp_suresh

Updated 29-Aug-13 20:42pm

v2