• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
  • Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

SQL - need to fetch month ends from Dates dimension

Collapse
X
  •  
  • Filter
  • Time
  • Show
Clear All
new posts

    SQL - need to fetch month ends from Dates dimension

    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

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

    Comment


      #3
      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.
      McCoy: "Medical men are trained in logic."
      Spock: "Trained? Judging from you, I would have guessed it was trial and error."

      Comment


        #4
        Code:
        select max(tdd.<datefield>), month(tdd.<datefield>)
        from tab_date_dim tdd
        group by month(tdd.<datefield>)
        order by month(tdd.<datefield>);
        Last edited by Cowboy Bob; 13 July 2007, 09:00.
        Listen to my last album on Spotify

        Comment


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

          Comment


            #6
            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

            Comment


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

              Comment


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

                Comment


                  #9
                  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

                  Comment


                    #10
                    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

                    Comment

                    Working...
                    X