Hi, I would like to know a solution which can help me generate a MySQL query to fetch data as I want from a single data base table.
Table name: Rates
Columns: id, date, weekday, costA, costB, costC, sellA, sellB, sellC
SAMPLE TABLE DATA
=================
id | date | weekday | costA | costB | costC | sellA | sellB | sellC
----------------------------------------------------------------------
01 2010/6/1 TUE 120 135 140 150 165 180
02 2010/6/2 WED 120 135 140 150 165 180
03 2010/6/3 THU 120 135 140 150 165 180
04 2010/6/4 FRI 120 135 140 150 165 180
05 2010/6/5 SAT 120 135 140 150 165 180
06 2010/6/6 SUN 120 135 140 150 165 180
07 2010/6/7 MON 120 135 140 150 165 180
...
16 2010/6/16 WED 150 140 140 200 220 230
17 2010/6/17 THU 150 140 140 200 220 230
18 2010/6/18 FRI 150 140 140 200 220 230
19 2010/6/19 SAT 150 140 140 200 220 230
20 2010/6/20 SUN 150 140 140 200 220 230
21 2010/6/21 MON 150 140 140 200 220 230
22 2010/6/22 TUE 150 140 140 200 220 230
The query I want to do with this table is that, I want to get the starting date of each cost and sell column and the last date of the price changed. Which I did manage to do it with the following MYSQL query:
SELECT * FROM rates ID IN(SELECT MIN(ID) FROM `rates` GROUP BY costA,costB,costC,sellA,sellB,sellC) OR ID IN(SELECT MAX(ID) FROM `rates` GROUP BY costA,costB,costC,sellA,sellB,sellC)
How ever the problem I have to tackle is, if in case for example, every wednesday and on every friday has two different price from 2010/06/01 to 2010/06/16. So how do I generate a query which can give me a total summary by weekdays as explained below.
id | date | weekday | costA | costB | costC | sellA | sellB | sellC
----------------------------------------------------------------------
01 2010/6/1 TUE 120 135 140 150 165 180
02 2010/6/2 WED 100 100 100 110 120 130
03 2010/6/3 THU 120 135 140 150 165 180
04 2010/6/4 FRI 140 150 160 150 175 180
05 2010/6/5 SAT 120 135 140 150 165 180
06 2010/6/6 SUN 120 135 140 150 165 180
07 2010/6/7 MON 120 135 140 150 165 180
...
16 2010/6/16 WED 100 100 100 110 120 130
17 2010/6/17 THU 150 140 140 200 220 230
18 2010/6/18 FRI 150 140 140 200 220 230
19 2010/6/19 SAT 150 140 140 200 220 230
20 2010/6/20 SUN 150 140 140 200 220 230
21 2010/6/21 MON 150 140 140 200 220 230
22 2010/6/22 TUE 150 140 140 200 220 230
From the above table, the report I need to generate should be something similar to the one below.
id | date | weekday | costA | costB | costC | sellA | sellB | sellC
----------------------------------------------------------------------
01 2010/6/1 TUE 120 135 140 150 165 180
02 2010/6/2 WED 100 100 100 110 120 130
03 2010/6/3 THU 120 135 140 150 165 180
04 2010/6/4 FRI 140 150 160 150 175 180
05 2010/6/5 SAT 120 135 140 150 165 180
15 2010/6/15 TUE 120 135 140 150 165 180
16 2010/6/16 WED 100 100 100 110 120 130
17 2010/6/17 THU 150 140 140 200 220 230
22 2010/6/22 TUE 150 140 140 200 220 230
Where it is read from the above table as 2010/06/01 to 2010/06/16 rates are:
WEEKDAY| costA | costB | costC | sellA | sellB | sellC
SAT 120 135 140 150 165 180
SUN 120 135 140 150 165 180
MON 120 135 140 150 165 180
TUE 120 135 140 150 165 180
WED 100 100 100 110 120 130
THU 120 135 140 150 165 180
FRI 140 150 160 150 175 180
and from 2010/06/17 to 2010/06/22
WEEKDAY| costA | costB | costC | sellA | sellB | sellC
SAT 150 140 140 200 220 230
SUN 150 140 140 200 220 230
MON 150 140 140 200 220 230
TUE 150 140 140 200 220 230
WED 150 140 140 200 220 230
THU 150 140 140 200 220 230
FRI 150 140 140 200 220 230
I hope there will be a solution to tackle this problem. In short, what i am trying to do here is to generate a periodic table of rates, where it will tell the cost/sell price details based on weekdays and grouped into dynamic date periods telling the start and end date of every similar week.