CODEWITHSUNDEEP
sqldatabasealgorithmpostgresql
Gnanam
Gnanam

Reputation: 10923

Date split-up based on break-up

Given a From Date, To Date, Fiscal Year system. I want to get all the split-up duration within the given duration based on the breakup asked for.

Breakups are:
1) Weekly: This should be followed from Monday through Sunday.
2) Monthly: This should be 1st of a month through end of the month.
3) Quarterly Since the Fiscal Year system is Apr-Mar, quarterly breakups should be Apr-Jun, Jul-Sep, Oct-Dec, Jan-Mar
4) Half-yearly: Since the Fiscal Year system is Apr-Mar, half-yearly breakups should be Apr-Sep, Oct-Mar.
5) Annual: Since the Fiscal Year system is Apr-Mar, annual breakup should be Apr-Mar.

Explained below with examples.
Example 1:
From Date: Feb-10-2010
To Date: Feb-10-2010
Fiscal Year system: Apr to Mar

Weekly Breakup: Feb-08-2010 to Feb-14-2010
Monthly Breakup: Feb-01-2010 to Feb-28-2010
Quarterly Breakup: Jan-01-2010 to Mar-31-2010
Half-yearly Breakup: Oct-01-2009 to Mar-31-2010
Annual Breakup: Apr-01-2009 to Mar-31-2010

Am looking for approach/algorithm to solve this in PostgreSQL 8.2.

This post is similar to a question posted here: Date split-up based on Fiscal Year

Upvotes: 0

Views: 342

Answers (1)

Gavin
Gavin

Reputation: 6470

Rather than trying to do this in code it may be worth using a calendar table keyed on date and with period attributes.
e.g.

CREATE TABLE calendar_day (
day_date DATE PRIMARY KEY,
week_number INTEGER NOT NULL,
month_number SMALLINT NOT NULL,
quarter_number SMALLINT NOT NULL,
demi_year_number SMALLINT NOT NULL,
fiscal_year SMALLINT NOT NULL);

This can be populated fairly easily using generate_series() and postgresql date functions and then used to join according to the date criteria you wish to use

Upvotes: 1

Related Questions