Task 3

–The firm has a few outlets that receive items for recycling. Each of the outlets receives funds to be paid to deliverers.

–Information on received funds is registered in a table:

–Income_o(point, date, inc)

–The primary key is (point, date), thus receiption of money (inc) takes place not more than once a day

–(date column does not include time component of the date). Information on payments to deliverers is registered in the table:

–Outcome_o(point, date, out)

–In this table the primary key (point, date) also ensures bookkeeping of the funds distribution at each point not more than

— once a day.

–In case incomes and expenses may occur more than once a day, another database schema is used. Corresponding tables include

–code column as primary key:

–Income(code, point, date, inc)

–Outcome(code, point, date, out)

–In this schema date column does not also include the day time.

–Find inlets with greater total payout in different pairs of inlets with equal inlet

–No’s from outcome and outcome_o tables considering only those dates when waste was collected at least at one of the inlets involved.

–Result set: Inlet #, date, text: – "once a day", if daily reporting inlet is in the lead; – "more than once a day",

–if several times a day reporting inlet is in the lead; – "both", for draw cases.

select COALESCE (x.point, y.point) point,

COALESCE (x.date, y.date) date,

lider

= case

when (x.out IS NULL) or (x.out < y.out) then ‘once a day’

when (y.out IS NULL) or (x.out > y.out) then ‘more than once a day’

else ‘both’

end

from

(

select point, date, SUM(out) out from Outcome

group

by point, date) x

full

join

(

select point, date, out from Outcome_o) y on x.point = y.point and x.date = y.date

inner

join (select distinct o1.point from Outcome o1, Outcome_o o2

where

o1.point = o2.point) z on z.point = x.point or z.point = y.point

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s