CODEWITHSUNDEEP
sqlsql-server
James Khan
James Khan

Reputation: 841

Computation of period Start date

I have a table that hold the start date and the end date of a financial period. 

CHARGE_PERIOD_ID    START_DATE                 END_DATE
13                  2013-03-31 00:00:00.000    2013-04-27 00:00:00.000
14                  2013-04-28 00:00:00.000    2013-05-25 00:00:00.000
15                  2013-05-26 00:00:00.000    2013-06-29 00:00:00.000
16                  2013-06-30 00:00:00.000    2013-07-27 00:00:00.000
17                  2013-07-28 00:00:00.000    2013-08-24 00:00:00.000
18                  2013-08-25 00:00:00.000    2013-09-28 00:00:00.000
19                  2013-09-29 00:00:00.000    2013-10-26 00:00:00.000
20                  2013-10-27 00:00:00.000    2013-11-23 00:00:00.000
21                  2013-11-24 00:00:00.000    2013-12-28 00:00:00.000
22                  2013-12-29 00:00:00.000    2014-01-25 00:00:00.000
23                  2014-01-26 00:00:00.000    2014-02-22 00:00:00.000
24                  2014-02-23 00:00:00.000    2014-03-29 00:00:00.000

The user of a report wants the current financial year split into 12 periods and want to give to feed in 2 parameters into the report , a year and a period number which will go into my sql . So something like @year=2014 @period=1 will be recieved . I have to write some sql to go to this table and set a period start date of 31/03/2014 and a period end date of 27/04/2014.

So in pseudo code:

Look up period 1 for 2014 and return period start date of 31/03/2014 and period end date of 27/04/2014.

@PERIOD_START_DATE = select the the first period that starts in March for the given year . all financial period starts in March.

@PERIOD_END_DATE = select the corresponding END_DATE from the table .

The question is how to begin to code this or my design approach? Should I create a function that calcualtes this or should I do a CTE and add a column which will hold the period number in the way they want etc .

Thinking about it more I think I need a mapping table . So the real question is can I do this without a mapping table ?

Upvotes: 0

Views: 634

Answers (1)

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

DECLARE @Year INT 
DECLARE @Period INT

SET @Year= 2013
SET @Period = 1


;WITH CTE AS 
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY 
                                CASE WHEN MONTH([START_DATE])<3 THEN YEAR([START_DATE]) -1 ELSE YEAR([START_DATE]) END                              
                                ORDER BY 
                                CASE WHEN MONTH([START_DATE])<3 THEN YEAR([START_DATE]) - 1 ELSE YEAR([START_DATE]) END
                                ,CASE WHEN MONTH([START_DATE])<3 THEN MONTH([START_DATE]) + 12 ELSE MONTH([START_DATE]) END 
                                ) AS RN
FROM Periods
)
SELECT * FROM CTE
WHERE RN = @Period 
AND CASE WHEN MONTH([START_DATE])<3 THEN YEAR([START_DATE]) -1 ELSE YEAR([START_DATE]) END = @Year

SQLFiddle DEMO

Upvotes: 1

Related Questions