Find last 6 months of data using ADD_MONTHS issue

[alfishabs]

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

[David_Roth]

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

[alfishabs]

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

[dnoeth]

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?

[alfishabs]

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.

[TAP9]

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

[dnoeth]

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

[alfishabs]

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.