...

View Full Version : how to concat 2 columns in separate rows



helenp
01-09-2012, 11:40 AM
Hi I have a query wich gives this result:
Property season start end date
Casa_Blanca_4 Christmas start 2012-01-01
Casa_Blanca_4 Christmas end 2012-01-01
Casa_Blanca_4 Low start 2012-01-07
Casa_Blanca_4 Low end 2012-05-31

As you can see I have a column saying it starts here which it startdate and another column wich say end with its end dates.
I would like this result:
Property season startdate enddate
Casa_Blanca_4 Christmas 2012-01-01 2012-01-01
Casa_Blanca_4 Low 2012-01-07 2012-05-31

This is the query:
SELECT property, description, start, end, cal_date FROM calendar_table
WHERE start = 'start' or end = 'end' order by property, cal_date

Is this possible to do?
Thanks in advance

theghostofc
01-09-2012, 12:30 PM
Hii helenp

This is an interesting question :)

I have 3 solutions for you - one which you want (#3) and rest are my suggestions (#1 and #2).

#1. if possible change the structure of the calendar_table to:
Property (varchar), Season (varchar), StartDate (date not null), EndDate (date)
This would give you output with minimum efforts :) -

select * from calendar_table;

#2. Add another column calendar_id, to uniquely map one start and one end entry. This would be required if the database can have multiple entries for same property and season. If so then you can run the following query:

select l.property, l.season, l.sedate, r.sedate from calendar_table l inner join calendar_table r on l.calendar_id=r.calendar_id where l.start_end='start' and r.start_end='end';

This is perhaps the solution which you want:
#3. Do a simple join in SQL query, as:

select l.property, l.season, l.sedate, r.sedate from calendar_table l, calendar_table r where l.property=r.property and l.season=r.season and l.start_end='start' and r.start_end='end';

I am not sure if you want NULL values for end-date or not. If not, then try to apply an inner join.

For best performance, create a view on this SQL query.

Cheers

helenp
01-09-2012, 01:20 PM
Hii helenp

This is an interesting question :)

I have 3 solutions for you - one which you want (#3) and rest are my suggestions (#1 and #2).

#1. if possible change the structure of the calendar_table to:
Property (varchar), Season (varchar), StartDate (date not null), EndDate (date)
This would give you output with minimum efforts :) -

select * from calendar_table;

#2. Add another column calendar_id, to uniquely map one start and one end entry. This would be required if the database can have multiple entries for same property and season. If so then you can run the following query:

select l.property, l.season, l.sedate, r.sedate from calendar_table l inner join calendar_table r on l.calendar_id=r.calendar_id where l.start_end='start' and r.start_end='end';

This is perhaps the solution which you want:
#3. Do a simple join in SQL query, as:

select l.property, l.season, l.sedate, r.sedate from calendar_table l, calendar_table r where l.property=r.property and l.season=r.season and l.start_end='start' and r.start_end='end';

I am not sure if you want NULL values for end-date or not. If not, then try to apply an inner join.

For best performance, create a view on this SQL query.

Cheers

Sorry, but I dont understand much of it.
#1 The dates are in one column called cal_date, then I added 2 columns to mark the start and end date, so I dont see how I can concat the result.
#2 I can add another column, however the id cant be autoincremented as start and end dates are in diferent rows, so not sure how to add an id. and the query is similar as in #3 which I dont understand part of it.
#3 using this
select l.property, l.description, l.start, r.end from calendar_table l,
calendar_table r where l.property=r.property
and l.description=r.description and l.start='start' and r.end='end' I get the result correctly, and also I need the column cal_date and if I do the same l.cal_date=r.cal_date I get an empty result.

Thanks for the help

helenp
01-09-2012, 02:25 PM
Dont get it,
If I do this I get 0 result

select l.property, l.description, l.cal_date, l.start, r.end from calendar_table l,
calendar_table r where l.property=r.property
and l.description=r.description and l.cal_date = r.cal_date and l.start='start' and r.end='end'

however if I do this I do get the dates:

SELECT l.cal_date
FROM calendar_table l, calendar_table r
WHERE l.cal_date = r.cal_date

And if I do your suggestion I get everything but I miss the date:

select l.property, l.description, l.start, r.end from calendar_table l,
calendar_table r where l.property=r.property
and l.description=r.description and l.start='start' and r.end='end'

helenp
01-09-2012, 06:01 PM
I have to add, that first I add a new property with dates for 2 years,
and then I update the daterows with prices, season etc.
So there are one row for every day, however the day and startdate of a season are marked in a row and have a unique row each.

Keleth
01-09-2012, 08:51 PM
The table structure you showed in your first post don't match the query you showed in your second to last post. Can you show your create statements for us to see?

I agree theghostofc. The best bet would be to have a single row that contains all your data, rather then two different rows with mostly the same data and one column being different. Is there a reason why you have two different rows, one for start date, and one for end, rather then a column for start, and a column for end? It does sound like your tables could be restructured for maximum ease now and the future.

However

select l.property, l.description, l.cal_date, l.start, r.end from calendar_table l,
calendar_table r where l.property=r.property
and l.description=r.description and l.cal_date = r.cal_date and l.start='start' and r.end='end'

definitely won't work, and isn't what was suggested. If you read that code, it says to give you results where the property is the same, the description is the same, and the date is the same. It would only give you results where the start and end dates are the same. Remove and l.cal_date = r.cal_date and it should work fine.

But again, I'd recommend into possibly changing your table structures, or let us know the full structure so we can make better recommendations.

helenp
01-09-2012, 10:52 PM
The table structure you showed in your first post don't match the query you showed in your second to last post. Can you show your create statements for us to see?

I agree theghostofc. The best bet would be to have a single row that contains all your data, rather then two different rows with mostly the same data and one column being different. Is there a reason why you have two different rows, one for start date, and one for end, rather then a column for start, and a column for end? It does sound like your tables could be restructured for maximum ease now and the future.

However

select l.property, l.description, l.cal_date, l.start, r.end from calendar_table l,
calendar_table r where l.property=r.property
and l.description=r.description and l.cal_date = r.cal_date and l.start='start' and r.end='end'

definitely won't work, and isn't what was suggested. If you read that code, it says to give you results where the property is the same, the description is the same, and the date is the same. It would only give you results where the start and end dates are the same. Remove and l.cal_date = r.cal_date and it should work fine.

But again, I'd recommend into possibly changing your table structures, or let us know the full structure so we can make better recommendations.

I dont think I gave the tablestructure, only the query and result?
Anyway,
I have a row with each day of the year (date column) and one for each property,
this is to easier and with more flexibility being able to calculate prices only summing the daily price instead of using start and endates as I have at this moment wich is a very complicated query and I have to use the same season and dates for all properties. (you wont see that as inclusive the person that helped dont understands it. :))
The only reason to add a column for start and a column for end is just to be able to view prices from start to end date (to know when it start and ends, and start and end are not dates, they are just varchar text. it is imposible to add start and end to the same row as they are diferent days.

I did not think that the end and start works as a where clause. however I do need the cal_date.

Not sure wich create statement you want, I created the table with phpmyadmin,
however to add properties I use php to get the dates and then I insert the dates choosed (2 years) and the property:

$fecha1 = $llegada;
$fecha2 = $salida;

$data = dates_between($fecha1, $fecha2, 'Y-m-d');
foreach ($data as $date) {
$query = "insert into calendar_table (cal_date, property)" .
"VALUES ('$date', '$propiedad')";

Then to add the diferent seasons, prices etc.
I update as the dates are already in the table, doing this:

$elena_n=$elena/7;
$price_client=$price_client_week/7;
$price_owner=$price_owner_week/7;
$query = mysql_query("UPDATE calendar_table SET start='start'
WHERE cal_date='$llegada' and property='$propiedad'");
$query = mysql_query("UPDATE calendar_table SET start='start'
WHERE cal_date='$llegada2' and property='$propiedad'");
$query = mysql_query("UPDATE calendar_table SET end='end'
WHERE cal_date='$salida'and property='$propiedad'");
$query = mysql_query("UPDATE calendar_table SET end='end'
WHERE cal_date='$salida2'and property='$propiedad'");
$query = mysql_query("UPDATE calendar_table SET price_client='$price_client',
price_owner='$price_owner', price_client_week='$price_client_week',
price_owner_week='$price_owner_week', description='$description', elena='$elena', elena_n='$elena_n', minimo='$minimo'
WHERE cal_date between '$llegada' and '$salida' and property='$propiedad'");
$query = mysql_query("UPDATE calendar_table SET price_client='$price_client',
price_owner='$price_owner', price_client_week='$price_client_week',
price_owner_week='$price_owner_week', description='$description', elena='$elena', elena_n='$elena_n', minimo='$minimo'
WHERE cal_date between '$llegada2' and '$salida2' and property='$propiedad'");

Thanks a lot

theghostofc
01-10-2012, 10:31 AM
Hii helenp

I think the confusion could be due to different column names.
Could you please post a "select *" column from the calendar_table, with column names? Please share the structure which you would actually use.

You can also go with having two rows one with start date and another with end date.

The query which i had given returned the output which you had expected in your first post. Not sure why you could not get the desired output :(

Cheers

helenp
01-10-2012, 12:06 PM
Hii helenp

I think the confusion could be due to different column names.
Could you please post a "select *" column from the calendar_table, with column names? Please share the structure which you would actually use.

You can also go with having two rows one with start date and another with end date.

The query which i had given returned the output which you had expected in your first post. Not sure why you could not get the desired output :(

Cheers

Thanks a lot for your time, however I think I did something simple complicated.
The query you gave me worked however it only gave me property, the words starts and end however no dates saying when start and end was, so I needed to add column cal_date which had diferent start and enddates, so of course I could not add it to the query.

This morning first I added an auto id, then I tried to select the id before I updated the rows (the dates that were already in the table) and tried to insert the id to a column called comun_id, this failed.

Then I thought how stupid, inserting a column saying start when the season starts and end when the season ends.
I deleted those columns and added 2 date columns with value null called startdate and enddate.
So when I update the rows, on the date the season starts I add the startdate and endate (on the same row).
So now I do this:
First I add the dates calculating the dates between choosen dates with php and insert the dates for 2 years + name of property in table.

Then I update those rows (doing both years at the same time) (so next year I will only have to update the year that have ended, dont know how to do yet, but suppose is possible):

$elena_n=$elena/7;
$price_client=$price_client_week/7;
$price_owner=$price_owner_week/7;
$query = mysql_query("UPDATE calendar_table2 SET startdate='$llegada', enddate='$salida'
WHERE cal_date='$llegada' and property='$propiedad'");
$query = mysql_query("UPDATE calendar_table2 SET startdate='$llegada2', enddate='$salida2'
WHERE cal_date='$llegada2' and property='$propiedad'");
$query = mysql_query("UPDATE calendar_table2 SET price_client='$price_client',
price_owner='$price_owner', price_client_week='$price_client_week',
price_owner_week='$price_owner_week', description='$description',
elena='$elena', elena_n='$elena_n', minimo='$minimo'
WHERE cal_date between '$llegada' and '$salida' and property='$propiedad'");
$query = mysql_query("UPDATE calendar_table2 SET price_client='$price_client',
price_owner='$price_owner', price_client_week='$price_client_week',
price_owner_week='$price_owner_week', description='$description',
elena='$elena', elena_n='$elena_n', minimo='$minimo'
WHERE cal_date between '$llegada2' and '$salida2' and property='$propiedad'");

And then its very easy to select as I only need the row where the start and enddate is, the rest of the rows I dont need for this query:


SELECT property, minimo, price_client_week,
price_owner_week, elena, startdate, enddate, description FROM `calendar_table2`
WHERE property = 'Casa_Blanca_4'
AND startdate AND enddate is not null

Thanks a lot, It looked complicated but was not.

helenp
01-10-2012, 12:17 PM
Just to share, then its easy to calculate price for one or all property doing this:


$date = "$salida";
$newdate = strtotime ( '-1 day' , strtotime ( $date ) ) ;
$newdate = date ( 'Y-m-j' , $newdate );
$result4 = mysql_query ("SELECT SUM(price_owner) as price, minimo, property FROM calendar_table
WHERE property = '$propiedad'
AND cal_date BETWEEN ('$llegada') AND ('$newdate')", $dbh);

theghostofc
01-10-2012, 01:44 PM
Hii helenp
Hola!

It's good to know that you've found a solution :)

Just to add...you can just go ahead with only two date columns - startdate and enddate, and remove cal_date. (I'm saying this coz i am not sure what is the role of cal_date. Please ignore if it is useful)

To fetch rows for today's status you can fetch as:

$todaysdate = date ( 'Y-m-j' , $date );
$result4 = mysql_query ("select * from calendar_table2 where startdate<='$todaysdate' and enddate>'$todaysdate'", $dbh);

Hope this helps!

Cheers

helenp
01-10-2012, 01:54 PM
Hii helenp
Hola!

It's good to know that you've found a solution :)

Just to add...you can just go ahead with only two date columns - startdate and enddate, and remove cal_date. (I'm saying this coz i am not sure what is the role of cal_date. Please ignore if it is useful)

To fetch rows for today's status you can fetch as:

$todaysdate = date ( 'Y-m-j' , $date );
$result4 = mysql_query ("select * from calendar_table2 where startdate<='$todaysdate' and enddate>'$todaysdate'", $dbh);

Hope this helps!

Cheers

Thanks, But I dont think I can or is the best solution as I already had that.
In the cal_date rows each row have a price for every day during 2 years that I just sum, I sum dates in the future, not todays date as it is about rentals, and this way its easy to have diferent seasons etc for all properties.

The query using startdate and enddate, in this case start and end of month is a very long query with 2 kinds of cases and more than 50 each....
its like this and a long etc:

$result = mysql_query ("SELECT
(CASE WHEN ('$llegada' BETWEEN nov_inicio AND nov_fin and '$salida' BETWEEN nov_inicio AND nov_fin)
THEN sum(novbr) * (TO_DAYS('$salida') - TO_DAYS('$llegada')) ELSE NULL END) AS price,
(CASE WHEN ('$llegada' BETWEEN nov_inicio AND nov_fin and '$salida' > nov_fin)
THEN sum(novbr) * (TO_DAYS(dec_inicio) - TO_DAYS('$llegada'))ELSE NULL END) AS price1,
(CASE WHEN ('$llegada' < nov_inicio and '$salida' BETWEEN nov_inicio AND nov_fin)
THEN sum(novbr) * (TO_DAYS('$salida') - TO_DAYS(nov_inicio))ELSE NULL END) AS price2,

(CASE WHEN ('$llegada' BETWEEN dec_inicio AND dec_fin and '$salida' BETWEEN dec_inicio AND dec_fin)
THEN sum(decbr) * ( TO_DAYS('$salida') - TO_DAYS('$llegada'))ELSE NULL END) AS price3,
(CASE WHEN ('$llegada' BETWEEN dec_inicio AND dec_fin and '$salida' > dec_fin)
THEN sum(decbr) * (TO_DAYS(nav_inicio)- TO_DAYS('$llegada'))ELSE NULL END) AS price4,
(CASE WHEN ('$llegada' < dec_inicio and '$salida' BETWEEN dec_inicio AND dec_fin)
THEN sum(decbr) * (TO_DAYS('$salida') - TO_DAYS(dec_inicio))ELSE NULL END) AS price5,

And doing it having one row for each day, the queries are short and easy to modify and individualize, thanks a lot



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum