Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2013
    Posts
    1

    Unanswered: Help Needed For joins

    Hi All,

    I am new to oracle.I have a requirement. i need to display sales fields for a particular time as below.

    *Time Sales*

    6-7 3333
    7-8 45345
    8-9 546
    ....................

    But the min time and max time are coming from different query and sales fields coming from different.Now i need to join both queries to get the results.But there is no common column.I tried with sub query there also i am getting error.Please find the queries and help me

    *Hour Group Query:*


    select

    time1.*,

    -- (select concat(?,' ',time_format(ifnull((SELECT prop_value FROM cust_conv_properties c where prop_key = 'DayStartingTime'), '00:00:00'), '%H:%i:%s'))) as fTime,
    (select concat(date_format(str_to_date(?,'%d/%m/%Y'),'%Y-%m-%d'),' ',time_format(ifnull((SELECT prop_value FROM cust_conv_properties c where prop_key = 'DayStartingTime'), '00:00:00'), '%H:%i:%s'))) as fTime,
    (select if(time1.max_value <= time_format(ifnull((SELECT prop_value FROM cust_conv_properties c where prop_key = 'DayStartingTime'), '00:00:00'), '%H'),
    (select date_format(concat(date_add(date_format(str_to_dat e(?,'%d/%m/%Y'),'%Y-%m-%d'),interval 1 day),' ',time_format(str_to_date(if(time1.max_value=24,'0 ',time1.max_value),'%H'),'%H:%i:%s')),'%Y-%m-%d %H:%i:%s')),
    -- (select date_format(concat(date_format(str_to_date(?,'%d/%m/%Y'),'%Y-%m-%d'),' ',time_format(str_to_date(if(time1.max_value=24,'0 ',time1.max_value),'%H'),'%H:%i:%s')),'%Y-%m-%d %H:%i:%s')))
    if(time1.max_value=24,(select date_format(concat(date_add(date_format(str_to_dat e(?,'%d/%m/%Y'),'%Y-%m-%d'),interval 1 day),' ',time_format(str_to_date(if(time1.max_value=24,'0 ',time1.max_value),'%H'),'%H:%i:%s')),'%Y-%m-%d %H:%i:%s')),(select date_format(concat(date_format(str_to_date(?,'%d/%m/%Y'),'%Y-%m-%d'),' ',time_format(str_to_date(if(time1.max_value=24,'0 ',time1.max_value),'%H'),'%H:%i:%s')),'%Y-%m-%d %H:%i:%s'))))
    )as tTime,

    (select if (time_format(dt.curr_time, '%H') <
    time_format(ifnull((SELECT prop_value FROM cust_conv_properties c where prop_key = 'DayStartingTime'), '00:00:00'), '%H'),
    date_format(date_add(dt.curr_time, INTERVAL 0 DAY),'%d/%m/%Y'),
    date_format(dt.curr_time,'%d/%m/%Y')) as curr_date
    from
    (select concat(date_format(str_to_date('23/10/2011','%d/%m/%Y'),'%Y-%m-%d'), ' ', '00:00:00') as curr_time) dt) as report_date
    from
    (select
    cast(min(cchg.from_hour) as unsigned) as min_value,
    cast(max(cchg.to_hour) as unsigned) as max_value,
    concat(concat(min(cchg.from_hour),''),'-',concat(max(cchg.to_hour),'')) as time_diff
    from
    cust_conv_hour_group as cchg
    where
    cchg.group_value in (SELECT distinct if(group_value = (SELECT min(group_value) FROM cust_conv_hour_group),
    (SELECT max(group_value) FROM cust_conv_hour_group), group_value-1) as group_value
    FROM cust_conv_hour_group c where from_hour <= cast(time_format('23:00:00','%H') as unsigned)

    and

    to_hour > cast(time_format('00:00:00','%H') as unsigned))
    group by
    cchg.group_value
    ) time1



    *Sales Fields Query*:




    select
    if(count(cons.wr_tarSale)=0,0, cons.wr_tarSale) wr_tarSale,
    if(count(cons.wr_actSale)=0,0, cons.wr_actSale) wr_actSale,
    if(count(cons.wr_tarTrans)=0,0, cons.wr_tarTrans) wr_tarTrans,
    if(count(cons.wr_actTrans)=0,0, cons.wr_actTrans) wr_actTrans,
    if(count(cons.tarSale)=0,0, cons.tarSale) tarSale,
    if(count(cons.actSale)=0,0, cons.actSale) actSale,
    if(count(cons.varSale)=0,0, cons.varSale) varSale,
    if(count(cons.tarTrans)=0,0, cons.tarTrans) tarTrans,
    if(count(cons.actTrans)=0,0, cons.actTrans) actTrans,
    if(count(cons.actAds)=0,0, cons.actAds) actAds,
    if(count(cons.actUpt)=0,0, cons.actUpt) actUpt,
    if(count(cons.tarAds)=0,0, cons.tarAds) tarAds,
    if(count(cons.tarUpt)=0,0, cons.tarUpt) tarUpt,
    if(count(cons.actConv)=0,0, cons.actConv) actConv,
    if(count(cons.tarTplh)=0,0, cons.tarTplh) tarTplh,
    if(count(cons.actBlank1)=0,0, cons.actBlank1) actBlank1,
    if(count(cons.actBlank2)=0,0, cons.actBlank2) actBlank2,
    if(count(cons.actTplh)=0,0, cons.actTplh) actTplh,
    if(count(cons.sDTarSale)=0,0, cons.sDTarSale)sDTarSale,
    if(count(cons.sDTarTrans)=0,0, cons.sDTarTrans)sDTarTrans,
    if(count(cons.sDTarQty)=0,0, cons.sDTarQty)sDTarQty,
    if(count(cons.sDTarFF)=0,0, cons.sDTarFF)sDTarFF
    FROM
    (
    SELECT
    ifnull(sum(ccsr.target_sale),0) wr_tarSale,
    ifnull(sum(ccsr.actual_sale),0) wr_actSale,
    ifnull(sum(ccsr.actual_transaction_count),0) wr_actTrans,
    ifnull(sum(ccsr.target_transaction_count),0) wr_tarTrans,
    round(ifnull(sum(ccsr.target_sale),0),2) tarSale,
    round(ifnull(sum(ccsr.actual_sale),0),2) actSale,
    round(round(ifnull(sum(ccsr.actual_sale),0),2)- round(ifnull(sum(ccsr.target_sale),0),2),2) varSale,
    round(ifnull(sum(ccsr.actual_transaction_count),0) ,0) actTrans,
    round(ifnull(sum(ccsr.target_transaction_count),0) ,0) tarTrans,
    0 actBlank1,
    round(ifnull((sum(ccsr.actual_sale)/sum(ccsr.actual_transaction_count)),0),2) actAds,
    round(ifnull((sum(ccsr.target_sale)/sum(ccsr.target_transaction_count)),0),2) tarAds,
    round(ifnull((sum(ccsr.actual_quantity)/sum(ccsr.actual_transaction_count)),0),2) actUpt,
    round(ifnull((sum(ccsr.target_quantity)/sum(ccsr.target_transaction_count)),0),2) tarUpt,
    round(ifnull((sum(ccsr.actual_transaction_count)/sum(ccsr.actual_footfalls))*100,0),2) actConv,
    0 actBlank2,
    0 actBlank3,
    round(ifnull((sum(ccsr.actual_footfalls)/sum(ccsr.actual_man_hours)),0),2) actTplh,
    round(ifnull((sum(ccsr.target_footfalls)/sum(ccsr.target_man_hours)),0),2) tarTplh,
    round(ifnull(ccsrs.target_sale,0),2) as sDTarSale,
    round(ifnull(ccsrs.target_transaction_count,0),0) as sDTarTrans,
    ifnull(ccsrs.target_quantity,0) as sDTarQty,
    ifnull(ccsrs.target_footfalls,0) as sDTarFF

    FROM
    cust_conv_store_report ccsr
    /*inner join*/ left outer join cust_conv_store_report_summary ccsrs on(ccsrs.brand_code=ccsr.brand_code and ccsrs.category=ccsr.category and
    ccsrs.store_date=ccsr.store_date and ccsrs.value_type=ccsr.value_type)
    WHERE
    ccsr.value_type='B' and
    ccsr.brand_code=? AND
    ccsr.store_date = date_format(str_to_date(?,'%d/%m/%Y'),'%Y-%m-%d')
    -- AND HOUR((TIME(ccsr.transaction_time))) >= (?) AND HOUR((TIME(ccsr.transaction_time)))< (?)
    AND ccsr.transaction_time >= date_format(str_to_date(?,'%Y-%m-%d %H:%i:%s'),'%Y-%m-%d %H:%i:%s') AND ccsr.transaction_time< date_format(str_to_date(?,'%Y-%m-%d %H:%i:%s'),'%Y-%m-%d %H:%i:%s')
    GROUP BY
    ccsr.brand_code
    ) cons;
    Last edited by vissubabau; 03-10-13 at 08:50. Reason: Need to Cange Thread

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Please NOTE that this sub-forum is specifically for Oracle RDBMS.
    Please post results from the SQL below so we can learn more about your specific DB.

    SELECT * FROM V$VERSION;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As long as you might think you use Oracle, I'm quite sure that code you posted is NOT Oracle.

    Anyway: it is useless posting desired result ("time sales" at the beginning of your message) when nobody knows what source you have. In order to provide that, post CREATE TABLE and INSERT INTO several sample records.

    Also, learn how to properly format your code and - in the future - enclose it into [code] tags which will preserve formatting. Because, code you posted is almost impossible to read and follow.

Posting Permissions

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