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.
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?
What is wrong with doing it this way? Does it not meet some criteria? Sounds like we need a fuller explanation of your issue.
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).
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.