Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2013
    Posts
    36

    Unanswered: GETDATE() subtract a year

    I am nearing the end of my current project and this question being one of my last is causing me trouble. I cannot implement the GETDATE() statement to my scenario.

    Basically, I need to list some movie details and the download dates for all movies that were downloaded one year ago throughout the calendar month that matches todays month. So, if I were to run the query in November 2013, I would need results that show all downloads made in November 2012.

    There are only two tables involved, M_DOWNLOADS that holds the download dates & M_MOVIES that holds movie details. I've pasted the CREATE TABLES sql for both tables below to help, but I do not know how the GETDATE clause works, and where it would go in my code.

    What I need this query to do is show the DOWNLOAD_DATE from M_DOWNLOADS and, say,
    TITLE_OF_MOVIE & DURATION_OF_MOVIE for now, from the M_MOVIES table.

    M_DOWNLOADS

    CREATE TABLE "M_DOWNLOADS"
    ( "DOWNLOAD_DATE" DATE,
    "CUSTOMER_ID" VARCHAR2(10),
    "MOVIE_ID" VARCHAR2(10),
    CONSTRAINT "M_DOWNLOADS_CON" PRIMARY KEY ("DOWNLOAD_DATE", "CUSTOMER_ID", "MOVIE_ID") ENABLE
    )
    /



    M_MOVIES

    CREATE TABLE "M_MOVIES"
    ( "MOVIE_ID" VARCHAR2(10),
    "TITLE_OF_MOVIE" VARCHAR2(20),
    "DURATION_OF_MOVIE" NUMBER(3,0),
    "CLASSIFICATION_CODE" VARCHAR2(10) NOT NULL ENABLE,
    "DIRECTOR_OF_MOVIE" VARCHAR2(20),
    CONSTRAINT "M_AVAILABLE_MOVIES_PK" PRIMARY KEY ("MOVIE_ID") ENABLE
    )
    /
    ALTER TABLE "M_MOVIES" ADD CONSTRAINT "M_AVAILABLE_MOVIES_CON" FOREIGN KEY ("CLASSIFICATION_CODE")
    REFERENCES "M_CLASSIFICATION_CODE" ("CLASSIFICATION_CODE") ENABLE
    /

    CREATE OR REPLACE TRIGGER "BI_M_AVAILABLE_MOVIES"
    before insert on "M_MOVIES"
    for each row
    begin
    if :NEW."MOVIE_ID" is null then
    select "M_AVAILABLE_MOVIES_SEQ".nextval into :NEW."MOVIE_ID" from dual;
    end if;
    end;

    /
    ALTER TRIGGER "BI_M_AVAILABLE_MOVIES" ENABLE
    /


    Thanks, and I promise there'll be no more hassle from me anytime soon.
    Still learning..

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Try:
    Code:
    ... AND TRUNC(download_date,'Month') = ADD_MONTHS(TRUNC(SYSDATE,'Month'),-12)
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I have never heard of GETDATE() before now.

    Code:
    SQL> select add_months(sysdate,-12) from dual;
    
    ADD_MONTH
    ---------
    20-NOV-12
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    GETDATE() is a SQL Server or Sybase function that returns the system date.

  5. #5
    Join Date
    Oct 2013
    Posts
    36
    Quote Originally Posted by LKBrwn_DBA View Post
    Try:
    Code:
    ... AND TRUNC(download_date,'Month') = ADD_MONTHS(TRUNC(SYSDATE,'Month'),-12)
    Ok thanks, but I am still confused how my select and from statements should be typed as there are joins involved, and why does this statement start with a "and" if there is no "where" beforehand?

    Quote Originally Posted by anacedent View Post
    I have never heard of GETDATE() before now.

    Code:
    SQL> select add_months(sysdate,-12) from dual;
    
    ADD_MONTH
    ---------
    20-NOV-12
    Quote Originally Posted by MCrowley View Post
    GETDATE() is a SQL Server or Sybase function that returns the system date.
    It seems I've been looking into the wrong info then. I didn't realise it was trunc that needs to be used in Oracle.
    Still learning..

  6. #6
    Join Date
    Oct 2013
    Posts
    36
    Bump..any help guys? I can't seem to figure this one out. :/
    Still learning..

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    in other words, you want us to complete your assignment for you.


    I'll pass on this opportunity to facilitate your cheating.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Dec 2007
    Posts
    253
    Quote Originally Posted by london34 View Post
    Bump..any help guys? I can't seem to figure this one out. :/
    What bit can't you figure out? Joining the tables? Building a where clause? Using the condition that's been handed to you in this thread?
    You received a LOT of help over the past couple of weeks, my concern is that you don't seem to have taken any of it on board. Maybe I'm wrong.
    Why not show that you've at least got something from what we've posted before. Create a select that joins the two tables in question and put the appropriate columns in the select clause

  9. #9
    Join Date
    Oct 2013
    Posts
    36
    So I'll admit I've been lazy, but I HAVE been taking on board what all of you have been helping me with. I've just been having a hard time putting all the theory into code. Nevertheless, I got this one to work as shown below;

    SELECT m_downloads.download_date, m_downloads.movie_id, m_movies.title_of_movie
    FROM m_downloads, m_movies
    WHERE m_downloads.movie_id = m_movies.movie_id
    AND TRUNC(download_date,'Month') = ADD_MONTHS(TRUNC(SYSDATE,'Month'),-12)



    What I wasn't sure about at the start, was how to make my joins, based on what anacedent posted in the Returning Updated Data thread the other day;

    select mc.customer_id,mmt.monthly_fee,mmt.monthly_fee*1.1
    from M_CUSTOMER MC, M_MEMBERSHIP_TYPE MMT
    where mc.MEMBERSHIP_TYPE_CODE = mmt.MEMBERSHIP_TYPE_CODE;


    In the code above, I wasn't sure why there is the "mc" before customer_id. Or the "mmt" before the monthly_fee. I was confused by this and thought I might have to apply that to the joins I am currently trying to create.

    Regardless, I fully understand how to create these simple joins, and where clauses. The trunk however is something I needed help with, as you can see I wasn't aware of the condition until now. I was researching the wrong thing.

    Apologies for all the trouble
    Still learning..

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    in SELECT m_downloads.download_date, m_downloads.movie_id, m_movies.title_of_movie
    you used the whole table name to qualify the columns names.

    in my FROM clause (from M_CUSTOMER MC, M_MEMBERSHIP_TYPE MMT)
    I established table name aliases of MC & MMT & used those aliases to qualify columns in the WHERE clause
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Oct 2013
    Posts
    36
    Right no wonder, see I didn't know you could do that to save time while typing. Ok that makes sense then.
    Still learning..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •