January 13, 2011 at 6:47 am
Hi there
I have a report which works fine :
select 'Openstaand' as 'Openstaand', year(DCLE.[Posting Date]) as Jaar, month(DCLE.[Posting Date]) as Maand, DCLE.Amount as Bedrag from dbo.[FIN$Detailed Cust_ Ledg_ Entry] as DCLE
where DCLE.[Posting Date] <= @date
and DCLE.Amount > 0
order by year(DCLE.[Posting Date]), month(DCLE.[Posting Date])
but this only results in one sum.
Now I need this info for the last year per month.
So eg.
@date = 30/11/2010
The result is 1.000 €
but in my report I need
30/11/2009 31/10/2009 ..... 31/10/2010 30/11/2010
999 € 1150 € 2500 € 1000 €
How to coop with this one ?
Kind regards
JV
January 13, 2011 at 7:24 am
Hi JV,
It is unclear to me where you do the sum, but does the solution not lie in: GROUP BY year(DCLE.[Posting Date]) as Jaar, month(DCLE.[Posting Date]) as Maand ?
Kind regards,
Linda
January 13, 2011 at 7:27 am
hi there
no, strangely enough it doesn't work that way.
I have found now a solution like this :
sum(case when DCLE.[Posting Date] <= @date then DCLE.Amount Else 0 end) as M1 ,
sum(case when DCLE.[Posting Date] <= dateadd("month",-1,@date) then DCLE.Amount Else 0 end) as M2 ,
JV
January 13, 2011 at 7:55 am
JV,
You have to be carefull as doing so you will not end up with the exact last day of each month. See following code sample:
DECLARE @date datetime
SELECT @date = '2010-02-28'
SELECT M0 = dateadd("month", -1, @date)
,M1 = dateadd("month", 1, @date)
I guess what you try to achieve is a cumulative sum per month. Within reporting services you can achieve this using the RunningValue function. With a bit more work you can achieve this in your sql as well. The following code sample will illustrate:
DECLARE @date datetime
SELECT @date = '2010-02-28'
SELECT @date = CONVERT(datetime, CONVERT(varchar, year(@date)) + '-' + CONVERT(varchar, month(@date)) + '-01')
CREATE TABLE #dates_past_12_months (last_day_of_month datetime)
INSERT INTO #dates_past_12_months
SELECT dateadd(day, -1, dateadd(month, 1, @date))
UNION SELECT dateadd(day, -1, dateadd(month, 0, @date))
UNION SELECT dateadd(day, -1, dateadd(month, -1, @date))
UNION SELECT dateadd(day, -1, dateadd(month, -2, @date))
UNION SELECT dateadd(day, -1, dateadd(month, -3, @date))
UNION SELECT dateadd(day, -1, dateadd(month, -4, @date))
UNION SELECT dateadd(day, -1, dateadd(month, -5, @date))
UNION SELECT dateadd(day, -1, dateadd(month, -6, @date))
UNION SELECT dateadd(day, -1, dateadd(month, -7, @date))
UNION SELECT dateadd(day, -1, dateadd(month, -8, @date))
UNION SELECT dateadd(day, -1, dateadd(month, -9, @date))
UNION SELECT dateadd(day, -1, dateadd(month, -10, @date))
UNION SELECT dateadd(day, -1, dateadd(month, -11, @date))
--SELECT * from #dates_past_12_months
select 'Openstaand op ' + CONVERT(varchar, last_day_of_month, 106) as 'Openstaand'
, sum(DCLE.Amount) as Bedrag
from dbo.[FIN$Detailed Cust_ Ledg_ Entry] as DCLE
JOIN #dates_past_12_months dt ON DCLE.[Posting Date] <= dt.last_day_of_month
WHERE DCLE.Amount > 0
order by year(DCLE.[Posting Date]), month(DCLE.[Posting Date])
DROP TABLE #dates_past_12_months)
Hope this helps.
Kind regards,
Linda
January 14, 2011 at 1:48 am
Linda
thx for reply.
I made the simular but without creating a tabel, so immediately in a select statement. I will try yours though.
But you've mentioned the running value in reporting services. I'm curious how this works...
Could you give an exemple ?
Many thanx
JV
January 17, 2011 at 1:24 am
Hi JV,
As attachment you find a sample rdl report for RunningValue. I guess this is self-explanatory but in case you have questions please get don't hesitate to ask.
Kind regards,
Linda
January 18, 2011 at 5:58 am
many thanx
JV
January 18, 2011 at 6:01 am
Welcome
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply