SQL - need to fetch month ends from Dates dimension

**css_jay99** · 13 July 2007, 08:47

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')

**lilelvis2000** · 13 July 2007, 08:48

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.

**Cowboy Bob** · 13 July 2007, 08:57

Code:

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

**Ivor1** · 13 July 2007, 09:01

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

**css_jay99** · 13 July 2007, 09:16

Originally posted by Ivor1

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** · 13 July 2007, 10:25

Originally posted by css_jay99

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)

**thunderlizard** · 13 July 2007, 10:53

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

**css_jay99** · 16 July 2007, 08:29

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

**css_jay99** · 16 July 2007, 08:51

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

SQL - need to fetch month ends from Dates dimension