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
| SELECT
R1.INVOICEACCOUNT,
R1.SEGMENT,
R1.NB_YEAR,
R1.NB_MONTH,
R1.NB_FACTURES,
R1.MAX_DATE as DERNIERE_DATE,
R2.MIN_DATE as DEBUT_ANALYSE,
DATEDIFF(month,R1.MAX_DATE,R2.MIN_DATE) as NB_MOIS_DIFF,
DATEADD(month,12,R2.MIN_DATE) as FIN_ANALYSE,
'NOUVEL FACTURATION' as TYPE_CLIENT
FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY INVOICEACCOUNT, dimension7_ ORDER BY year(invoicedate), month(invoicedate)) AS POSITION,
INVOICEACCOUNT, dimension7_ AS SEGMENT, year(invoicedate) AS NB_YEAR, month(invoicedate) as NB_MONTH, count(*) AS NB_FACTURES, min(invoicedate) AS MIN_DATE, max(invoicedate) AS MAX_DATE
FROM CUSTINVOICEJOUR
WHERE
Dimension7_ <> ''
and Dimension7_ <> 'EMPTY'
GROUP BY INVOICEACCOUNT, dimension7_, year(invoicedate), month(invoicedate)) as R1
CROSS JOIN
(
SELECT ROW_NUMBER() OVER(PARTITION BY INVOICEACCOUNT, dimension7_ ORDER BY year(invoicedate), month(invoicedate)) AS POSITION,
INVOICEACCOUNT, dimension7_ AS SEGMENT, year(invoicedate) AS NB_YEAR, month(invoicedate) AS NB_MONTH, count(*) AS NB_FACTURES, min(invoicedate) as MIN_DATE, max(invoicedate) as MAX_DATE
FROM CUSTINVOICEJOUR
where
Dimension7_ <> ''
and Dimension7_ <> 'EMPTY'
group by INVOICEACCOUNT, dimension7_, year(invoicedate), month(invoicedate)) as R2
where R1.POSITION >1 and R1.INVOICEACCOUNT = R2.INVOICEACCOUNT and R1.SEGMENT = R2.SEGMENT and R2.POSITION = R1.POSITION +1
and DATEDIFF(month,R1.MAX_DATE,R2.MIN_DATE) >= 6 |
Partager