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_date(?,'%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_date(?,'%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





Actual sum 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;