I've written a function that calculates the number of days for every month in a
given range and returns that as a set of records.
CREATE OR REPLACE FUNCTION general_daysinmonth(
date1 IN date,
date2 IN date,
month OUT date,
days OUT integer) RETURNS SETOF record AS
$body$
DECLARE
startdate date;
enddate date;
BEGIN
IF date1 >= date2 THEN
startdate := date2;
enddate := date1;
ELSE
startdate := date1;
enddate := date2;
END IF;
month := date_trunc('month', startdate);
WHILE month <= enddate LOOP
days := LEAST(general_lastdayofmonth(month), enddate) - GREATEST(startdate,
month) + 1;
RETURN NEXT;
month := month + interval '1 month';
END LOOP;
RETURN;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE RETURNS NULL ON NULL INPUT SECURITY INVOKER;
Now I want to use that resultset as part of a join with a table called billing
(see definition below).
This join should return a record per month that is between salesstartdate and
salesenddate and lineamountmst should be divided pro rata the number of days in
each month
An example
If table billing holds a record like
id invoiceid inventtransid invoicedate dataareaid lineamountmst dimension
itemid salesunit issues salesstartdate salesenddate salesstopcode salespoolid
1170 22428431 735706 2006-02-28 hlm 89,89 nlaatoal-6 nlaatoal 3m 0 2006-02-24
2006-05-23 SWI-TRM-1Y aans
Then select * from general_daysinmonth(date '2006-02-24', date '2006-05-23')
would return
month days
2006-02-01 5
2006-03-01 31
2006-04-01 30
2006-05-01 23
So my join should return 4 records like
invoiceid billingmonth revenuemonth revenue
22428431 2006-02-01 2006-02-01 5,05
22428431 2006-02-01 2006-03-01 31,31
22428431 2006-02-01 2006-04-01 30,30
22428431 2006-02-01 2006-05-01 23,23
where 89,89 / number of days between 2006-02-24 and 2006-05-23 * number of days
in 2006-02 = 5,05 for the first record
I thought I could do that quite easily like this
SELECT B.invoiceid, date_trunc('month', B.invoicedate) as billingmonth,
C.month as revenuemonth,
B.lineamountmst * C.days / (B.salesenddate - B.salesstartdate +
1) as revenue
FROM billing B, general_daysinmonth(B.salesstartdate, B.salesenddate) C
WHERE B.dataareaid = 'hlm' AND
B.issues = 0 AND
B.salesenddate >= date_trunc('year', current_timestamp) - interval '1
year';
or like this
SELECT B.invoiceid, date_trunc('month', B.invoicedate) as billingmonth,
C.month as revenuemonth,
B.lineamountmst * C.days / (B.salesenddate - B.salesstartdate +
1) as revenue
FROM billing B, (select * from general_daysinmonth(B.salesstartdate,
B.salesenddate)) C
WHERE B.dataareaid = 'hlm' AND
B.issues = 0 AND
B.salesenddate >= date_trunc('year', current_timestamp) - interval '1
year';
But I get an error message in both cases: respectively
ERROR: function expression in FROM may not refer to other relations of same
query level
ERROR: subquery in FROM may not refer to other relations of same query level
Can anyone please explain me why I'm getting this error message and how I do
what I'm trying to do?
Thanks for your help, advice and time...
CREATE TABLE public.billing (
id serial PRIMARY KEY,
invoiceid varchar(20) DEFAULT NULL,
inventtransid varchar(20) DEFAULT NULL,
invoicedate date NOT NULL,
dataareaid varchar(3) NOT NULL,
lineamountmst numeric(32,16) NOT NULL,
dimension varchar(16) NOT NULL,
itemid varchar(20) NOT NULL,
salesunit varchar(10) NOT NULL,
issues numeric(2) DEFAULT 0,
salesstartdate date NOT NULL,
salesenddate date NOT NULL,
salesstopcode varchar(16) DEFAULT NULL,
salespoolid varchar(10) NOT NULL
) WITH (fillfactor=75);
