Calculate Running Total by Month Interval

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

Other Forums

MS Access

Calculate Running Total by Month Interval

Author

Topic

blinton25
Yak Posting Veteran

90 Posts

Posted - 2003-09-18 : 15:42:52

Hello,

I want to create a report looking like this:

[Code]

January-February January-March January -April

Sales Sales Sales

$3000 $5000 $9000

and so on until the end of the year. Is this possible?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-18 : 15:53:31

sure ....

this is Access, right?

I will asume you want to show total sales by Employee since you gave no information ....

1. create your first query, call it "Sales1" or something like that:


SELECT Employee, year(saledate) as Year, Month(SaleDate) as Month,
  sum(Amount) as Amount
FROM
  Sales
GROUP BY Employee, year(saledate), month(SaleDate)

2. then, write a query of that query doing the cross tab / running total with IIF's :


SELECT Year,Employee,
  SUM(iif(Month<=1,Sales,0)) as Jan,
  SUM(iif(Month<=2,Sales,0)) as Feb,
  SUM(iif(Month<=3,Sales,0)) as Mar,
  ...
  SUM(Sales) as Dec
FROM
  Sales1
GROUP BY Year, Employee

3. just filter your results or add a report group by Year, add the fields Jan-Dec to your report, and you are good to go.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-18 : 16:33:22

I think you just hurt him...

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

blinton25
Yak Posting Veteran

90 Posts

Posted - 2003-09-19 : 08:33:17

Hello,

Understood, thanks. A variation on the above concept, this query:

 
SELECT Format([F_ARRV_DAT],"m") AS MonthOrder, 
Format([F_ARRV_DAT],"mmmm") AS [Month], [Final_Clean Tourists Query].[REGIONS 2], 
Count
(IIf(DatePart('yyyy',[F_ARRV_DAT],1,0)=PreviousYear,[Final_Clean Tourists Query].[TRAV-KEY])) 
AS y2002,
Count
(IIf(DatePart('yyyy',[F_ARRV_DAT],1,0)=CurrentYear,[Final_Clean Tourists Query].[TRAV-KEY])) 
AS y2003
FROM [Final_Clean Tourists Query]
WHERE (((DatePart('yyyy',[F_ARRV_DAT],1,0))=[PreviousYear] 
Or 
(DatePart('yyyy',[F_ARRV_DAT],1,0))=[CurrentYear]))
GROUP BY Format([F_ARRV_DAT],"m"), 
Format([F_ARRV_DAT],"mmmm"), [Final_Clean Tourists Query].[REGIONS 2]
HAVING ((([Final_Clean Tourists Query].[REGIONS 2])<>'' 
And ([Final_Clean Tourists Query].[REGIONS 2])<>'Other'
And ([Final_Clean Tourists Query].[REGIONS 2])<>'Not stated'));

returns a result set looking like:


MonthOrder Month REGIONS 2 y2002 y2003
  5          May   USA      668   692
  5          May   CANADA   243   361

I would like to return the following:


 Month           REGIONS 2 y2002 y2003
  January -May     USA      668   692
  January - May   CANADA    243   361

Tried:


SELECT   [Final_Clean Tourists Query].REGIONS3, 
IIf(DatePart('m',[F_ARRV_DAT],1,0)<='3',[Final_Clean Tourists Query].[TRAV-KEY]) 
AS  [January-April],
IIf(DatePart('m',[F_ARRV_DAT],1,0)<='4',[Final_Clean Tourists Query].[TRAV-KEY]) 
AS  [January-May],
Count(IIf(DatePart('yyyy',[F_ARRV_DAT],1,0)=PreviousYear,[Final_Clean Tourists Query].[TRAV-KEY])) 
AS y2002, 
Count(IIf(DatePart('yyyy',[F_ARRV_DAT],1,0)=CurrentYear,[Final_Clean Tourists Query].[TRAV-KEY])) 
AS y2003

FROM 

[Final_Clean Tourists Query]

WHERE 
(((DatePart('yyyy',[F_ARRV_DAT],1,0))=[PreviousYear] 
Or (DatePart('yyyy',[F_ARRV_DAT],1,0))=[CurrentYear]))
group by  [Final_Clean Tourists Query].REGIONS3, 
IIf(DatePart('m',[F_ARRV_DAT],1,0)<='3',[Final_Clean Tourists Query].[TRAV-KEY]),
IIf(DatePart('m',[F_ARRV_DAT],1,0)<='4',[Final_Clean Tourists Query].[TRAV-KEY])

which gave me:


REGIONS3	January-April	January-May	y2002	y2003
CANADA			                         898	692
CANADA		                   6515546	  1	0
CANADA		                   6515872	  1	0
CANADA		                   6515879	  1	0

Ideally it should group on the Region to give:


REGIONS3			y2002	y2003
CANADA          January-April     898	 692
USA		January-April     898	 692
CANADA		January-May       898	 692            
USA		January-May       898	 692

I use y2002 and y2003 to identify those records which fall into a specific year, so for example I say 2002 and 2003.

blinton25
Yak Posting Veteran

90 Posts

Posted - 2003-09-22 : 00:53:42

Hello,

Got it worked out using the following procedure:

1. Created a yeartodate table which has the following structure:


id     MonthRange          MonthDigit
1   January - February         1
2   January - March            1
3   January - March            2
4   January - March            3

2. In the query I created a Join on the Numeric value of the Month and MonthDigit


SELECT  YearToDate.id, YearToDate.MonthRange, Format([F_ARRV_DAT],"mmmm") AS [Month], 
[Final_Clean Tourists Query].REGIONS3, 
Count(IIf(DatePart('yyyy',[F_ARRV_DAT],1,0)=PreviousYear,[Final_Clean Tourists Query].[TRAV-KEY])) 
AS y2002, 
Count(IIf(DatePart('yyyy',[F_ARRV_DAT],1,0)=CurrentYear,[Final_Clean Tourists Query].[TRAV-KEY])) 
AS y2003
FROM 
[Final_Clean Tourists Query] 
INNER JOIN YearToDate ON 
[Final_Clean Tourists Query].MonthOrder = YearToDate.MonthDigit
WHERE (((DatePart('yyyy',[F_ARRV_DAT],1,0))=[PreviousYear] 
Or (DatePart('yyyy',[F_ARRV_DAT],1,0))=[CurrentYear]))
GROUP BY YearToDate.id, YearToDate.MonthRange, 
Format([F_ARRV_DAT],"mmmm"), [Final_Clean Tourists Query].REGIONS3
HAVING ((([Final_Clean Tourists Query].REGIONS3)<>''));

3. The results were returned in the desired format.

From there I was able to use a Pivot Table to achieve the desired report layout and totals.

I would like to think there is a more elegant way of doing this, and I suspect there is a performance hit. But for now this is working.

Thanks for the guidance and assistance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources