Postgres View / Function

Question

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

It's not clear to me what you are asking. Why do you need the view? Why not use the last query if that gives what you need? — Eelke
– Eelke, Commented Mar 8, 2012 at 18:48

Community · Accepted Answer · 2020-06-20 09:12:55Z

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

Collectives™ on Stack Overflow

Postgres View / Function

1 Answer 1

Major points

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Major points

Comments

Your Answer

Sign up or log in

Post as a guest

Related