Find last 6 months of data using ADD_MONTHS issue

Database

Find last 6 months of data using ADD_MONTHS issue

Hi,

 

It's my first post here and coming from an SQL background.

I am trying to use the ADD_MONTHS function to add to the MIN date in my data set and do a CASE statement based on that.

 

Something like:

   CASE

      WHEN StartDate between MIN(StartDate) and ADD_MONTHS(MIN(StartDate),6) THEN 1

 

Is there any other way to get this done?

 

Wonder is it possible?

 

Thanks,

Shabbir

Tags (3)
7 REPLIES
Teradata Employee

Re: Find last 6 months of data using ADD_MONTHS issue

What is wrong with doing it this way? Does it not meet some criteria? Sounds like we need a fuller explanation of your issue.

 

thanks

 

Dave

Re: Find last 6 months of data using ADD_MONTHS issue

Hi David,

I tried to run the query and isn't working. It's because I have a Partition statement too.

WHEN StartDate between MIN(StartDate) and ADD_MONTHS(MIN(StartDate),6) OVER (partition by ID order by StartDate ASC) THEN SUM(Value)

This doesn't work. I have a workaround, but have to do multiple CASE statements (6 in above case).

 

Regards,

Shabbir

Junior Contributor

Re: Find last 6 months of data using ADD_MONTHS issue

Your current condition will be true for every row.

I don't know exactly what you're trying to do, but it looks like some sort of conditional aggregation, can you add more details or you exising query?

 

 

Re: Find last 6 months of data using ADD_MONTHS issue

What I am trying to achieve is the Total value for the First 6 Months and similarly for Last 6 Months and do comparision for each individual Customer using ID. 

As I mentioned above, I did manage to get it using multiple CASE statements for each months and it works as expected, but a lenghty way of getting things done.

 

Fan

Re: Find last 6 months of data using ADD_MONTHS issue

This is not the most friendly code, but it works.  You would be better off making a volatile table and storing min(startdate) in a table.  Be sure to collect statistics to improve performance.

 

Format:HTML Format Version:1.0 StartHTML: 165 EndHTML: 2178 StartFragment: 314 EndFragment: 2146 StartSelection: 314 EndSelection: 314SyntaxEditor Code Snippet

CASE WHEN startdate BETWEEN (SELECT MIN(startdate) FROM table1) AND ADD_MONTHS((SELECT MIN(startdate) FROM table1), 1) THEN 1 END

 

Junior Contributor

Re: Find last 6 months of data using ADD_MONTHS issue

You need at least two CASEs:

 

select cust_id,
   sum(case when dateCol <= dt1 then Value else 0 end), -- first 6 months
   sum(case when dateCol >= dt2 then Value else 0 end) -- last 6 months from tab join  ( -- get the min/max ranges first    select cust_id,
add_months(min(dateCol), 6) as dt1, -- six months after first date
 add_months(max(dateCol),-6) as dt2 -- six months before last date
   from tab    group by 1  ) as dt on tab.cust_id = dt.cust_id and (dateCol <= dt1 or dateCol >= dt2)
group by 1

Depending on the actual data OLAP aggregates might be more efficient:

 

 

select cust_id,
   sum(case when dateCol <= dt1 then Value else 0 end), -- first 6 months
   sum(case when dateCol >= dt2 then Value else 0 end)  -- last 6 months
from 
 ( 
   select cust_id, dateCol, Value,
add_months(min(dateCol) over (partition by cust_id), 6) as dt1, -- six months after first date
add_months(max(dateCol) over (partition by cust_id),-6) as dt2 -- six months before last date from tab qualify dateCol <= dt1 or dateCol >= dt2 ) as dt group by 1

 

 

 

Re: Find last 6 months of data using ADD_MONTHS issue

I tried to use the VOLATILE TABLE suggested by TAP9 and then used some Inner Joins and it worked a charm.

 

I'll give your option a try too, as it's good to know a few ways of doing the same thing and decide which is the most efficient way of getting it done.

 

 

Thanks for your responses.