January 26, 2008 at 4:21 pm
Hi
I am currently working on a chart which will let user view there progress by month(up to 12 months).
So it will take the current month and then go back another 11 months(or till it runs out of data to make a month).
I been working on 2 ways but neither of them work 100% since I get stuck and don't know how to solve the remaining part.
Here is my chart table data
Charts Table:
PracticeNum int
UserID uniqueidentifier
Correct numeric(3, 0)
Wrong numeric(3, 0)
AssitanceNeeded numeric(3, 0)
TimeDateStamp smalldatetime
example data
PracticeNum UserID Correct Wrong AssitanceNeeded TimeDateStamp
----------- ------------------------------------ --------------------------------------- --------------------------------------- --------------------------------------- -----------------------
9 4E3E5936-D87D-401E-99AC-04C626A61339 1 1 1 2007-01-01 00:00:00
10 4E3E5936-D87D-401E-99AC-04C626A61339 2 2 2 2007-02-01 00:00:00
11 4E3E5936-D87D-401E-99AC-04C626A61339 3 3 3 2007-03-01 00:00:00
12 4E3E5936-D87D-401E-99AC-04C626A61339 4 4 4 2007-04-01 00:00:00
13 4E3E5936-D87D-401E-99AC-04C626A61339 5 5 5 2007-05-01 00:00:00
14 4E3E5936-D87D-401E-99AC-04C626A61339 6 6 6 2007-06-01 00:00:00
15 4E3E5936-D87D-401E-99AC-04C626A61339 7 7 7 2007-07-01 00:00:00
16 4E3E5936-D87D-401E-99AC-04C626A61339 8 8 8 2007-08-07 00:00:00
17 4E3E5936-D87D-401E-99AC-04C626A61339 9 9 9 2007-09-07 00:00:00
18 4E3E5936-D87D-401E-99AC-04C626A61339 10 10 10 2007-10-07 00:00:00
19 4E3E5936-D87D-401E-99AC-04C626A61339 11 11 11 2007-11-07 00:00:00
20 4E3E5936-D87D-401E-99AC-04C626A61339 12 12 12 2007-12-30 00:00:00
21 4E3E5936-D87D-401E-99AC-04C626A61339 13 13 13 2008-01-01 00:00:00
22 4E3E5936-D87D-401E-99AC-04C626A61339 1 1 1 2008-01-01 00:00:00
23 4E3E5936-D87D-401E-99AC-04C626A61339 1 1 1 2008-01-30 00:00:00
(15 row(s) affected)
So what I want to do is add all the months corrects,wrong and assitanceNeed together and then later display that in a chart so the use can see their progress vs different months.
I first came up with this
SELECT userID, SUM(Correct) AS CORRECT, SUM(Wrong) as Wrong, SUM(AssitanceNeeded) AS AssitanceNeeded, Month(TimeDateStamp), Year(TimeDateStamp)
FROM Charts
WHERE TimeDateStamp
BETWEEN DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
AND
DATEADD(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate()) +1, 0))
GROUP BY UserID, Month(TimeDateStamp), Year(TimeDateStamp)
But this only comes up with the current months stuff. I however don't know how to switch it around and get the last 12 months.
My second try was this
SELECT userID, SUM(Correct) AS CORRECT, SUM(Wrong) as Wrong, SUM(AssitanceNeeded) AS AssitanceNeeded, Month(TimeDateStamp) AS 'Month', Year(TimeDateStamp)AS 'Year'
FROM Charts
GROUP BY UserID, Month(TimeDateStamp), Year(TimeDateStamp)
ORDER BY Year(TimeDateStamp);
This gets all the data of each month and adds it all together but I don't know how to alter it to make it show only the last 12 months.
Please explain your solution in detail since I am a big noob and both of these I had to look up on the net how to do the DATEADD and stuff like that. I understand them more now but I would not be able to figure how to do them on the spot.
Thanks
January 26, 2008 at 6:03 pm
WHERE TimeDateStamp
BETWEEN DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
AND
DATEADD(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate()) +1, 0))
try this instead:
where TimeDateStamp <=
dateadd(month, -12,
dateadd(day,1 - day(getdate()),
convert(char(10),getdate(),120)
)
)
the dateadd expression truncates time from getdate() (convert()), moves back to the start of this month, and then moves back 12 months. adjust it if you only 12 months from the current day or consider the current month the 12th month. by the way, smalldatetimes are only precise to seconds, so adding/subtracting milliseconds does nothing.
January 26, 2008 at 6:18 pm
antonio.collins (1/26/2008)
WHERE TimeDateStamp
BETWEEN DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
AND
DATEADD(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate()) +1, 0))
try this instead:
where TimeDateStamp <=
dateadd(month, -12,
dateadd(day,1 - day(getdate()),
convert(char(10),getdate(),120)
)
)
the dateadd expression truncates time from getdate() (convert()), moves back to the start of this month, and then moves back 12 months. adjust it if you only 12 months from the current day or consider the current month the 12th month. by the way, smalldatetimes are only precise to seconds, so adding/subtracting milliseconds does nothing.
Hi
Your statement seems to be off a bit. Like just looking at one of the returned results I get 16 correct, 16 wrong and 16 assitanceNeeded.
When I look at the data in my table I get 15 across the board.
Also I am still a bit confused by your statement. So I am going to go through it.
where TimeDateStamp >= dateadd(month, -12, dateadd(day,1 - day(getdate()), convert(char(10),getdate(),120)))
the inner dateadd returns(based on today Jan 26)
TimeDateStamp >= dateadd(month, -12, dateadd(day, 25, convert(char(10),getdate(),120))
Thats as far as I can get I don't understand what is happening in your convert. Like I don't know how you got this char(10) or this 120 and how they play into getting the start of the date.
Thanks
January 26, 2008 at 6:27 pm
sorry the comparison should be TimeStamp >= date expression
run these statements:
select dateadd(month, -12,
dateadd(day,1 - day(getdate()),
convert(char(10),getdate(),120)
)
)
select dateadd(day,1 - day(getdate()),
convert(char(10),getdate(),120)
)
select convert(char(10),getdate(),120)
they will produce:
2007-01-01 00:00:00.000
2008-01-01 00:00:00.000
2008-01-26
that and a trip the the documentation for convert() and dateadd() should help you understand the expression.
January 26, 2008 at 6:52 pm
antonio.collins (1/26/2008)
sorry the comparison should be TimeStamp >= date expressionrun these statements:
select dateadd(month, -12,
dateadd(day,1 - day(getdate()),
convert(char(10),getdate(),120)
)
)
select dateadd(day,1 - day(getdate()),
convert(char(10),getdate(),120)
)
select convert(char(10),getdate(),120)
they will produce:
2007-01-01 00:00:00.000
2008-01-01 00:00:00.000
2008-01-26
that and a trip the the documentation for convert() and dateadd() should help you understand the expression.
Ya I looked at it I still don't understand it.
Anyways your expression still looks like it is off.
Here is yours
SELECT userID, SUM(Correct) AS CORRECT, SUM(Wrong) as Wrong, SUM(AssitanceNeeded) AS AssitanceNeeded, Month(TimeDateStamp), Year(TimeDateStamp)
FROM Charts
where TimeDateStamp >=
dateadd(month, -12,
dateadd(day,1 - day(getdate()),
convert(char(10),getdate(),120)
)
)
GROUP BY UserID, Month(TimeDateStamp), Year(TimeDateStamp)
results
userID CORRECT Wrong AssitanceNeeded
------------------------------------ --------------------------------------- --------------------------------------- ---------------------------------------
4E3E5936-D87D-401E-99AC-04C626A61339 1 1 1
4E3E5936-D87D-401E-99AC-04C626A61339 20 20 20
4E3E5936-D87D-401E-99AC-04C626A61339 2 2 2
4E3E5936-D87D-401E-99AC-04C626A61339 3 3 3
4E3E5936-D87D-401E-99AC-04C626A61339 4 4 4
4E3E5936-D87D-401E-99AC-04C626A61339 5 5 5
4E3E5936-D87D-401E-99AC-04C626A61339 6 6 6
4E3E5936-D87D-401E-99AC-04C626A61339 7 7 7
4E3E5936-D87D-401E-99AC-04C626A61339 8 8 8
4E3E5936-D87D-401E-99AC-04C626A61339 9 9 9
4E3E5936-D87D-401E-99AC-04C626A61339 10 10 10
4E3E5936-D87D-401E-99AC-04C626A61339 11 11 11
4E3E5936-D87D-401E-99AC-04C626A61339 12 12 12
(13 row(s) affected)
20 is for jan 2008(this current month). Everything else is from 2007 so 1 in Jan 2007, 2 is Feb 2007 and so on.
So there should be that plus 11 other ones or 12 rows total. Thats 13 rows
Yours again with -11 instead of -12
SELECT userID, SUM(Correct) AS CORRECT, SUM(Wrong) as Wrong, SUM(AssitanceNeeded) AS AssitanceNeeded, Month(TimeDateStamp), Year(TimeDateStamp)
FROM Charts
where TimeDateStamp >=
dateadd(month, -11,
dateadd(day,1 - day(getdate()),
convert(char(10),getdate(),120)
)
)
GROUP BY UserID, Month(TimeDateStamp), Year(TimeDateStamp)
results
userID CORRECT Wrong AssitanceNeeded
------------------------------------ --------------------------------------- --------------------------------------- --------------------------------------- ----------- -----------
4E3E5936-D87D-401E-99AC-04C626A61339 20 20 20 1 2008
4E3E5936-D87D-401E-99AC-04C626A61339 2 2 2 2 2007
4E3E5936-D87D-401E-99AC-04C626A61339 3 3 3 3 2007
4E3E5936-D87D-401E-99AC-04C626A61339 4 4 4 4 2007
4E3E5936-D87D-401E-99AC-04C626A61339 5 5 5 5 2007
4E3E5936-D87D-401E-99AC-04C626A61339 6 6 6 6 2007
4E3E5936-D87D-401E-99AC-04C626A61339 7 7 7 7 2007
4E3E5936-D87D-401E-99AC-04C626A61339 8 8 8 8 2007
4E3E5936-D87D-401E-99AC-04C626A61339 9 9 9 9 2007
4E3E5936-D87D-401E-99AC-04C626A61339 10 10 10 10 2007
4E3E5936-D87D-401E-99AC-04C626A61339 11 11 11 11 2007
4E3E5936-D87D-401E-99AC-04C626A61339 12 12 12 12 2007
(12 row(s) affected)
I then made some changes to yours since like I said I still don't understand the convert.
SELECT userID, SUM(Correct) AS CORRECT, SUM(Wrong) as Wrong, SUM(AssitanceNeeded) AS AssitanceNeeded
FROM Charts
where TimeDateStamp >=
dateadd(month, -11,
DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
)
GROUP BY UserID, month(TimeDateStamp), year(TimeDateStamp)
results
userID CORRECT Wrong AssitanceNeeded
------------------------------------ --------------------------------------- --------------------------------------- ---------------------------------------
4E3E5936-D87D-401E-99AC-04C626A61339 20 20 20
4E3E5936-D87D-401E-99AC-04C626A61339 2 2 2
4E3E5936-D87D-401E-99AC-04C626A61339 3 3 3
4E3E5936-D87D-401E-99AC-04C626A61339 4 4 4
4E3E5936-D87D-401E-99AC-04C626A61339 5 5 5
4E3E5936-D87D-401E-99AC-04C626A61339 6 6 6
4E3E5936-D87D-401E-99AC-04C626A61339 7 7 7
4E3E5936-D87D-401E-99AC-04C626A61339 8 8 8
4E3E5936-D87D-401E-99AC-04C626A61339 9 9 9
4E3E5936-D87D-401E-99AC-04C626A61339 10 10 10
4E3E5936-D87D-401E-99AC-04C626A61339 11 11 11
4E3E5936-D87D-401E-99AC-04C626A61339 12 12 12
(12 row(s) affected)
I have another question can you have 2 where clauses? Like I going to have to change this now into a stored procedure and I will have to do one more filtering.... By userID Or should I just have a && clause?
edit-
Oh I just tried to add another row 01/02/2008 and all of our statements fail on this. Like it still shows up
January 26, 2008 at 8:01 pm
I think I might have got it
SELECT userID, SUM(Correct) AS CORRECT, SUM(Wrong) as Wrong, SUM(AssitanceNeeded) AS AssitanceNeeded
FROM Charts
where TimeDateStamp between
dateadd(month, -11, DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
and
dateadd(ss,-1, DATEADD(mm, DATEDIFF(mm,0,getdate()) + 1, 0))
GROUP BY UserID, month(TimeDateStamp), Year(TimeDateStamp)
January 28, 2008 at 8:02 am
You could do something like this to make it a little cleaner.
SELECT
userID,
SUM(Correct) AS CORRECT,
SUM(Wrong) as Wrong,
SUM(AssitanceNeeded) AS AssitanceNeeded,
Month(TimeDateStamp) AS 'Month',
Year(TimeDateStamp)AS 'Year'
FROM Charts
WHERE TimeDateStamp > DATEADD(MONTH, -12,GETDATE())
GROUP BY UserID, Month(TimeDateStamp), Year(TimeDateStamp)
ORDER BY Year(TimeDateStamp) ASC
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply