开发者

I need to combine 2 queries into one on query 1 area to query 2 description and query 1 empl to query 2 employee_id

I have the following 2 queries and their output.

Query 1

select
types,area,avg(time2)as time,empl,whse,des
from(select  l.control_number_2 as types, l.control_number as area,
sum(round(cast(DATEDIFF(ss, l.start_tran_time,l.end_tran_time) /60.0 as float),2))over (partition by l.control_number) time2, l.employee_id as empl, l.wh_id as whse ,
(select distinct lu.description from t_lookup lu where lu.description = l.control_number) as des
from t_cp_work_area_log l 
where start_tran_date >= '2010-05-04 00:00:00.000' 
) t
group by whse, empl,types, area,des 

Query 2

select t.client_code,t.tran_type, t.employee_id,count(t.hu_id)as plts, lu.description,cs.prod_id, ps.fixed_std, ps.dynamic_std,(fixed_std/sum(fixed_std) OVER (PARTITION BY lu.description)) "fs_summed",fixed_std/60 as stand_per_min
from t_tran_log t, t_lookup lu, t_cp_client_prod_stds cs, t_cp_prod_stds ps
where lu.text = t.tran_type and lu.source in ('t_cp_prod_stds','t_cp_work_area')
and t开发者_如何学运维.client_code = cs.client_code and cs.prod_id = ps.prod_id and ps.work_type = lu.text and
t.start_tran_date >= '2010-05-04 00:00:00.000' 
group by t.tran_type, t.client_code,t.employee_id, cs.prod_id,lu.description,ps.fixed_std, ps.dynamic_std

Output query 1

types area time empl whse des
Inventory ADJ 7 TA C1 ADJ
LOG-ON LOG-ON 55.4 TA C1 LOG-ON
Outbound LDG 62.7 TA C1 NULL
Outbound PCKG 11.45 TA C1 NULL
Receiving RCVG 8.73 TA C1 RCVG

Output query 2

client_code tran_type emplyee_id plts description prod_id fixed_std dynamic_std fs_summed stand_per_min
826 853 TA 2 ADJ 13 50 50 1 0.833333
810 114 TA 1 RCVG 4 50 50 0.555555556 0.833333
826 114 TA 1 RCVG 11 40 40 0.444444444 0.666666


Although I barely understand your question, I think you are just asking how to do a join.

SELECT *  -- better to explicitly list the columns you need here
  FROM
  ( <text of query1> ) q1
  JOIN
  ( <text of query2> ) q2
  ON q1.area = q2.description AND q1.empl = q2.employee_id
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜