Teradata SQl experts , help please

[Suhaas]

15 minutes ago, Rabbo said:

ikada oka issue endhi ante consecutive 30 days ne 0 to 30 bucket lo vasthadi 

DATE_FIELD is from last 1 year ,

how my data is from july 2017 upto  july 2016

so all the july 2017 should come into 0 to 30 days bucket

 

Bro, I am not sure I understand your question completely. 

Consecutive dates undala to place a customer into a bucket?

Like if Customer A has 55 consecutive dates in the table he falls into Bucket 30 to 60, 

If Customer B has 95 consecutive dates in the tables he falls into Bucket 90 to 120

and similarly if Customer C has 30 or lesser consecutive dates in the table he falls into Bucket 0 to 30

Am I getting it right? 

[Rabbo]

2 minutes ago, Suhaas said:

Bro, I am not sure I understand your question completely. 

Consecutive dates undala to place a customer into a bucket?

Like if Customer A has 55 consecutive dates in the table he falls into Bucket 30 to 60, 

If Customer B has 95 consecutive dates in the tables he falls into Bucket 90 to 120

and similarly if Customer C has 30 or lesser consecutive dates in the table he falls into Bucket 0 to 30

Am I getting it right? 

yes, correct 

[Suhaas]

And if a customer has two or more sets of consecutive dates, what do we do?

For Example, 

If a customer has 40 consecutive dates and then has a break and they he has another 90 consecutive dates after the break? Which bucket does he fall into ?

[Rabbo]

1 minute ago, Suhaas said:

And if a customer has two or more sets of consecutive dates, what do we do?

For Example, 

If a customer has 40 consecutive dates and then has a break and they he has another 90 consecutive dates after the break? Which bucket does he fall into ?

Good question man 

we will take the latest consecutive dates and assign the bucket

[Suhaas]

2 minutes ago, Rabbo said:

Good question man 

we will take the latest consecutive dates and assign the bucket

Ok Bro. I have a meeting now. Will work on it and get back to you after. 

[Rabbo]

18 minutes ago, Suhaas said:

Ok Bro. I have a meeting now. Will work on it and get back to you after. 

Thank bro

[Rabbo]

bro got his from internet 

select customer_no, date_field,
   ROW_NUMBER() OVER (PARTITION BY customer_no, dummy ORDER BY date_field) as consecutive_days
from
 (
   select date_field, customer_no,
     date_field - ROW_NUMBER() OVER (PARTITION BY customer_no ORDER BY date_field) as dummy
   from
    (
      select distinct date_field, customer_no
      from 

    ) as dt
 ) as dt

[Suhaas]

This should work Bro, Let me know if it doesn't. It's a little dirty so you may need to clean up a little. 

WITH CTE AS (
  SELECT  Customer_No,[Date_Field] d,ROW_NUMBER() OVER(ORDER BY Customer_No, [Date_Field]) i
  FROM Table_Name
  GROUP BY Customer_No,[Date_Field]
),
 CTE2 AS (
SELECT Customer_No, (datediff(day,Min(d),Max(d))+1) AS CT, MIN(d) AS MIN_DATE,
CASE
 WHEN  (datediff(day,Min(d),Max(d))+1) >0 AND (datediff(day,Min(d),Max(d))+1) <=30 THEN '0 to 30'
 WHEN  (datediff(day,Min(d),Max(d))+1) >30 AND (datediff(day,Min(d),Max(d))+1)<=60 THEN '30 to 60'
 WHEN  (datediff(day,Min(d),Max(d))+1) >60 AND (datediff(day,Min(d),Max(d))+1)<=90 THEN '60 to 90'
 WHEN  (datediff(day,Min(d),Max(d))+1) >90 THEN '90 to 120'
END Bucket
FROM CTE
GROUP BY Customer_No,DATEDIFF(day,i,d)),
CTE3 AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY(Customer_No)ORDER BY MIN_DATE DESC) as ROW_NUM fROM CTE2)
SELECT Customer_No,Bucket  FROM CTE3 WHERE ROW_NUM = 1 ORDER BY Customer_No
 

[Rabbo]

57 minutes ago, Suhaas said:

This should work Bro, Let me know if it doesn't. It's a little dirty so you may need to clean up a little. 

WITH CTE AS (
  SELECT  Customer_No,[Date_Field] d,ROW_NUMBER() OVER(ORDER BY Customer_No, [Date_Field]) i
  FROM Table_Name
  GROUP BY Customer_No,[Date_Field]
),
 CTE2 AS (
SELECT Customer_No, (datediff(day,Min(d),Max(d))+1) AS CT, MIN(d) AS MIN_DATE,
CASE
 WHEN  (datediff(day,Min(d),Max(d))+1) >0 AND (datediff(day,Min(d),Max(d))+1) <=30 THEN '0 to 30'
 WHEN  (datediff(day,Min(d),Max(d))+1) >30 AND (datediff(day,Min(d),Max(d))+1)<=60 THEN '30 to 60'
 WHEN  (datediff(day,Min(d),Max(d))+1) >60 AND (datediff(day,Min(d),Max(d))+1)<=90 THEN '60 to 90'
 WHEN  (datediff(day,Min(d),Max(d))+1) >90 THEN '90 to 120'
END Bucket
FROM CTE
GROUP BY Customer_No,DATEDIFF(day,i,d)),
CTE3 AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY(Customer_No)ORDER BY MIN_DATE DESC) as ROW_NUM fROM CTE2)
SELECT Customer_No,Bucket  FROM CTE3 WHERE ROW_NUM = 1 ORDER BY Customer_No
 

Thanks , it works 

[Suhaas]

4 minutes ago, Rabbo said:

Thanks , it works 

Sure Bro. Good to know

[srinima]

1) First get the max date and min date for each customer and then calculate the number of days between them

2) get the count of distinct dates and if the count of distinct dates is equal to the difference between the min and max dates then classify if its not then use a case statement to get down the counts.

 

I hope this helps.... If you are looking for SQL please wait till tmrw will post it from work....