-
Notifications
You must be signed in to change notification settings - Fork 58
/
Copy pathcleaning company.sql
77 lines (72 loc) · 6.24 KB
/
cleaning company.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
/*
First of all thank you for doing this job. I am confident your company is the perfect choice for the work I need to have done.
I have been running a small cleaning business since January 15, 2017 called Spruce Up.
Recently, due to COVID-19, there have been many changes to keep track of - many have chosen my service to keep the virus from their house.
Unfortunately, a few of my elderly customers have dropped my service because of their fear of letting people potentially carrying the virus into their house.
I feel like I'm now at a point where it's neccessary to store my records electronically so i can keep track of everything in a more orderly and accurate way.
First of all, I need the first and last names of my customers as well as their phone number and address.
I offer weekly or bi weekly (once every two weeks) cleaning.
I have worked out with each customer a specific rate to charge per hour depending on how cluttered the house is or how hard it is to clean.
I would also like a column for the number of hours I have calculated to be the average time I spend cleaning each house.
In addition I would like a column that computes how much money I earn from each customer at the end of the month.
The other thing I need to include is the date I started working for each customer as well as an end date for those who have chosen to discontinue my cleaning service.
Question: Did you start cleaning for any of your customers before the official start of your business in 2017?
Answer: I did do a bit of cleaning for friends before then, which is what inspired me to start the business.
None of these people, however, are in my records. I help them out as a friend every now and then for no cost.
Question: What is the highest price you charge for cleans including deep cleans?
Answer: For regular cleans I charge up to $35.00 per hour.
For deep cleans the highest I would ever charge is $53.00 per hour.
For total price it never computes to more than $424.00 even for deep cleans.
Question: Do you have customers in your records more than once if you do other jobs for them?
Answer: Yes we do have a few instances where we do a customer's house as well as their beach house, airb&b, office or rental.
In these cases, the jobs are recorded separately under the customer's name.
Question: What do you want to see in the address?
Answer: All my jobs are local so I only need to know the house number, and street name.
Question: Do you take any time off work?
Answer: Every so often I take a couple days off, but I always add customers to different days
so by the end of the year the weekly customers have 52 services and the bi weekly 26 services.
Reports:
1) I offer deep cleans for each customer on request. Deep cleans take twice as long as normal cleans.
I charge 50% more than a regular clean in that house.
For this I need to see the customers phone number, last name and the amount of money they will have to pay.
Please do it in this format: last name, first name (phone number) - price of service.
2) I would like to know how many months I have been working for each current customer.
If you could show the customers I have worked for for the longest time at the top, I would appreciate that.
Please include a column formatted like this: last name - weekly/bi weekly and a column for number of months
3) What is the average price I ask per hour for regular cleans and what is the average price per hour for deep cleans?
4) Which customer do I earn the most money from in a year? (not counting deep cleans)
And how much do I get from them?
5) How many customers did I gain in the year 2020 and how many did I lose?
These are my customers:
first name, last name, phone number, address, frequency, start date, end date, price per hour, hours
Tara, Hughes, 727 456 2346, 3478 71st Ave N, bi weekly, $25, 5, 04-04-2017, null
David, Sullivan, 727 346 6945, 5061 47th St. N, bi weekly, $30, 6, 05-14-2019, null
Jennifer, Hill, 727 456 8689, 2890 6th St. S, weekly, $35, 7, 03-10-2020, null
Bob, Evans, 727 680 3867, 1747 1st Ave N, bi weekly, $30, 8, 08-15-2018, null
Amy, Campbell, 727 245 6956, 1820 Tropical Shores Dr SE, bi weekly, $35, 8, 05-24-2017, 11-29-2020
Peter, Mitchell, 727 357 9765, 4059 Burlington Ave N, bi weekly, $30, 7, 09-30-2021, null
Sarah, Reed, 727 356 8708, 4908 Central Ave S, bi weekly, $32, 8, 12-06-2020, null
Diana, Moore, 727 476 7908, 5554 22nd Ave S, bi weekly, $25, 6,07-04-2017, 02-21-2021
Amanda, Roberts, 727 446 7964, 2778 20th Ave S, weekly, $35, 8, 04-30-2020, null
George, Anderson, 727 589 5756, 1918 Sunrise Dr SE, bi weekly, $28, 5, 09-26-2017, null
George, Anderson, 727 589 5756, 2917 Beach Dr SE, bi weekly, $25, 3, 04-09-2018, null
Samuel, Newman, 727 374 6632, 3344 Oak St NE, weekly, $30, 6, 10-03-2018, 10-07-2022
Judy, Davis, 727 233 0875, 2323 Mangrove St N, bi weekly, $25, 4, 12-28-2018, null
Walter, Nelson, 727 066 6356, 2734 Pinellas Point Dr S, bi weekly, $28, 5, 11-20-2019, null
Stephanie, Johnson, 727 007 5335, 1145 Driftwood Rd S, bi weekly, $32, 8, 08-03-2017, null
Heather, Miller, 727 568 9967, 2890 Coral Way N, bi weekly, $35, 8, 12-11-2019, 06-09-2020
Michele, Lopez, 727 245 7667, 3786 Snell Isle Blvd NE, weekly, $35, 8, 09-26-2020, null
Michele, Lopez, 727 245 7667, 1604 Gulf Way, bi weekly,$35, 7, 01-25-2021, null
Rodney, Wright, 727 345 7857, 3675 Driftwood Rd S, bi weekly, $32, 6, 02-21-2021, null
Susan, Lee, 727 134 6783, 2674 Central Ave S, bi weekly, $27, 6,11-03-2021,null
Isaiah, Eisenberg, 727 794 6865, 1665 Bayshore Dr NE, bi weekly, $32, 5, 08-22-2018, null
Kelly, Brooks 727 456 7857, 1777 1st St S, bi weekly, $30, 5, 07-29-2018, null
Lisa, Adams, 727 285 8999, 1386 48th Ave N, bi weekly, $30, 6, 09-01-2020, null
Lisa, Adams, 727 285 8999, 1533 48th Ave N, bi weekly, $25, 3, 11-12-2017, null
John, Allen, 727 709 7985, 2722 Bay St NE, bi weekly, $30, 6, 03-31-2019, null
Kim, White, 727 264 8996, 1718 50th Ave N, weekly, $34, 7, 06-19-2019, 04-05-2020
Christina, Lewis, 727 245 7240, 1444 Newton Ave S, bi weekly, $32, 5, 01-17-2021, null
James, Morris, 727 277 3365, 1006 5th Ave S, bi weekly, $35, 8, 09-08-2018, null
James, Morris, 727 277 3365, 4789 41st St S, bi weekly, $25, 4, 11-30-2019, null
Michael, Rogers, 727 955 62464, 2789 6th St S, weekly, $30, 8, 07-27-2017, null
*/