Hello

I have a table with tasks done on cars during a specific year.
The table inclune the fields CarNumber and DateDone.
The tasks are performed monthly. Only one day per month per each car.

Now I run thrugh the table to create a new table that will have
one row for each CarNumber and a field with the day of the month for each
month.

The new Table fields list look like:
CarNumber, Jan_Day, Feb_Day, Mar_Day.....

Can this be done using one query or
only using a program.

Thanks in advance
Sorin

Sorin,

>>Now I run thrugh the table to create a new table that will have
>>one row for each CarNumber and a field with the day of the month for each
>>month.

If the tasks table already contains the date of the task for each car for each month then it is possible to get the result you want with a slightly torturous piece of SQL:

SELECT
 C.CarNumber,
 T1.TaskDate as Jan_Date,
 T2.TaskDate as Feb_Date,
 [ ... etc. for all other months  ... ]

FROM Cars C
LEFT JOIN Tasks T1 ON T1.CarNumber = C.CarNumber
LEFT JOIN Tasks T2 ON T2.CarNumber = C.CarNumber

WHERE T1.TaskDate BETWEEN '2010-01-01' and '2010-01-31'
AND  T2.TaskDate BETWEEN '2010-02-01' and '2010-02-31'
[etc. for the other months]

--

If the tasks have been done in 1 year, and you are forward-planning for the next year, you can increment the year:

Keep the rest of the SQL above the same, but add:

SELECT
 C.CarNumber,
 T1.TaskDate + 365 as Jan_Date,

--

Not exactly sure whether this is really what you want, but I hope it is helpful

Sorin

> I have a table with tasks done on cars during a specific year.
> The table inclune the fields CarNumber and DateDone.
> The tasks are performed monthly. Only one day per month per each car.
>
> Now I run thrugh the table to create a new table that will have
> one row for each CarNumber and a field with the day of the month for each
> month.
>
> The new Table fields list look like:
> CarNumber, Jan_Day, Feb_Day, Mar_Day.....
>
>  Can this be done using one query or
> only using a program.

The following will give the highest day in each month that a card had work
done.
SELECT CarNumber,
MAX(IF(EXTRACT(MONTH FROM DateDone) = 1 THEN EXTRACT(DAY FROM DateDone) else
Null)) as Jan_Day,
MAX(IF(EXTRACT(MONTH FROM DateDone) = 2 THEN EXTRACT(DAY FROM DateDone) else
Null)) as Feb_Day,
MAX(IF(EXTRACT(MONTH FROM DateDone) = 3 THEN EXTRACT(DAY FROM DateDone) else
Null)) as Mar_Day,
MAX(IF(EXTRACT(MONTH FROM DateDone) = 4 THEN EXTRACT(DAY FROM DateDone) else
Null)) as Apr_Day,
MAX(IF(EXTRACT(MONTH FROM DateDone) = 5 THEN EXTRACT(DAY FROM DateDone) else
Null)) as May_Day,
MAX(IF(EXTRACT(MONTH FROM DateDone) = 6 THEN EXTRACT(DAY FROM DateDone) else
Null)) as Jun_Day,
MAX(IF(EXTRACT(MONTH FROM DateDone) = 7 THEN EXTRACT(DAY FROM DateDone) else
Null)) as Jul_Day,
MAX(IF(EXTRACT(MONTH FROM DateDone) = 8 THEN EXTRACT(DAY FROM DateDone) else
Null)) as Aug_Day,
MAX(IF(EXTRACT(MONTH FROM DateDone) = 9 THEN EXTRACT(DAY FROM DateDone) else
Null)) as Sep_Day,
MAX(IF(EXTRACT(MONTH FROM DateDone) = 10 THEN EXTRACT(DAY FROM DateDone)
else Null)) as Oct_Day,
MAX(IF(EXTRACT(MONTH FROM DateDone) = 11 THEN EXTRACT(DAY FROM DateDone)
else Null)) as Nov_Day,
MAX(IF(EXTRACT(MONTH FROM DateDone) = 12 THEN EXTRACT(DAY FROM DateDone)
else Null)) as Dec_Day
FROM MyTable
where DateDone BETWEEN :StartDate AND :EndDate
group by CarNumber

John

Thanks for the effort but not exactly.
Maybe this will clarify my problem.

I have the table

CarNumber        DateDone
1                        25/01/2010
1                        24/02/2010
......
2                        15/02/2010
2                        13/02/2010
.....

from this I want to create

CarNumber        Jan_Day        Feb_Day ....
1                        25                  24
2                        15                  13

Thanks
Sorin

Sorin

> Thanks for the effort but not exactly.
> Maybe this will clarify my problem.
>
> I have the table
>
> CarNumber        DateDone
> 1                        25/01/2010
> 1                        24/02/2010
> .....
> 2                        15/02/2010

I presume this is a type ie it should be 15/01/2010

> 2                        13/02/2010

> CarNumber        Jan_Day        Feb_Day ....
> 1                        25                  24
> 2                        15                  13
>

Unless I am missing something this is exactly what it does.  Did you try it?

John

>> I have the table
>>
>> CarNumber        DateDone
>> 1                        25/01/2010
>> 1                        24/02/2010
>> .....
>> 2                        15/02/2010
>
> I presume this is a type ie it should be 15/01/2010

Yes it's my mistake

>> CarNumber        Jan_Day        Feb_Day ....
>> 1                        25                  24
>> 2                        15                  13
>>
>
> Unless I am missing something this is exactly what it does.  Did you try
> it?

Yes I did
I get the result

CarNumber        Jan_Day        Feb_Day
1                        25                   Null
1                        Null                24
.....

Thanks
Sorin

Sorin,

I think that my suggestion (adding 12 "LEFT JOIN" tables, one for each month, plus a "WHERE limiting each of the 12 joined Tasks tables to only span 1 month) should work.

I think John's SQL would also work if a GROUP BY was added on CarNumber.

Adam

Adam

>
> I think John's SQL would also work if a GROUP BY was added on CarNumber.

There is a GROUP BY and it does work <bg>

John

> I think that my suggestion (adding 12 "LEFT JOIN" tables, one for each
> month, plus a "WHERE limiting each of the 12 joined Tasks tables to only
> span 1 month) should work.
>
> I think John's SQL would also work if a GROUP BY was added on CarNumber.

Adam

I didn't try your's sql because I don't thing it suits my case

There is only one table with the cars numbers and the dates.
There isn't a separate tasks table to join.

I try John's solution with GROUP BY but this limit the results to one row
per car
but the others fields are all nulls.

Thanks
Sorin

Sorin
>
> I try John's solution with GROUP BY but this limit the results to one row
> per car
> but the others fields are all nulls.

Surely it is meant to be 1 row per car.  With the data you posted I get

Car Number  Jan_Day  Feb_Day  Mar_Day....
1                   25            24           Null
2                   15            13           Null

John