–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.
selectCOALESCE (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
(select point, date, SUM(out) out from Outcome
groupby point, date) x
(select point, date, out from Outcome_o) y on x.point = y.point and x.date = y.date
innerjoin (select distinct o1.point from Outcome o1, Outcome_o o2
whereo1.point = o2.point) z on z.point = x.point or z.point = y.point