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
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
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
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?
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.
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
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
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.