Issues joining and union tables ???
Hi to all,
need some help on my select statement...
trying to join the following 2 select statement but not coming out the way i need it...
1st select returns all info needed...
2nd select get me all units sold between the dates...
together i want to return all info having the units sold for each store else put a zero
select * from (
SELECT StoreID as 'StoreID',City as 'City', S.INUMBR as 'SKU',IBPOOQ as 'IBPOOQ',
sum(S.IBHAND+S.IBINTQ) as 'OH',I.ATT as 'ATT',sum(IBWKCR) as 'TW',sum(IBWK01) as 'LW',
SUM(IBRSUP) as 'MTD',SUM(IUSECR) as 'STD', SUM(IUSE02) as 'SSLY',
SUM(IUSE01) as 'PS', SUM(IUSE03) as 'PSLY'
FROM MMS_SKUStore S,Stores,MMS_SKUXRef X, MMS_INVBAL_WHS I
WHERE S.INUMBR=X.INUMBR AND StoreID=S.ISTORE
AND S.INUMBR=I.INUMBR AND I.ISTORE = 4
GROUP BY StoreID,City,S.INUMBR,IBPOOQ,I.ATT
) as EAM
select SUM(H.SKSNU) as 'Units',MMS_SKUXRef.INUMBR, H.ITRLOC from
join MMS_SKUXRef on MMS_SKUXRef.INUMBR = H.INUMBR
where H.ITRDAT between '121001' and '121007'
group by MMS_SKUXRef.INUMBR, H.ITRLOC
) as LDF
on EAM.SKU = LDF.INUMBR and EAM.StoreID = LDF.ITRLOC
thank you in advance