455,364 Members | 1,341 Online
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
4 Replies

 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 select CurMon.Date, isnull(CurMon.Orders,0) + isnull(PrevMon.Orders,0) from   YearSum as CurMon        LEFT OUTER JOIN YearSum as NextMon        ON Month(CurMon.Date)  = Month(NextMon.Date) - 1        LEFT OUTER JOIN YearSum as PrevMon        ON Month(CurMon.Date)  = Month(PrevMon.Date) + 1   Mar 17 '08 #2

 P: 3 Hi, Use the following query Expand|Select|Wrap|Line Numbers select CurMon.Date, isnull(CurMon.Orders,0) + isnull(PrevMon.Orders,0) from   YearSum as CurMon        LEFT OUTER JOIN YearSum as NextMon        ON Month(CurMon.Date)  = Month(NextMon.Date) - 1        LEFT OUTER JOIN YearSum as PrevMon        ON Month(CurMon.Date)  = Month(PrevMon.Date) + 1   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

 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 SELECT Date1,sum(Orders2) from(     SELECT DISTINCT * --,DATEDIFF(MM,T2.DATE2,T1.DATE1)     FROM          ( SELECT Date AS DATE1,Orders  AS ORDERS1           FROM Table_Name ) AS T1 ,         ( SELECT Date AS DATE2,Orders AS ORDERS2           FROM Table_Name ) AS T2      WHERE  DATEDIFF(MM,T2.DATE2,T1.DATE1) >= 0     ) as s group by date1   Mar 17 '08 #4

 P: 3 try the following query use appropriate table names and column names Expand|Select|Wrap|Line Numbers SELECT Date1,sum(Orders2) from(     SELECT DISTINCT * --,DATEDIFF(MM,T2.DATE2,T1.DATE1)     FROM          ( SELECT Date AS DATE1,Orders  AS ORDERS1           FROM Table_Name ) AS T1 ,         ( SELECT Date AS DATE2,Orders AS ORDERS2           FROM Table_Name ) AS T2      WHERE  DATEDIFF(MM,T2.DATE2,T1.DATE1) >= 0     ) as s group by date1   thank you very much! Mar 17 '08 #5