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
|
select DTLIV,GUE.LBSPRGUE,GUE.LBGUEPRX,CLA.CDUEXSMR,CLA.CDUEXSMR+'-'+cla.LBUEXSMR as UE,CLA.LBFAM
,SUM(rupt.MtPCILIV) as LIV,sum(rupt.MTPCICDE) as CDE
from PRX_FLX.LIVRAISON RUPT
inner join PRX_REF.PRODUIT PRD
on RUPT.ID_PRD=PRD.ID_PRD
inner join prx_ref.classi_produit CLA
on DTANNCLA=YEAR(DATEADD("DD",-1,cast(GETDATE() as date)))
and PRD.CDFAM=CLA.CDFAM
and coalesce(PRD.CDSFA,'01')=CLA.CDSFA
inner join PRX_PARAM.GROUPES_UE GUE
on CLA.CDUEXSMR=GUE.CDUEXSMR
and CLA.DtANNCLA=GUE.DTANN
where DTLIV between DATEADD("DD",-7,cast(GETDATE() as date)) and DATEADD("DD",-1,cast(GETDATE() as date))
and
(CDPRD_A in (select Code from prx_wrk.PRODUIT_TauxSERVICE where TYPE like 'produit' and IN_OUT>0)
or PRD.CDFAM in (select Code from prx_wrk.PRODUIT_TauxSERVICE where TYPE like 'famille' and IN_OUT>0)
or PRD.CDFAM in (select Code from prx_wrk.PRODUIT_TauxSERVICE where TYPE like 'UE' and IN_OUT>0)
)
group by DTLIV,GUE.LBSPRGUE,GUE.LBGUEPRX,CLA.CDUEXSMR,CLA.CDUEXSMR+'-'+cla.LBUEXSMR,CLA.LBFAM |
Partager