Solved

Assign Running Count to Period in Date Table

Posted on 2012-03-28
2
583 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 40

Accepted Solution

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

Author Closing Comment

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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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 …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

685 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