0

As you can see, the beginning and the end of the week are selected with this code. I'm new to sql and I understand what's going on here in general, but I can't explain it in detail. For example ;

(select lookupweekid-1 from lookupday where dateoftransaction = date) t2 (lookupweekid)

This one confused me a lot. (...) t2 (...), <-I don't understand what the parenthesis in the last part is telling

select start_of_week,end_of_week from
(select dateoftransaction
 from lookupday t1,
      (select lookupweekid-1 from lookupday 
       where dateoftransaction = date) t2 (lookupweekid)
 where t1.lookupweekid=t2.lookupweekid
   and t1.weekdaynumber=1) t3 (start_of_week)
,(select dateoftransaction
  from lookupday t1,
       (select lookupweekid-1 from lookupday 
        where dateoftransaction = date) t2 (lookupweekid)
  where t1.lookupweekid=t2.lookupweekid
  and t1.weekdaynumber=7) t4 (end_of_week)
;
mert
  • 153
  • 1
  • 11
  • 1
    `t2` is the table alias, i.e. the name for the table resulting from the subquery. The names that follow in parentheses are the column aliases. In your case the expression `lookupweekid-1` gets the column name alias `lookupweekid`. – Thorsten Kettner Nov 26 '21 at 08:05

1 Answers1

3

t4 (end_of_week) is a table alias that also defines a column alias. This avoids having to specify the column aliases inside the derived table.

So this:

(select dateoftransaction
 from lookupday t1 ... 
 where t1.lookupweekid=t2.lookupweekid
 and t1.weekdaynumber=7) t4 (end_of_week)

is equivalent to:

(select dateoftransaction as end_of_week
 from lookupday t1 ... 
 where t1.lookupweekid=t2.lookupweekid
 and t1.weekdaynumber=7) t4

This is e.g. explained in the Postgres manual