May 5, 2011 at 12:10 pm
Need a little help.
I've wrote a couple queries t1 scheduled hrs and t2 labour demand.
I want to join these queries but I'm not entirely sure how. I've tried doing a couple things but can't quite get it to work.
Here are the queries.
T1
SELECT b.name as BU,
lsbd.bus_date,
lw.name as 'Workgroup',
SUM(lsbd.lab_sc_hs_qy) as 'Schd Hrs'
FROM t_wh..f_gn_lbr_sch_bu_dy lsbd WITH (NOLOCK)
JOIN t_wh..lbr_wkp lw
ON lw.lbr_wkp_id = lsbd.lbr_wkp_id
JOIN tam..r_sys_da_acc bu WITH (NOLOCK)
ON bu.da_acc_id = lsbd.bu_id
WHERE lsbd.bus_date >= '05/30/2010'
AND lsbd.bus_date <= '06/05/2010'
AND lsbd.lbr_cos_co != 'n'
GROUP BY bu.name, lw.name, lsbd.bus_date
--HAVING bu.name = '12345'
ORDER BY bu.name, lw.name, lsbd.bus_date
T2
SELECT bu.name as BU,
lrbd.bus_date,
lw.name as 'Workgroup',
SUM(lrbd.aj_fe_qty)/4 as 'Hrs of Lbr Dmd'
FROM t_wh..f_gn_lbr_req_bu_dy lrbd WITH (NOLOCK)
JOIN t_wh..lbr_wkp lw
ON lw.lbr_wkp_id = lrbd.labor_wkp_id
JOIN tam..r_sys_da_acc bu WITH (NOLOCK)
ON bu.da_acc_id = lrbd.bu_id
WHERE lrbd.bus_date >= '05/30/2010'
AND lrbd.bus_date <= '06/05/2010'
GROUP BY bu.name, lw.name, lrbd.bus_date
--HAVING bu.name = '12345'
ORDER BY bu.name, lw.name, lrbd.bus_date
Any help would be great.
Thanks,
Jess.
May 5, 2011 at 12:31 pm
I'm assuming by "join" you mean have the two sum fields together in one row kind of thing. Right?
If so, it looks like the two queries are very similar to each other. Same tables, similar Where clauses. Can you come up with one Where clause that would get you the overlapped dataset? (I don't know your data, so I'm kind of stuck on suggesting anything there.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 5, 2011 at 1:19 pm
Thanks for the quick response.
If I do an new where clause, does it matter that some of the tables are stored in different db's? If that makes any sense? I'm kind of new to this if you can't already tell lol
Basically what I want is a report that will end up looking like this.
BUBusiness DateWorkgroup Scheduled Hours Labour Demand
1234 05/05/2011 02:30Grocery Workgroup 89.500000 89.250000
I had previously written these queries and I thought maybe I could do a quick join of both instead of writing a new one.
Still learning 🙂
May 6, 2011 at 12:59 pm
CTE might be the way to go here. You can use your same queries and just wrap them:
;WITH T1 as (
SELECT b.name as BU,
lsbd.bus_date,
lw.name as 'Workgroup',
SUM(lsbd.lab_sc_hs_qy) as 'Schd Hrs'
FROM t_wh..f_gn_lbr_sch_bu_dy lsbd WITH (NOLOCK)
JOIN t_wh..lbr_wkp lw
ON lw.lbr_wkp_id = lsbd.lbr_wkp_id
JOIN tam..r_sys_da_acc bu WITH (NOLOCK)
ON bu.da_acc_id = lsbd.bu_id
WHERE lsbd.bus_date >= '05/30/2010'
AND lsbd.bus_date <= '06/05/2010'
AND lsbd.lbr_cos_co != 'n'
GROUP BY bu.name, lw.name, lsbd.bus_date
--HAVING bu.name = '12345'
),
T2 as (
SELECT bu.name as BU,
lrbd.bus_date,
lw.name as 'Workgroup',
SUM(lrbd.aj_fe_qty)/4 as 'Hrs of Lbr Dmd'
FROM t_wh..f_gn_lbr_req_bu_dy lrbd WITH (NOLOCK)
JOIN t_wh..lbr_wkp lw
ON lw.lbr_wkp_id = lrbd.labor_wkp_id
JOIN tam..r_sys_da_acc bu WITH (NOLOCK)
ON bu.da_acc_id = lrbd.bu_id
WHERE lrbd.bus_date >= '05/30/2010'
AND lrbd.bus_date <= '06/05/2010'
GROUP BY bu.name, lw.name, lrbd.bus_date
--HAVING bu.name = '12345'
)
SELECT
T1.BU,
T1.bus_date,
T1.workgroup,
T1.[Schd Hrs],
T2.[Hrs of Lbr Dmd]
FROM
T1
INNER JOIN
T2
ON
T1.BU = T2.BU
AND
T1.bus_date = T2.bus_date
AND
T1.workgroup = T2.workgroup
ORDER BY
T1.BU,
T1.bus_date,
T1.workgroup;
May 7, 2011 at 7:04 am
Nice, this works.
thank you!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply