CODEWITHSUNDEEP
functionpostgresqlview
user1253956
user1253956

Reputation: 31

Postgres View / Function

I am trying to create a view which joins 2 tables but the results are dependent on the query. Here is a example of what I want to achieve.

I have a table called sessionaccounting and a table called sessionaccountingdailysplit.

sessionaccounting contains all our accounting data and sessionaccountingdailysplit is the sessionaccounting data split by date. They are joined by the foreign key sessionaccountingid

How the two tables work in unison is as follows:

for the row in sessionaccounting :

starttime - '2012-01-01', endtime - '2012-01-03', usage - 10000

for the rows in sessionaccountingdailysplit :

date - '2012-01-01', usage - 3000

date - '2012-01-02', usage - 5000

date - '2012-01-03', usage - 2000

Now what I want to do is if I run a view called vw_sessionaccounting as

SELECT * 
FROM vw_sessionaccounting 
WHERE starttime >= '2011-01-01' AND starttime <= '2011-01-02';

it must only sum the first two dates from sessionaccountingdailysplit and replace the usage in sessionaccounting accordingly for each effected row. (most cases sessionaccountingdailysplit wont have a row as there was no split)

So as above if I run

SELECT * 
FROM sessionaccounting 
WHERE starttime >= '2011-01-01' AND starttime <= '2011-01-02';

I will get the result of

starttime - '2012-01-01', endtime - '2012-01-03', usage - 10000

but if I run the query

SELECT * 
FROM vw_sessionaccounting 
WHERE starttime >= '2011-01-01' 
AND starttime <= '2011-01-02';

I will get the result of

starttime - '2012-01-01', endtime - '2012-01-03', usage - 8000

Upvotes: 0

Views: 719

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657122

Your question is a bit vague in several respects. But from what I gather and guess, your query (view) could look like this:

SELECT s.starttime
      ,s.endtime
      ,COALESCE(max(sd.date), s.endtime) AS effective_endtime_max_2_days
      ,COALESCE(sum(sd.usage), s.usage) AS usage_max_2_days
FROM   sessionaccounting s
LEFT   JOIN sessionaccountingdailysplit sd USING (sessionaccountingid)
WHERE  sd.sessionaccountingid IS NULL  -- no split ..
   OR  (starttime + 2) > sd.date -- .. or only the first two days of the split

GROUP BY s.starttime, s.endtime

Major points

  • Use a LEFT JOIN because:

... most cases sessionaccountingdailysplit wont have a row as there was no split

  • Only include the first two days: (starttime + 2) > sd.date

  • Be sure to include sessions without spit: WHERE sd.sessionaccountingid IS NULL OR

  • Use table aliases to get your monstrous table names out of the way:
    FROM sessionaccounting s

  • sum() the usage for the first two days. If there was no spit take the original total usage instead: COALESCE(sum(sd.usage), s.usage) AS usage_max_2_days

Upvotes: 2

Related Questions