Solved

Assign Running Count to Period in Date Table

Posted on 2012-03-28
2
576 Views
Last Modified: 2012-06-21
I have a date table that I need to assign a running count on the "Fiscal_Year_And_Period" column in order to do look back calculations like last 13 periods average, last 6 periods average, last 3 period average, and current period within the same query. I know i can use a CASE expression to do the calcs if a period is within 13 periods of the current period, but I run into problems when those periods extend into the prior year. I have attached a spreadsheet showing the table I have to work with and highlighted in yellow the column I need created (want to use a CTE if possible). Please let me know if furthur clarification is needed.
Calendar.xlsx
0
Comment
Question by:nirajkrishna
2 Comments
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
Comment Utility
You can get your value as follows: year*13+period -2008*13
0
 

Author Closing Comment

by:nirajkrishna
Comment Utility
Now that is one way I never thought of solving it. Very nice!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now