SQL - need to fetch month ends from Dates dimension

[css_jay99]

Brain seems to have woken up again, I think this should do the trick. though not fully efficient if the start date has to be longer. Any ideas as to rewrite such that Multiple rows dont have to be eliminated with a disticnt

SELECT DISTINCT (LAST_DAY(TRUNC(SYSDATE) - LEVEL) ) month_end
FROM dual
CONNECT BY LEVEL <= sysdate - TO_DATE('01-JAN-2002', 'DD-MON-YYYY')
ORDER BY 1 ASC

[css_jay99]

Drawing a blank on monday mornig or its just the way my brain is is !

Another question comes to mind again

How do i do this from dual

i am sure a LAG/RoWNUM has to be used. The oarcle 9i schema I am runing the original query has not been granted access to the date dimension table, so i guess i have to write the query using dual.


So the original question is how do I Select Last day of the month from year 2002 - Current date from dual.



css_jay99

[thunderlizard]

If you're using Oracle, there's a nice LAST_DAY function.

[Ivor1]

true month ends.

cheers guys. my query did the job.

i forgot to say i am working in oracle hence the other queries wont work

Ivor1, comming back to your question about business month end, how do you do that ?. best to know answer to that now as well

css_jay99

Ideally on your CALENDER_DIM table you should have a business month end flag on, in most cases the business month end will be a week day not a weekend. If you havent got a business month end flag on your tables you will need to return the max date by month thats not a Sat or Sunday. So something like this,

SELECT TO_DATE(TO_CHAR (DAY_date, 'MON-YYYY'), 'MON-YYYY')
, max(day_date)
FROM CALENDAR_TIME_DIM
WHERE TO_CHAR (DAY_date,'D') not in (6,7)
group by TO_DATE(TO_CHAR (day_date, 'MON-YYYY'), 'MON-YYYY')

Ive added a WHERE clause and that will strip out Saturday and Sundays, and return max month end date thats a weekeday. I cant remeber though if Oracle sees Sunday as day 1 though and Saturday as day 7, if is does change the above to not in (5,6)

[css_jay99]

But do you want actual business month end ie last working business day or actual true month end ?

true month ends.

cheers guys. my query did the job.

i forgot to say i am working in oracle hence the other queries wont work

Ivor1, comming back to your question about business month end, how do you do that ?. best to know answer to that now as well

css_jay99

[Ivor1]

But do you want actual business month end ie last working business day or actual true month end ?

[Cowboy Bob]

select max(tdd.<datefield>), month(tdd.<datefield>)
from tab_date_dim tdd
group by month(tdd.<datefield>)
order by month(tdd.<datefield>);

[lilelvis2000]

The general form is

select dateadd(month,datediff(month,'19000101',getdate()) ,'19000101') - 1


If you use a distinct that should sort you.

see

http://www.dbforums.com/showthread.php?t=478171 for a starting point.

[css_jay99]

i think this solves my problem. must be the black monday drinks yesterday night still messing with my head

SELECT TO_DATE(TO_CHAR (tab_date, 'MON-YYYY'), 'MON-YYYY')
, max(tab_date)
FROM TAB_DATE_DIM
group by TO_DATE(TO_CHAR (tab_date, 'MON-YYYY'), 'MON-YYYY')

[css_jay99]

Hi
Quick query. I would like to select month ends from a table of date

There is a date dimension table (e.g. TAB_DATE_DIM) that contains a date column row for all dates/days in the year :-

01/01/2001
02/01/2001
03/01/2001
....
31/01/2001
....
28/02/2001
....
01/01/2007
02/01/2007
03/01/2007



what i would like to do is use a query to select month ends from this table, so results will be :-

31/01/2001
28/02/2001
31/01/2001
30/01/2001
....
....
....
31/01/2007
28/02/2007
31/01/2007
30/01/2007

Any Ideas, sure the query has gotta use a max & group by somewhere

cheers