Teradata SQl experts , help please

[Rabbo]

I have data in table like below, i have to put the customers in buckets  0-30 days, 31-60 days , 61-90 days ,91-120 days,

• 121-150 days.
• 151-180 days
• 181+ days

date_field data is present  every day , if the date_field data is missing for one day or more than one day in last 30 days i have to ignore that customers and he will not fall into 0-30 days bucket and if the date_field data is missing for one day or more than one day in last 60 days i have to ignore that customers and he will not fall into  31-60 days bucket , the customer might fall into 0-30 days 

 

priority starts from 181 + days  and so on

For example if a customer data in date_field is present for the last 182  days  (everyday), he will fall into  181+ days bucket 

if the customer data in date_field is present for the the last 179 days  (everyday), 151-180 days bucket

customer_no     date_field            amount 

1234                     07/01/2017      0.00

1234                     07/02/2017         0.00

1234                      07/03/2017         0.00

2456                     06/01/2017            0.00

2456                       06/02/2017          0.00

2456                        06/03/2017         0.00

 

i hope question clear ga adiganu

[argadorn]

[kiraak_poradu]

undi ayithe nee question endi kaka

[mindless]

good kochen

[Rushwood]

4 minutes ago, Rabbo said:

I have data in table like below

customer_no     date_field

1234

[Rabbo]

ippudu cheppandi i have edited

[Rushwood]

9 minutes ago, Rabbo said:

ippudu cheppandi i have edited

Only @Tadika or @NeneBhai_NeneMod23 can help you bro 

[mtkr]

Count* group by custm_no chesi number of days decide cheyyochu gaa!!?

[argadorn]

/* 30 days*/
select column1,column2 from table 
where column2 not  in 
(select column2 from table 
 where column1 not between ADD_MONTHS(current_date - EXTRACT(DAY FROM current_date)+1, -1) and current_date 
 )
 and column1  between ADD_MONTHS(current_date - EXTRACT(DAY FROM current_date)+1, -1) and current_date 
 union 
 /* 60 days*/

[Suhaas]

I am not sure about TERADATA Bro But SQL SERVER lo ayithey we can do it this way. 

WITH CTE AS(
SELECT CUSTOMER_NO, COUNT(CUSTOMER_NO)AS DATE_COUNT FROM TABLE_NAME WHERE DATE_FIELD IS NOT NULL AND DATE_FIELD <> '' GROUP BY CUSTOMER_NO )
SELECT CUSTOMER_NO, 
CASE
 WHEN  DATE_COUNT >=0 AND DATE_COUNT <=30 THEN '0 to 30'
 WHEN  DATE_COUNT >=31 AND DATE_COUNT <=60 THEN '30 to 60'
 WHEN  DATE_COUNT >=61 AND DATE_COUNT <=90 THEN '60 to 90'
 WHEN  DATE_COUNT >=91 AND DATE_COUNT <=120 THEN '90 to 120'
END BUCKET
FROM CTE

[argadorn]

2 minutes ago, Suhaas said:

I am not sure about TERADATA But SQL SERVER lo ayithey we can do it this way. 

WITH CTE AS(
SELECT CUSTOMER_NO, COUNT(CUSTOMER_NO)AS DATE_COUNT FROM TABLE_NAME WHERE DATE_FIELD is not null and DATE_FIELD <> '' group by CUSTOMER_NO )
SELECT CUSTOMER_NO, 
CASE
 WHEN  DATE_COUNT >=0 AND DATE_COUNT <=30 THEN '0 to 30'
 WHEN  DATE_COUNT >=31 AND DATE_COUNT <=60 THEN '30 to 60'
 WHEN  DATE_COUNT >=61 AND DATE_COUNT <=90 THEN '60 to 90'
 WHEN  DATE_COUNT >=91 AND DATE_COUNT <=120 THEN '90 to 120'
END BUCKET
FROM CTE

Thsi is the correct way but adhi ah candidate ki ardhamu kadhu ani na doubt ... 

[former]

41 minutes ago, Suhaas said:

I am not sure about TERADATA Bro But SQL SERVER lo ayithey we can do it this way. 

WITH CTE AS(
SELECT CUSTOMER_NO, COUNT(CUSTOMER_NO)AS DATE_COUNT FROM TABLE_NAME WHERE DATE_FIELD IS NOT NULL AND DATE_FIELD <> '' GROUP BY CUSTOMER_NO )
SELECT CUSTOMER_NO, 
CASE
 WHEN  DATE_COUNT >=0 AND DATE_COUNT <=30 THEN '0 to 30'
 WHEN  DATE_COUNT >=31 AND DATE_COUNT <=60 THEN '30 to 60'
 WHEN  DATE_COUNT >=61 AND DATE_COUNT <=90 THEN '60 to 90'
 WHEN  DATE_COUNT >=91 AND DATE_COUNT <=120 THEN '90 to 120'
END BUCKET
FROM CTE

1

Your solution is in complete if same customer have mulitple records for the same date ?

Use Count(DISTINCT DATE_FIELD) and then create a Bucket is a better solution I guess.

 

[Suhaas]

12 minutes ago, former said:

Your solution is in complete if same customer have mulitple records for the same date ?

Use Count(DISTINCT DATE_FIELD) and then create a Bucket is a better solution I guess.

 

Sure Bro. Based on the TS's requirement, he can include DISTINCT DATE_FIELD like you said in my query. 

[Rabbo]

53 minutes ago, Suhaas said:

I am not sure about TERADATA Bro But SQL SERVER lo ayithey we can do it this way. 

WITH CTE AS(
SELECT CUSTOMER_NO, COUNT(CUSTOMER_NO)AS DATE_COUNT FROM TABLE_NAME WHERE DATE_FIELD IS NOT NULL AND DATE_FIELD <> '' GROUP BY CUSTOMER_NO )
SELECT CUSTOMER_NO, 
CASE
 WHEN  DATE_COUNT >=0 AND DATE_COUNT <=30 THEN '0 to 30'
 WHEN  DATE_COUNT >=31 AND DATE_COUNT <=60 THEN '30 to 60'
 WHEN  DATE_COUNT >=61 AND DATE_COUNT <=90 THEN '60 to 90'
 WHEN  DATE_COUNT >=91 AND DATE_COUNT <=120 THEN '90 to 120'
END BUCKET
FROM CTE

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

 

[Rabbo]

37 minutes ago, Suhaas said:

Sure Bro. Based on the TS's requirement, he can include DISTINCT DATE_FIELD like you said in my query. 

i dont have any duplicates in data for date field for one customer, so thats not a problem