Diviser les nombres en plages dans SQL

Question

J'essaie d'écrire une requête SQL qui montre la distribution de l'ancienneté des clients depuis le 2015/01/31 :

1. Jusqu'à un mois
2. Entre un et six mois
3. Entre six mois et un an
4. Plus d'un an

J'ai réussi à diviser et à regrouper le nombre de mois des clients.

SELECT Seniority, COUNT(Customer_ID) [Number of Customers]
FROM
(SELECT Customer_ID,
DATEDIFF(MONTH, MIN(CONVERT(datetime, Order_Date)), '2015/01/31') Seniority
FROM Orders
GROUP BY Customer_ID) t
GROUP BY Seniority

Comment puis-je diviser par des plages données ?

Attendu :

        Seniorty                |   Number of Customers
Up to one month                 |            0
Between one and six months      |            14
Between six months and one year |            1
Over a year                     |            0

Answer 1

Utiliser l'agrégation conditionnelle :

WITH cte AS (
    SELECT
        'Up to one month' AS Seniority,
        COUNT(CASE WHEN DATEDIFF(MONTH, Order_Date, '2015-01-31') < 1 THEN 1 END) AS [Number of Customers],
        1 AS position
    FROM Orders
    UNION ALL
    SELECT
        'Between one and six months',
        COUNT(CASE WHEN DATEDIFF(MONTH, Order_Date, '2015-01-31') >= 1 AND
                        DATEDIFF(MONTH, Order_Date, '2015-01-31') < 6 THEN 1 END),
        2
    FROM Orders
    UNION ALL
    SELECT
        'Between six months and one year',
         COUNT( CASE WHEN DATEDIFF(MONTH, Order_Date, '2015-01-31') >= 6 AND
                        DATEDIFF(MONTH, Order_Date, '2015-01-31') < 12 THEN 1 END),
         3
    FROM Orders
    UNION ALL
    SELECT
        'Over a year',
        COUNT(CASE WHEN DATEDIFF(MONTH, Order_Date, '2015-01-31') > 12 THEN 1 END),
        4
    FROM Orders
)

SELECT
    Seniority,
    [Number of Customers]
FROM cte
ORDER BY
    position;

Cette réponse suppose que le Order_Date est déjà une date ou une période de temps. Si ce n'est pas le cas, la première chose à faire est de convertir cette colonne en un type de date réel.

Answer 2

WITH CTE AS (
    SELECT CUSTOMER_ID,
           CASE WHEN DATEDIFF(MONTH,'2015-01-31',ORDER_DATE)=1 THEN 'Up to one month'
               WHEN DATEDIFF(MONTH,'2015-01-31',ORDER_DATE) BETWEEN 1 AND 6 THEN 'Between one and six months'
               WHEN DATEDIFF(MONTH,'2015-01-31',ORDER_DATE) BETWEEN 6 AND 12 THEN 'Between six months and one year'
               WHEN DATEDIFF(MONTH,'2015-01-31',ORDER_DATE)>12 THEN 'Over a year'
           END AS SENIORITY
      FROM ORDERS 
     )
SELECT SENIORITY AS 'Seniority', COUNT(CUSTOMER_ID) AS 'Number of Customers'
FROM CTE
WHERE SENIORITY IS NOT NULL
GROUP BY SENIORITY