Distribution in time

[jeanulrich00]

Hi

I have a table containing over 300 records

Table contain 4 fields.
Unique ID for each field
Amount (Field with number)
Beginning (dated field)
End (date field)

I would like to distribute Amounts in time

Example let say that for the first record I have 1000$ , the
beginning is the first of January 2007 and the end is the 15 of march
2007

1/1/07 to 15/3/07 equal 74 days (counting the last day)

Distribution should be like that

For January it is : 31 days / 74 days * 1000 = 418.91
For February it is : 28 days / 74 days * 1000 = 378.38
For march it is : 15 days / 74 days * 1000 = 202.70

Is it possible to do that with a query or do I need some codes

Thanks

 

[Pat Hartman]

You will need to write code for this since you will be taking one record and
turning it into many or taking one column and turning it into many.

 

[Michael Gramelspacher]

This is a similiar problem. May it can be of some use. It uses a second table
to handle the dates.

CREATE TABLE Projects
(
project_id VARCHAR (10) NOT NULL
,project_name VARCHAR (100) NOT NULL
,project_value DECIMAL (12,4) NOT NULL
,project_start_date DATETIME NOT NULL
..project_end_date DATETIME NOT NULL
,PRIMARY KEY (project_id)
);

CREATE TABLE FiscalMonths
(
fiscal_year INTEGER NOT NULL
,fiscal_month INTEGER NOT NULL
,fiscal_quarter INTEGER NOT NULL
,month_start_date DATETIME NOT NULL
,month_end_date DATETIME NOT NULL
,PRIMARY KEY (fiscal_year, fiscal_month)
);


Query: Contract Days Per Fiscal Month

SELECT b.project_id
, b.project_name
, b.project_value
, b.project_start_date
, b.project_end_date
, a.fiscal_year
, a.fiscal_quarter
, a.fiscal_month
, DATEDIFF("d",b.project_start_date,b.project_end_date) + 1
AS contracted_days
, SUM(DATEDIFF("d",IIF(b.project_start_date < a.month_start_date,
a.month_start_date,b.project_start_date),
IIF(b.project_end_date > a.month_end_date,
DATEADD("d",1,a.month_end_date),
DATEADD("d",1,b.project_end_date)))) AS days
FROM Projects AS b,
FiscalMonths AS a
WHERE b.project_start_date <= a.month_end_date
AND b.project_end_date >= a.month_start_date
GROUP BY b.project_id,b.project_name,b.project_value,
b.project_start_date,b.project_end_date,a.fiscal_year,
a.fiscal_quarter,a.fiscal_month;

Query: Contract Value by Month

SELECT c.project_id,
c.project_name,
c.project_start_date,
c.project_end_date,
c.fiscal_year,
c.fiscal_month,
c.project_value,
c.Days,
c.project_value * c.days / c.contracted_days AS MonthlyValue
FROM [Contract Days by Fiscal Month] AS c;

 

[jeanulrich00]

This is a similiar problem. May it can be of some use. It uses a second table
to handle the dates.

CREATE TABLE Projects
(
project_id VARCHAR (10) NOT NULL
,project_name VARCHAR (100) NOT NULL
,project_value DECIMAL (12,4) NOT NULL
,project_start_date DATETIME NOT NULL
.project_end_date DATETIME NOT NULL
,PRIMARY KEY (project_id)
);

CREATE TABLE FiscalMonths
(
fiscal_year INTEGER NOT NULL
,fiscal_month INTEGER NOT NULL
,fiscal_quarter INTEGER NOT NULL
,month_start_date DATETIME NOT NULL
,month_end_date DATETIME NOT NULL
,PRIMARY KEY (fiscal_year, fiscal_month)
);

Query: Contract Days Per Fiscal Month

SELECT b.project_id
, b.project_name
, b.project_value
, b.project_start_date
, b.project_end_date
, a.fiscal_year
, a.fiscal_quarter
, a.fiscal_month
, DATEDIFF("d",b.project_start_date,b.project_end_date) + 1
AS contracted_days
, SUM(DATEDIFF("d",IIF(b.project_start_date < a.month_start_date,
a.month_start_date,b.project_start_date),
IIF(b.project_end_date > a.month_end_date,
DATEADD("d",1,a.month_end_date),
DATEADD("d",1,b.project_end_date)))) AS days
FROM Projects AS b,
FiscalMonths AS a
WHERE b.project_start_date <= a.month_end_date
AND b.project_end_date >= a.month_start_date
GROUP BY b.project_id,b.project_name,b.project_value,
b.project_start_date,b.project_end_date,a.fiscal_year,
a.fiscal_quarter,a.fiscal_month;

Query: Contract Value by Month

SELECT c.project_id,
c.project_name,
c.project_start_date,
c.project_end_date,
c.fiscal_year,
c.fiscal_month,
c.project_value,
c.Days,
c.project_value * c.days / c.contracted_days AS MonthlyValue
FROM [Contract Days by Fiscal Month] AS c;

Hi

I have a table containing over 300 records

Table contain 4 fields.
Unique ID for each field
Amount (Field with number)
Beginning (dated field)
End (date field)

I would like to distribute Amounts in time

Example let say that for the first record I have 1000$ , the
beginning is the first of January 2007 and the end is the 15 of march
2007

1/1/07 to 15/3/07 equal 74 days (counting the last day)

Distribution should be like that

For January it is : 31 days / 74 days * 1000 = 418.91
For February it is : 28 days / 74 days * 1000 = 378.38
For march it is : 15 days / 74 days * 1000 = 202.70

Is it possible to do that with a query or do I need some codes

Thanks- Hide quoted text -

- Show quoted text -

Hi Michael

I have try your code but I think that there is something missing

I have create Table "Projects"
I have create Table "FiscalMonths"
I have create Query "Contract Days Per Fiscal Month" and this seems to
work fine

Then I could not continue and create query "Contract Value by Month"
because you did not included all the code

As I don't know how to manage to obtain all colums (one month by
colum) your help will be precious for me

thanks

 

[Michael Gramelspacher]

Then I could not continue and create query "Contract Value by Month"
because you did not included all the code

Not so. Note that the second query is selecting from the first query. This is
the entire second query:

SELECT c.project_id,
c.project_name,
c.project_start_date,
c.project_end_date,
c.fiscal_year,
c.fiscal_month,
c.project_value,
c.Days,
c.project_value * c.days / c.contracted_days AS MonthlyValue
FROM [Contract Days by Fiscal Month] AS c;