How to Retrieve Month to Date Data???

Question

How to Retrieve Month to Date Data???

Salvatore Conti Jr.

Ten Centuries

Points: 1155
More actions
March 22, 2005 at 6:40 am

#163957

I have a table that is building each day with data...I need a way to write an SQL statement that will show the Month to Date data that is in the table...
I was hoping that someone has come accross this issue before??
My table has a date column.
Thank you.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 1

If I understand your question correctly you want to be able to agregate all of the monthly figures. Something like below?

select datepart(y, date) as the year, datepart(mm, date) as theMonth, sum(amount) as thetotal

from yourtable

group by datepart(y, date), datepart(mm, date)

order by datepart(y, date), datepart(mm, date)

That will give you a total for the year and the month, all you have to do is substitute your columns for date and amount, and rename the table.

If this wasn't what you wanted to do then please repost and I'll see if I can help you further.

Mike

Salvatore Conti Jr. Ten Centuries Points: 1155 More actions · Answer 2

I believe this is what I am looking for...

You see..I have a JSP that is pulling data from a table on my SQL Server...as it stands I have the JSP showing the day to day data...

What I need to do now is show the Month to Date data...

From the code you provided I should be able to replace the date field with a date that I will pass to it from the original JSP.

I will try it out and see what happens....THANK YOU!

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 3

Mike Metcalf

SSCrazy Eights

Points: 8763

March 22, 2005 at 7:54 am

#548196

you're welcome, glad I could help you out.

Salvatore Conti Jr. Ten Centuries Points: 1155 More actions · Answer 4

Mike,

It did not work...could you look at my select statment and see where I am wrong?

select datepart(mm, Col001) as theMonth, substring(convert(varchar,cast(SUM(Col003) as money),1),1,charindex('.',convert(varchar,cast(SUM(Col003) as money),1))-1), substring(convert(varchar,cast(SUM(Col004) as money),1),1,charindex('.',convert(varchar,cast(SUM(Col004) as money),1))-1), CAST(((Sum(Col005)/Sum(Col003))*100)as decimal(5,1)), CAST(((Sum(Col008)/Sum(Col004))*100) as int), CAST(ROUND((Sum(Col010)/Sum(Col004)),0) as int), CAST(ROUND((Sum(Col007)/Sum(Col004)),0) as int), Sum(Col009) FROM cms WHERE Col002 = '65' And Col001 = '3/10/2005' group by datepart(mm, Col001) order by datepart(mm, Col001)

Col001 is the coloumn with the dates...

Thank you..

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 5

thats a bit of a monster

I'll take a look and see what I can do

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 6

rather than communicate via the forum can you send me your email address via PM.

I have a couple of questions and it would be easier to do it that way.

Salvatore Conti Jr. Ten Centuries Points: 1155 More actions · Answer 7

How do you send a pm?

I went to the button, but I did not see an option to send a private message...

Salvatore Conti Jr. Ten Centuries Points: 1155 More actions · Answer 8

Mike,

Were you able to see my PM...I am very interested in what you think I need to do to make my SELECT statement work???

Thank you.

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 9

got your PM but couldnt reach your email, sent the questions via PM instead