Seek Electricity Power Thefts by SQL

• Status: Closed
• Prize: €100
• Entries Received: 3
• Winner: freelancer22to00

Winner

Contest Brief

Hi there, I am seeking an experienced professional to help with a Flash software project.

The question is to find the Electricity Power Thefts in a territory using the given .csv files only.

1. Records.csv (140.000 clients-5 families) and
2. Consumptions.csv ( 1.000.000 measurements).

The file fields the common fields in bold:
• Property ID (ACCT_NBR) - Common File
• Property habitant serial No (SUCCESSOR) - Common File
• Voltage (VOLTAGE)
• Fuse size (PARNO)
• Usage type (XRHSH)
• Max allowed power capacity (CONTRACT_CAPACITY)
• Manually/electronically counted (ACCT_CONTROL)
• Consumer meter category (business-home-night meter) (BS_RATE)
• Consumer meter No (MS_METER_NBR)
• Consumption in KWh-last measure minus previous measure-(CSS_MS_HS_USE)
• Measurement Date (MEASUREMENT_DATE)

I need Relational Database Management System (RDBMS) software installed in Windows to present my methodology of evaluating the Power thefts in the electricity consumption of a town.

I will need a dashboard to
1. choose and import the two .csv files,
2. select a period of time (e.g., 4 years),
3. display the database and final .csv
4. Export the final csv

Methodology
From a consumer consumption measurement given, we calculate the daily average from the previous measurement for all measurements of the given period. We calculate the % participation of each to the daily totals of families and to the day grand total consumption and we calculate the Standard deviation of the % per family and grand day total. Using a weight factor, I may select from the dashboard, it will add all standard deviations to the Final SD number, rank the database in descending order by the SD Final and export a .csv file with fields "ACCT_NBR", "SUCCESSOR," and "SD FINAL" only

Recommended Skills

• Excel
• Microsoft Access
• MySQL
• SQL