Efficient SQL and python function
Budget: $30 – $250 USD
i have 3 tables
"kriot_ratsif":
=============
it holds all readings of water meters
the important are :
reading date = the exect day and time the water meter quantity was taken
water count: the quantity
mone: the water meter serial number
second table:
=============
"kriot_yomi":(sum of daily quantitiws ) it holds the data from "kriot _ratsif" aggregated:
for each mone for each date there is 1 quantity
the important fields are
"day_date": the day we summerise
"mone": the water_meter number
"former reading": the water quantity if the end of yesterday
"last_reading" : the last reading of today
"real_qty" : -just for mission2
"diff_sons" - just for mission2
^^^^^^^^^^^^^^^^^^
mission 1 create an efficient sql to transfer from "kriot_ratsif" to kriot_yomi ===> for some dates
function may be called "aggregate (cursor,"2020-01-01","2021-01-02")"
^^^^^^^^^^^^^^^^^^
Mission 2
there is a table called
"monim"
==========
the important fields there are:
"mone": the water meter number
"mone_av" : its "father" - the water meter that it is connected to (its a tree of water meters where one "mone_av" is connected to several "sons"
"neches" : a field for other connectiob - just data
"diameter" the bigger- the more important it is
the mission:
in "kriot_yomi" there is a field called "real_qty"
this should hold the total quantities of its direct sons = which is in "qty" field (automatically calculated from (last-reading-former_reading by DB_
create a function called "update_real (cursor,"2020-01-01","2021-01-02"1)
a) update the total qty of the sons, (if father has no record - open it) ,in order of mone_diameter small to high
b) update field "diff_sons" to be the value of "sons field " in mone_av record and subtract the number of sons you had values
c) update ""neches" from monim
i need an efficient fast sql, may have some python code
** for all "wise guys" ***
a) price wont change so please be sure you are an expert in both sqland python
b)look at the attached db
c) no milestone until i know you can di the job!
"kriot_ratsif":
=============
it holds all readings of water meters
the important are :
reading date = the exect day and time the water meter quantity was taken
water count: the quantity
mone: the water meter serial number
second table:
=============
"kriot_yomi":(sum of daily quantitiws ) it holds the data from "kriot _ratsif" aggregated:
for each mone for each date there is 1 quantity
the important fields are
"day_date": the day we summerise
"mone": the water_meter number
"former reading": the water quantity if the end of yesterday
"last_reading" : the last reading of today
"real_qty" : -just for mission2
"diff_sons" - just for mission2
^^^^^^^^^^^^^^^^^^
mission 1 create an efficient sql to transfer from "kriot_ratsif" to kriot_yomi ===> for some dates
function may be called "aggregate (cursor,"2020-01-01","2021-01-02")"
^^^^^^^^^^^^^^^^^^
Mission 2
there is a table called
"monim"
==========
the important fields there are:
"mone": the water meter number
"mone_av" : its "father" - the water meter that it is connected to (its a tree of water meters where one "mone_av" is connected to several "sons"
"neches" : a field for other connectiob - just data
"diameter" the bigger- the more important it is
the mission:
in "kriot_yomi" there is a field called "real_qty"
this should hold the total quantities of its direct sons = which is in "qty" field (automatically calculated from (last-reading-former_reading by DB_
create a function called "update_real (cursor,"2020-01-01","2021-01-02"1)
a) update the total qty of the sons, (if father has no record - open it) ,in order of mone_diameter small to high
b) update field "diff_sons" to be the value of "sons field " in mone_av record and subtract the number of sons you had values
c) update ""neches" from monim
i need an efficient fast sql, may have some python code
** for all "wise guys" ***
a) price wont change so please be sure you are an expert in both sqland python
b)look at the attached db
c) no milestone until i know you can di the job!