Acumulate the total in the last 12 months

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Acumulate the total in the last 12 months

Author

Topic

santana
Yak Posting Veteran

72 Posts

Posted - 2009-09-25 : 06:31:24

Hi,

Could you help me, please??!!

I created one query with the year-month and the total product by year-month.

I would like to know, how I can accumulate in the last 12 months the products by year-month.

Today I have this: I need this:

1 2008 01 200801

1 2008 02 200802

7 2008 03 200803

3 2008 04 200804

4 2008 05 200805

3 2008 06 200806

9 2008 07 200807

2 2008 08 200808

4 200809 200809

5 200810 200810

0 200811 200811

3 200812 200812

1 200901 200901

2 200902 45 200902 sum between 200801/200902

2 200903 46 200903 sum between 200802/200903

3 200904 48 200904 sum between 200803/200904

2 200905 43 200905 ...

2 200906 42 200906 ...

4 200907 42 200907 ...

4 200908 43 200908 ...

2 200909 36 200909 ...

Thank you!

Regards,

winterh
Posting Yak Master

127 Posts

Posted - 2009-09-25 : 06:46:23

You can use ROLLUP?

Or try
SUBSTRING(Convert (VarChar(8), DateColumnHere, 112) 1, 6) as 'Year & Month'

Or Try
Select DatePart(Format_of_Date_Field_Here, DateColumn)
Group BY Whatever_you_need_it_grouped_like

Or if you want it cummulated then use this link.

http://www.sqlteam.com/article/calculating-running-totals

Search before you post xD

[ /fail at query]

santana
Yak Posting Veteran

72 Posts

Posted - 2009-09-25 : 07:33:00

Hi,

I created the column that show me the year-month 12 months before.

But If I used this in the select, it show me:

year-month qt year-month-12
200902 2 200801

And I don't know how can I accumulate the qt between the months 200801 and 200901 in this row.

If I create a sum(qt) where year-month-12 <= year-month group by year-month

it is Still not working.

show me only:

year-month qt
200902 2

and

I want to see:

year-month qt
200902 45 (1+7+3+4+3+9+2+4+5+3+1+2)

Sorry about my question.
I am starting to working with SQL Server recently.

Thank you for all!

winterh
Posting Yak Master

127 Posts

Posted - 2009-09-25 : 08:47:19

So you want to be able to see the month 12 monthes before any date you enter? And then an acummulated total for the 12 monthes leading up to the month you enter?

You looking for something Like:

SELECT TOP 12(yearMonth), Quantity, Sum(Quantity)
FROM Your_Table

ORDER BY yearMonth

Or something Like:

Declare @CumulativeTotal Table
(
YearPrior DateTime,
Total int
)

Insert into @CumulativeTotal(YearPrior, Total)
Select DateAdd(month, -12, YearMonth),
SUM(Select TOP 12(Quantity) From Your_Table Order By YearMonth)

From Your_Table

Order BY yearMonth

Query Blaa Blaa goes here 9you can work this bit out)

Sorta thing???

Table names and column names would of been nice lol.

I am trying to guage what you are getting at.

[ /fail at query]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-25 : 09:31:55

IIUYC,

select sum(col),cast(cast(year_month as varchar(6))+'01' as datetime) from your_table
group by cast(cast(year_month as varchar(6))+'01' as datetime)

Madhivanan

Failing to plan is Planning to fail

winterh
Posting Yak Master

127 Posts

Posted - 2009-09-25 : 09:55:20

Madhivanan, he wanted the columns to show like this I think

YearMonth Quantity YearMonth-12 12MonthTotal
200801 83 200701 921
200802 75 200702 943

Which is why you need a temp table to hold dateadd(month, -12, YearMonth)

You know what I am trying to do in my queries Madhivanan?

[ /fail at query]

santana
Yak Posting Veteran

72 Posts

Posted - 2009-09-29 : 03:13:58

Yes....is that what I want!!!
How I am doing this???

Thank you!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-29 : 03:35:31

are you using SQL 2005 / 2008 ?

KH
[spoiler]Time is always against us[/spoiler]

santana
Yak Posting Veteran

72 Posts

Posted - 2009-09-29 : 04:37:31

SQL Server 2005.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-29 : 04:41:17

use CROSS APPLY. . .

something like this . . .


select *
from   yourtable t
       cross apply
       (
           select sum(somecol) as total_somecol
           from   yourtable x
           where  x.somedate >= dateadd(month, -12, t.somedate)
           and    x.somedate <= t.somedate
       ) a

KH
[spoiler]Time is always against us[/spoiler]

santana
Yak Posting Veteran

72 Posts

Posted - 2009-09-29 : 06:20:09

I think that I did something wrong.
Because it didn't work. Only duplicate each row 41 times.
In my database, I don't have a Calendar key, I only have the stardate and leavedate in the table.

Could you help me one more time, please?
And sorry ....

select qtd_sum, Calendar_Key
from
[tmsdata].[tmsuser].[CE] as tt
cross apply

(
select sum(qtd)as qtd_sum, Calendar_Key

from(

select count (*) as qtd, Calendar_Key

from [tmsdata].[tmsuser].[CE],tmsuser.TMSEMP,

(
select [Year_Month] as Calendar_Key
from
(
select '2007 01' as [Year_Month], '2007' as [Year Key]
union
select '2007 02' as [Year_Month], '2007' as [Year Key]
union
select '2007 03' as [Year_Month], '2007' as [Year Key]
union
select '2007 04' as [Year_Month], '2007' as [Year Key]
union
select '2007 05' as [Year_Month], '2007' as [Year Key]
union
select '2007 06' as [Year_Month], '2007' as [Year Key]
union
select '2007 07' as [Year_Month], '2007' as [Year Key]
union
select '2007 08' as [Year_Month], '2007' as [Year Key]
union
select '2007 09' as [Year_Month], '2007' as [Year Key]
union
select '2007 10' as [Year_Month], '2007' as [Year Key]
union
select '2007 11' as [Year_Month], '2007' as [Year Key]
union
select '2007 12' as [Year_Month], '2007' as [Year Key]
union
select '2008 01' as [Year_Month], '2008' as [Year Key]
union
select '2008 02' as [Year_Month], '2008' as [Year Key]
union
select '2008 03' as [Year_Month], '2008' as [Year Key]
union
select '2008 04' as [Year_Month], '2008' as [Year Key]
union
select '2008 05' as [Year_Month], '2008' as [Year Key]
union
select '2008 06' as [Year_Month], '2008' as [Year Key]
union
select '2008 07' as [Year_Month], '2008' as [Year Key]
union
select '2008 08' as [Year_Month], '2008' as [Year Key]
union
select '2008 09' as [Year_Month], '2008' as [Year Key]
union
select '2008 10' as [Year_Month], '2008' as [Year Key]
union
select '2008 11' as [Year_Month], '2008' as [Year Key]
union
select '2008 12' as [Year_Month], '2008' as [Year Key]
union
select '2009 01' as [Year_Month], '2009' as [Year Key]
union
select '2009 02' as [Year_Month], '2009' as [Year Key]
union
select '2009 03' as [Year_Month], '2009' as [Year Key]
union
select '2009 04' as [Year_Month], '2009' as [Year Key]
union
select '2009 05' as [Year_Month], '2009' as [Year Key]
union
select '2009 06' as [Year_Month], '2009' as [Year Key]
union
select '2009 07' as [Year_Month], '2009' as [Year Key]
union
select '2009 08' as [Year_Month], '2009' as [Year Key]
union
select '2009 09' as [Year_Month], '2009' as [Year Key]
union
select '2009 10' as [Year_Month], '2009' as [Year Key]
union
select '2009 11' as [Year_Month], '2009' as [Year Key]
union
select '2009 12' as [Year_Month], '2009' as [Year Key]

)as WorkDayCalendar )as tb1

where CONVERT(char(4),DATEPART(year, tmsuser.CE.LEAVEDATE)) + ' ' +
CASE WHEN DATEPART(month,tmsuser.CE.LEAVEDATE) < 10
THEN ('0')
ELSE ('')
END
+ CONVERT(char(2),DATEPART(month, tmsuser.CE.LEAVEDATE)) = Calendar_Key

and tmsuser.TMSEMP.empref = tmsuser.ce.empref

group by Calendar_Key

)as tab2

where Calendar_Key >= CONVERT(char(4),DATEPART(year, DATEADD(MONTH,-12, tt.LEAVEDATE))) + ' ' +
CASE WHEN DATEPART(month,DATEADD(MONTH,-11, tt.LEAVEDATE)) < 10
THEN ('0')
ELSE ('')
END
+ CONVERT(char(2),DATEPART(month, DATEADD(MONTH,-11, tt.LEAVEDATE)))

and

Calendar_Key <= CONVERT(char(4),DATEPART(year, tt.LEAVEDATE)) + ' ' +
CASE WHEN DATEPART(month,tt.LEAVEDATE) < 10
THEN ('0')
ELSE ('')
END
+ CONVERT(char(2),DATEPART(month, tt.LEAVEDATE))

group by Calendar_Key
)

as tab_total

order by 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 06:26:04

what do you want to show against each month?

santana
Yak Posting Veteran

72 Posts

Posted - 2009-09-29 : 06:34:45

I only need to show the accumulation by month.
In the example below I need to show: YearMonth and 12MonthTotal (200801 921 / 200802 943 ...)

YearMonth Quantity YearMonth-12 12MonthTotal
200801 83 200701 921
200802 75 200702 943

winterh
Posting Yak Master

127 Posts

Posted - 2009-09-29 : 06:54:11

http://www.sqlteam.com/article/calculating-running-totals
use the link. I already posted it to you once.

[ /fail at query]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 07:00:33

what does 12 month total represent? total in 2007?

santana
Yak Posting Veteran

72 Posts

Posted - 2009-09-29 : 07:23:47

If the month is 200901, represent the sum between 200801 and 200901.
If the month is 200902, represent the sum between 200802 and 200902.
If the month is 200903, represent the sum between 200803 and 200903.

...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 07:35:29

something like:-


SELECT t.DisplayPeriod,
t.TotalQty,
t1.12MonthQty
FROM
(
SELECT DATENAME(yy,DATEADD(mm,DATEDIFF(mm,0,datefield,0)) + RIGHT('0'+ CAST(DATEPART(mm,DATEADD(mm,DATEDIFF(mm,0,GETDATE(datefield),0)) AS varchar(2)),2) AS DisplayPeriod,
DATEADD(mm,DATEDIFF(mm,0,datefield,0) AS MonthDate,
SUM(Qty) AS TotalQty
FROM YourTable
GROUP BY DATEADD(mm,DATEDIFF(mm,0,datefield,0)
)t
CROSS APPLY
(SELECT SUM(Qty) AS 12MonthQty
 FROM YourTable
WHERE datefield > = DATEADD(mm,-1,DATEADD(yy,-1,MonthDate))
AND datefield <MonthDate
)t1
ORDER BY t.MonthDate

santana
Yak Posting Veteran

72 Posts

Posted - 2009-09-29 : 07:54:28

Is this correct DATEDIFF(mm,0,datefield,0) ?

Because I received the msg: The datediff function requires 3 argument(s)

I chenged for that:

DATENAME(yy,DATEADD(mm,DATEDIFF(mm,0,STARTDATE))
+ RIGHT('0'+ CAST(DATEPART(mm,DATEADD(mm,DATEDIFF(mm,0,GETDATE(STARTDATE))) AS varchar(2)),2) AS DisplayPeriod,

And now I received the msg:
The dateadd function requires 3 argument(s)

Thank you!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 07:58:58

it should be DATEADD(mm,DATEDIFF(mm,0,datefield),0)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 08:04:48

i missed some braces


SELECT t.DisplayPeriod,
t.TotalQty,
t1.12MonthQty
FROM
(
SELECT DATENAME(yy,DATEADD(mm,DATEDIFF(mm,0,datefield),0)) + RIGHT('0'+ CAST(DATEPART(mm,DATEADD(mm,DATEDIFF(mm,0,datefield),0)) AS varchar(2)),2) AS DisplayPeriod,
DATEADD(mm,DATEDIFF(mm,0,datefield),0) AS MonthDate,
SUM(Qty) AS TotalQty
FROM YourTable
GROUP BY DATEADD(mm,DATEDIFF(mm,0,datefield),0)
)t
CROSS APPLY
(SELECT SUM(Qty) AS 12MonthQty
 FROM YourTable
WHERE datefield > = DATEADD(mm,-1,DATEADD(yy,-1,MonthDate))
AND datefield <MonthDate
)t1
ORDER BY t.MonthDate

winterh
Posting Yak Master

127 Posts

Posted - 2009-09-29 : 08:58:52

Crazy

[ /fail at query]

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources