1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37
| SELECT par_code as ref
, SUM (tmp.qtystockhr)
, SUM (tmp.qtyencomhr)
, SUM (tmp.qtystockr)
, SUM (tmp.qtyencomr)
FROM gmao_prod.dbo.r5parts
LEFT outer JOIN (
SELECT distinct par_code as ref
, SUM (ISNULL (rsnr.sto_qty , 0)) as 'qtystockhr'
, 0 as 'qtyencomhr'
, SUM (ISNULL (rsr.sto_qty , 0)) as 'qtystockr'
, 0 as 'qtyencomr'
FROM gmao_prod.dbo.r5parts
LEFT outer JOIN r5stock rsnr ON rsnr.sto_part = par_code
AND (rsnr.sto_store IN ('m1-d' , 'm1-e' , 'm3-e' , 'm3-d' , 'ms-e'))
LEFT outer JOIN r5stock rsr ON rsr.sto_part = par_code
AND (rsr.sto_store IN ('mr-d' , 'mr-e' , 'mr-f' , 'mt-f'))
GROUP by par_code
UNION all
SELECT distinct par_code as ref
, 0 as 'qtystockhr'
, SUM (ISNULL (odhr.ono_qty , 0)) as 'qtyencomhr'
, 0 as 'qtystockr'
, SUM (ISNULL (odr.ono_qty , 0)) as 'qtyencomr'
FROM gmao_prod.dbo.r5parts
LEFT outer JOIN r5onorder odhr ON odhr.ono_part = par_code
AND (odhr.ono_store IN ('m1-d' , 'm1-e' , 'm3-e' , 'm3-d' , 'ms-e'))
LEFT outer JOIN r5onorder odr ON odr.ono_part = par_code
AND (odr.ono_store IN ('mr-d' , 'mr-e' , 'mr-f' , 'mt-f'))
GROUP by par_code) as tmp ON tmp.ref = par_code
GROUP by par_code
/*
, tmp.qtystockhr
, tmp.qtystockr
, tmp.qtyencomhr
, tmp.qtyencomr*/
ORDER by par_code |
Partager