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 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55
| With controle as (
select
t.Facture,
max (t.DateFacture) as Date_Facture,
max (t.DateReglt) as Date_Reglt,
DATEDIFF(day,max (t.DateFacture),max (t.DateReglt)) AS NbJours,
case
when month(max (t.DateReglt))= 12 then '0'
else DATEDIFF(day,'31/12/08',max (t.DateReglt))
end as NbJours_Modif
from
(
-- RECHERCHE DES FACTURES ----------------------------------------
select [Document No_] as facture,
convert(char(10),[Posting Date],103) as DateFacture,
null as dateReglt
from [societe$Cust_ Ledger Entry]
where [Posting Date] between '01/12/08' and '31/12/08'
and [Document Type] = '2'
and [Facture Acompte] = '0'
and [Source Code] = 'ventes'
union all
-- RECHERCHE DES REGLEMENTS --------------------------------------
select
case when [Applies-to Doc_ No_] = ' ' then [External Document No_]
else [Applies-to Doc_ No_]
end as facture,
null as DateFacture,
convert(char(10),[Posting Date],103) as DateReglt
from [societe$Cust_ Ledger Entry]
where [Posting Date] between '01/12/08' and '31/05/09'
and [Facture Acompte] in('0')
--and [Source Code] in('vtereglt')
)t
group by t.facture
having max (t.DateFacture) is not null)
select
Facture,
Date_Facture,
Date_Reglt,
NbJours,
NbJours_Modif,
Case
When NbJours_Modif > '0' and NbJours_Modif <= '7' then '1'
When NbJours_Modif > '7' and NbJours_Modif <= '14' then '2'
When NbJours_Modif > '14' and NbJours_Modif <= '21' then '3'
When NbJours_Modif > '21' and NbJours_Modif <= '28' then '4'
When NbJours_Modif > '28' and NbJours_Modif <= '35' then '5'
When NbJours_Modif > '35' then '6'
End as Ratio
From controle |
Partager