none
create a bucketing logic RRS feed

  • Question

  • There is a requirement to create some buckets like 0-30 days,30-60 days,60-90 days and >90 days as per the value in a column x for each customer

    0-30 days: If x=0 for one month for a customer
    30-60 days: If x=0 for two consecutive months for a customer
    60-90 days: If x =0 for consecutive 3 months for a customer
    >90 days: If x=0 for greater than 3 consecutive months for a customer

    No customer can lie in more than 1 bucket;so we will have to consider the max bucket for each customer.

    Please help me with the logic.
    Thursday, June 13, 2019 6:42 AM

All replies

  • Can anyone please help me for the same.
    Thursday, June 13, 2019 12:44 PM
  • The posted link provides guidance for posting technical questions. You can provide sufficient information for others (and hope that they find the kindness in their hearts to assist), search the internet for similar discussions and suggestions, or ask someone at your own firm to help. 

    A starting point for searching 

    Thursday, June 13, 2019 1:43 PM
  • Hi Team,

    Customer id Month Sum

    x                 Jan       0

    x                 March    0

    x                 April      0

    x                 June       0

    x                 July       0

    x                 August   0

    As per the image the customer x has 0 value in sum column for Jan;it lies in 0-30 days

    Now again in the next row the value of sum is 0 for 2 consecutive months(March and April) so it should lie in 30-60 days

    After this the value of sum is 0 for 3 consecutive months(June,July and August) so it should lie in 60-90 days.

    Thus the maximum value of bucket for this customer is 60-90 days.

    Please let me know if this helps in understanding my requirement

    Friday, June 14, 2019 4:21 AM
  • Thanks for your reply.

    Since you have provided sample data with detailed explanation, what is your desired output?

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 14, 2019 5:39 AM
    Moderator
  • Hi,


    Customer id Month Sum

    x                 Jan       0

    x                 March    0

    x                 April      0

    x                 June       0

    x                 July       0

    x                 August   0

    y                Feb         0

    z                June       0

    z               July         0

    My deisred output is as per the sample data above

    Customer id  Bucket

    x                 60-90 days

    y                0-30 days

    z                30-60 days

    Please let me know if any other information is required from my end.

    Friday, June 14, 2019 5:56 AM
  • Hi,


    Customer id Month Sum

    x                 Jan       0

    x                 March    0

    x                 April      0

    x                 June       0

    x                 July       0

    x                 August   0

    y                Feb         0

    z                June       0

    z               July         0

    My deisred output is as per the sample data above

    Customer id  Bucket

    x                 60-90 days

    y                0-30 days

    z                30-60 days

    Please let me know if any other information is required from my end.

    OK, please try

    create table bucket
    (
     [Customer id] varchar(64),
     [Month] varchar(64),
     [Sum] int
     )
    
     insert into bucket values
     ('x','Jan',0),
     ('x','Mar',0),
     ('x','Apr',0),
     ('x','Jun',0),
     ('x','Jul',0),
     ('x','Aug',0),
     ('y','Feb',0),
     ('z','Jun',0),
     ('z','Jul',0)
    
     --Query
     ;WITH GRAP_CTE AS
     (
     SELECT 
     [Customer id],
     CASE [Month] WHEN 'Jan' THEN 1 WHEN 'Feb' THEN 2 WHEN 'Mar' THEN 3 WHEN 'Apr' THEN 4 WHEN 'May' THEN 5 WHEN 'Jun' THEN 6 WHEN 'Jul' THEN 7 WHEN 'Aug' THEN 8 WHEN 'Sep' THEN 9 WHEN 'Oct' THEN 10  WHEN 'Nov' THEN 11 WHEN 'Dec' THEN 12 END-ROW_NUMBER() OVER (PARTITION BY [Customer id],[Sum] ORDER BY CASE [Month] WHEN 'Jan' THEN 1 WHEN 'Feb' THEN 2 WHEN 'Mar' THEN 3 WHEN 'Apr' THEN 4 WHEN 'May' THEN 5 WHEN 'Jun' THEN 6 WHEN 'Jul' THEN 7 WHEN 'Aug' THEN 8 WHEN 'Sep' THEN 9 WHEN 'Oct' THEN 10  WHEN 'Nov' THEN 11 WHEN 'Dec' THEN 12 END) AS GRAP
     FROM bucket
     ),
     GROUP_G AS
     (
      SELECT [Customer id],GRAP, COUNT(*) AS V_COUNT  FROM GRAP_CTE
      GROUP BY [Customer id],GRAP
     )
     SELECT 
     DISTINCT T.[Customer id], CASE WHEN T1.MAX_COUNT=1 THEN '0-30 days' WHEN T1.MAX_COUNT=2 THEN '30-60 days' WHEN T1.MAX_COUNT=3 THEN '60-90 days' ELSE '>90 days' END AS Bucket
     FROM bucket T
     CROSS APPLY (SELECT MAX(V_COUNT) AS MAX_COUNT FROM GROUP_G WHERE  [Customer id]=T.[Customer id]) T1

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 14, 2019 10:00 AM
    Moderator