By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,364 Members | 1,341 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,364 IT Pros & Developers. It's quick & easy.

Year to Month Sum

P: 3
Hello,
My problem is to summarize
Year to Month Orders
For example I have a table like this :
[Date] ------------------------------------[Orders]
2007-01-31 00:00:00.000------------- 2
2007-02-28 00:00:00.000------------- 8
2007-03-31 00:00:00.000------------- 7
2007-04-30 00:00:00.000------------- 1

and my need is to sum each month's Orders to next one
to Look like this :

2007-01-31 00:00:00.000------------- 2
2007-02-28 00:00:00.000------------- 10
2007-03-31 00:00:00.000------------- 17
2007-04-30 00:00:00.000------------- 18

Thanks in advance .
Mar 17 '08 #1
Share this Question
Share on Google+
4 Replies


deepuv04
Expert 100+
P: 227
Hello,
My problem is to summarize
Year to Month Orders
For example I have a table like this :
[Date] ------------------------------------[Orders]
2007-01-31 00:00:00.000------------- 2
2007-02-28 00:00:00.000------------- 8
2007-03-31 00:00:00.000------------- 7
2007-04-30 00:00:00.000------------- 1

and my need is to sum each month's Orders to next one
to Look like this :

2007-01-31 00:00:00.000------------- 2
2007-02-28 00:00:00.000------------- 10
2007-03-31 00:00:00.000------------- 17
2007-04-30 00:00:00.000------------- 18

Thanks in advance .
Hi,
Use the following query

Expand|Select|Wrap|Line Numbers
  1. select CurMon.Date, isnull(CurMon.Orders,0) + isnull(PrevMon.Orders,0)
  2. from   YearSum as CurMon
  3.        LEFT OUTER JOIN YearSum as NextMon
  4.        ON Month(CurMon.Date)  = Month(NextMon.Date) - 1
  5.        LEFT OUTER JOIN YearSum as PrevMon
  6.        ON Month(CurMon.Date)  = Month(PrevMon.Date) + 1
  7.  
Mar 17 '08 #2

P: 3
Hi,
Use the following query

Expand|Select|Wrap|Line Numbers
  1. select CurMon.Date, isnull(CurMon.Orders,0) + isnull(PrevMon.Orders,0)
  2. from   YearSum as CurMon
  3.        LEFT OUTER JOIN YearSum as NextMon
  4.        ON Month(CurMon.Date)  = Month(NextMon.Date) - 1
  5.        LEFT OUTER JOIN YearSum as PrevMon
  6.        ON Month(CurMon.Date)  = Month(PrevMon.Date) + 1
  7.  
thanks 4 your answer it was exactly what i asked.
and it was not what i wonted in the first place.... Sorry
I want every month to summarize the priv month sum :-| .
Ex: 1 month (1)
2nd month (3)
the record of the 2nd month should be (4) ,
3rd month (5)
the record of the 3nd month should be (9)etc..

thanks
Mar 17 '08 #3

deepuv04
Expert 100+
P: 227
thanks 4 your answer it was exactly what i asked.
and it was not what i wonted in the first place.... Sorry
I want every month to summarize the priv month sum :-| .
Ex: 1 month (1)
2nd month (3)
the record of the 2nd month should be (4) ,
3rd month (5)
the record of the 3nd month should be (9)etc..

thanks
try the following query
use appropriate table names and column names

Expand|Select|Wrap|Line Numbers
  1. SELECT Date1,sum(Orders2)
  2. from(
  3.     SELECT DISTINCT * --,DATEDIFF(MM,T2.DATE2,T1.DATE1)
  4.     FROM 
  5.         ( SELECT Date AS DATE1,Orders  AS ORDERS1
  6.           FROM Table_Name ) AS T1 ,
  7.         ( SELECT Date AS DATE2,Orders AS ORDERS2
  8.           FROM Table_Name ) AS T2 
  9.     WHERE  DATEDIFF(MM,T2.DATE2,T1.DATE1) >= 0
  10.     ) as s
  11. group by date1
  12.  
Mar 17 '08 #4

P: 3
try the following query
use appropriate table names and column names

Expand|Select|Wrap|Line Numbers
  1. SELECT Date1,sum(Orders2)
  2. from(
  3.     SELECT DISTINCT * --,DATEDIFF(MM,T2.DATE2,T1.DATE1)
  4.     FROM 
  5.         ( SELECT Date AS DATE1,Orders  AS ORDERS1
  6.           FROM Table_Name ) AS T1 ,
  7.         ( SELECT Date AS DATE2,Orders AS ORDERS2
  8.           FROM Table_Name ) AS T2 
  9.     WHERE  DATEDIFF(MM,T2.DATE2,T1.DATE1) >= 0
  10.     ) as s
  11. group by date1
  12.  
thank you very much!
Mar 17 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.