Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    New Coder
    Join Date
    Oct 2011
    Posts
    65
    Thanks
    1
    Thanked 0 Times in 0 Posts

    how to concat 2 columns in separate rows

    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

  • #2
    New Coder
    Join Date
    Jan 2012
    Posts
    12
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Thumbs up

    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 -
    Code:
    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:
    Code:
    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:
    Code:
    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

  • #3
    New Coder
    Join Date
    Oct 2011
    Posts
    65
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by theghostofc View Post
    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 -
    Code:
    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:
    Code:
    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:
    Code:
    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
    Code:
    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
    Last edited by helenp; 01-09-2012 at 02:54 PM.

  • #4
    New Coder
    Join Date
    Oct 2011
    Posts
    65
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Dont get it,
    If I do this I get 0 result
    Code:
    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:
    Code:
    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:
    Code:
    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'
    Last edited by helenp; 01-09-2012 at 02:54 PM.

  • #5
    New Coder
    Join Date
    Oct 2011
    Posts
    65
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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.

  • #6
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,546
    Thanks
    45
    Thanked 259 Times in 256 Posts
    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
    Code:
    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.

  • #7
    New Coder
    Join Date
    Oct 2011
    Posts
    65
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Keleth View Post
    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
    Code:
    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:
    Code:
    $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:
    Code:
    $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
    Last edited by helenp; 01-09-2012 at 10:56 PM.

  • #8
    New Coder
    Join Date
    Jan 2012
    Posts
    12
    Thanks
    0
    Thanked 4 Times in 4 Posts
    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

  • #9
    New Coder
    Join Date
    Oct 2011
    Posts
    65
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by theghostofc View Post
    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):
    Code:
    $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:

    Code:
    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.
    Last edited by helenp; 01-10-2012 at 12:33 PM.

  • #10
    New Coder
    Join Date
    Oct 2011
    Posts
    65
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Just to share, then its easy to calculate price for one or all property doing this:
    Code:
        $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);

  • #11
    New Coder
    Join Date
    Jan 2012
    Posts
    12
    Thanks
    0
    Thanked 4 Times in 4 Posts
    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:
    Code:
    $todaysdate = date ( 'Y-m-j' , $date );
    $result4 = mysql_query ("select * from calendar_table2 where startdate<='$todaysdate' and enddate>'$todaysdate'", $dbh);
    Hope this helps!

    Cheers

  • #12
    New Coder
    Join Date
    Oct 2011
    Posts
    65
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by theghostofc View Post
    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:
    Code:
    $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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •