The purpose of this set of web pages is to demonstrate knowledge of building Microsoft Excel based solutions, data structure design and live querying on data tables (MySQL and MS SQL).
As previously mentioned, the week commencing field, week number field and week day field are not stored in the MySQL database as they are built from the transaction date field. By default, dates in MySQL are stored in the format 'yyyy-mm-dd'. The date functions to alter the format and produce the new date-related data are:
MySQL Code
SELECT trans_id, trans_staff_id, trans_cust_id,
DATE_FORMAT(trans_date, '%d/%m/%Y') AS Date,
DATE_FORMAT(DATE_ADD(trans_date, INTERVAL(-WEEKDAY(trans_date)) DAY),'%d/%m/%Y' ) AS trans_wc,
DAYNAME(trans_date) AS trans_day,
WEEK(trans_date) As trans_wknum,
MONTH(trans_date) As trans_month
FROM transactions
If the database being used was an MS-SQL database, this would be achieved as follows:
T-SQL Code (MS SQL)
SET DATEFIRST 1 -- Changes first day of week from Sunday to Monday
SELECT trans_id, trans_staff_id, trans_cust_id,
CONVERT (varchar(10), trans_date, 103) AS trans_date,
DATEADD(DAY, 1 - DATEPART(WEEKDAY, trans_date),
CAST(trans_date AS DATE)) AS trans_wc,
DATENAME(dw, trans_date) AS trans_day,
DATEPART(ISO_WEEK, trans_date) AS trans_wknum,
DATEPART(M, trans_date) AS trans_month
FROM transactions
Here are the live results of a MySQL query demonstrating the above functions that will build this data from the 'trans_date' in the Transactions table:
| trans_id | trans_staff_id | trans_cust_id | Date | trans_wc | trans_day | trans_wknum | trans_month |
|---|---|---|---|---|---|---|---|
| 52 | 50 | 4589 | 03/01/2017 | 02/01/2017 | Tuesday | 1 | 1 |
| 53 | 51 | 4583 | 03/01/2017 | 02/01/2017 | Tuesday | 1 | 1 |
| 54 | 52 | 4568 | 04/01/2017 | 02/01/2017 | Wednesday | 1 | 1 |
| 55 | 53 | 4570 | 04/01/2017 | 02/01/2017 | Wednesday | 1 | 1 |
| 56 | 55 | 4582 | 05/01/2017 | 02/01/2017 | Thursday | 1 | 1 |
| 57 | 56 | 4582 | 05/01/2017 | 02/01/2017 | Thursday | 1 | 1 |
| 58 | 50 | 4587 | 09/01/2017 | 09/01/2017 | Monday | 2 | 1 |
| 59 | 51 | 4589 | 09/01/2017 | 09/01/2017 | Monday | 2 | 1 |
| 60 | 52 | 4582 | 10/01/2017 | 09/01/2017 | Tuesday | 2 | 1 |
| 61 | 53 | 4568 | 11/01/2017 | 09/01/2017 | Wednesday | 2 | 1 |
| 62 | 55 | 4568 | 12/01/2017 | 09/01/2017 | Thursday | 2 | 1 |
| 63 | 51 | 4618 | 12/01/2017 | 09/01/2017 | Thursday | 2 | 1 |
| 64 | 56 | 4567 | 13/01/2017 | 09/01/2017 | Friday | 2 | 1 |
| 65 | 50 | 4583 | 16/01/2017 | 16/01/2017 | Monday | 3 | 1 |
| 66 | 51 | 4584 | 16/01/2017 | 16/01/2017 | Monday | 3 | 1 |
| 67 | 52 | 4583 | 18/01/2017 | 16/01/2017 | Wednesday | 3 | 1 |
| 68 | 53 | 4585 | 20/01/2017 | 16/01/2017 | Friday | 3 | 1 |
| 69 | 55 | 4616 | 20/01/2017 | 16/01/2017 | Friday | 3 | 1 |
| 70 | 56 | 4617 | 20/01/2017 | 16/01/2017 | Friday | 3 | 1 |
| 71 | 50 | 4618 | 25/01/2017 | 23/01/2017 | Wednesday | 4 | 1 |
| 72 | 51 | 4587 | 25/01/2017 | 23/01/2017 | Wednesday | 4 | 1 |
| 73 | 52 | 4589 | 25/01/2017 | 23/01/2017 | Wednesday | 4 | 1 |
| 74 | 51 | 4567 | 25/01/2017 | 23/01/2017 | Wednesday | 4 | 1 |
| 75 | 53 | 4570 | 25/01/2017 | 23/01/2017 | Wednesday | 4 | 1 |
| 76 | 52 | 4571 | 30/01/2017 | 30/01/2017 | Monday | 5 | 1 |
| 77 | 53 | 4572 | 30/01/2017 | 30/01/2017 | Monday | 5 | 1 |
| 78 | 55 | 4573 | 30/01/2017 | 30/01/2017 | Monday | 5 | 1 |
| 79 | 50 | 4585 | 06/02/2017 | 06/02/2017 | Monday | 6 | 2 |
| 80 | 51 | 4582 | 07/02/2017 | 06/02/2017 | Tuesday | 6 | 2 |
| 81 | 51 | 4618 | 07/02/2017 | 06/02/2017 | Tuesday | 6 | 2 |
| 82 | 52 | 4601 | 07/02/2017 | 06/02/2017 | Tuesday | 6 | 2 |
| 83 | 53 | 4602 | 07/02/2017 | 06/02/2017 | Tuesday | 6 | 2 |
| 84 | 54 | 4603 | 07/02/2017 | 06/02/2017 | Tuesday | 6 | 2 |
| 85 | 55 | 4616 | 07/02/2017 | 06/02/2017 | Tuesday | 6 | 2 |
| 86 | 56 | 4582 | 07/02/2017 | 06/02/2017 | Tuesday | 6 | 2 |
| 87 | 57 | 4583 | 08/02/2017 | 06/02/2017 | Wednesday | 6 | 2 |
| 88 | 50 | 4616 | 13/02/2017 | 13/02/2017 | Monday | 7 | 2 |
| 89 | 51 | 4572 | 13/02/2017 | 13/02/2017 | Monday | 7 | 2 |
| 90 | 51 | 4570 | 13/02/2017 | 13/02/2017 | Monday | 7 | 2 |
| 91 | 52 | 4585 | 13/02/2017 | 13/02/2017 | Monday | 7 | 2 |
| 92 | 53 | 4617 | 13/02/2017 | 13/02/2017 | Monday | 7 | 2 |
| 93 | 54 | 4567 | 15/02/2017 | 13/02/2017 | Wednesday | 7 | 2 |
| 94 | 55 | 4571 | 15/02/2017 | 13/02/2017 | Wednesday | 7 | 2 |
| 95 | 56 | 4572 | 15/02/2017 | 13/02/2017 | Wednesday | 7 | 2 |
| 96 | 57 | 4591 | 15/02/2017 | 13/02/2017 | Wednesday | 7 | 2 |
| 97 | 57 | 4592 | 16/02/2017 | 13/02/2017 | Thursday | 7 | 2 |
| 98 | 50 | 4618 | 20/02/2017 | 20/02/2017 | Monday | 8 | 2 |
| 99 | 51 | 4601 | 20/02/2017 | 20/02/2017 | Monday | 8 | 2 |
| 100 | 52 | 4602 | 20/02/2017 | 20/02/2017 | Monday | 8 | 2 |
| 101 | 52 | 4603 | 20/02/2017 | 20/02/2017 | Monday | 8 | 2 |
| 102 | 53 | 4583 | 20/02/2017 | 20/02/2017 | Monday | 8 | 2 |
| 103 | 54 | 4585 | 23/02/2017 | 20/02/2017 | Thursday | 8 | 2 |
| 104 | 55 | 4617 | 23/02/2017 | 20/02/2017 | Thursday | 8 | 2 |
| 105 | 56 | 4616 | 23/02/2017 | 20/02/2017 | Thursday | 8 | 2 |
| 106 | 57 | 4571 | 24/02/2017 | 20/02/2017 | Friday | 8 | 2 |
| 107 | 50 | 4611 | 28/02/2017 | 27/02/2017 | Tuesday | 9 | 2 |
| 108 | 51 | 4612 | 28/02/2017 | 27/02/2017 | Tuesday | 9 | 2 |
| 109 | 52 | 4613 | 28/02/2017 | 27/02/2017 | Tuesday | 9 | 2 |
| 110 | 52 | 4603 | 28/02/2017 | 27/02/2017 | Tuesday | 9 | 2 |
| 111 | 53 | 4616 | 02/03/2017 | 27/02/2017 | Thursday | 9 | 3 |
| 112 | 54 | 4614 | 03/03/2017 | 27/02/2017 | Friday | 9 | 3 |
| 113 | 55 | 4572 | 03/03/2017 | 27/02/2017 | Friday | 9 | 3 |
| 114 | 56 | 4603 | 03/03/2017 | 27/02/2017 | Friday | 9 | 3 |
| 115 | 57 | 4571 | 03/03/2017 | 27/02/2017 | Friday | 9 | 3 |
| 116 | 56 | 4601 | 07/03/2017 | 06/03/2017 | Tuesday | 10 | 3 |
| 117 | 57 | 4602 | 08/03/2017 | 06/03/2017 | Wednesday | 10 | 3 |
| 118 | 55 | 4603 | 09/03/2017 | 06/03/2017 | Thursday | 10 | 3 |
| 119 | 54 | 4583 | 09/03/2017 | 06/03/2017 | Thursday | 10 | 3 |
| 120 | 54 | 4585 | 09/03/2017 | 06/03/2017 | Thursday | 10 | 3 |
| 121 | 53 | 4612 | 10/03/2017 | 06/03/2017 | Friday | 10 | 3 |
| 122 | 52 | 4616 | 10/03/2017 | 06/03/2017 | Friday | 10 | 3 |
| 123 | 50 | 4617 | 10/03/2017 | 06/03/2017 | Friday | 10 | 3 |
Click the button below to advance to the next stage
Or use the above 'SQL Queries' dropdown menu to jump to any stage.