Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2008
    Posts
    5

    Unanswered: sql for date-7 days?

    Hi,

    I have a sql coding question:
    I have tableX with a HistoryDate column and an AmountX column. I need to subtract the AmountX column for the latest HistoryDate from the AmountX column for the HistoryDate from seven days prior.

    Does anyone know how to code that?

    Thanks.

  2. #2
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    Yes, that should be easy, I am sure many here do. Post the create table and create index DDL, and identify what each row represents.
    Last edited by Derek Asirvadem; 04-09-09 at 02:01.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  3. #3
    Join Date
    Jul 2008
    Posts
    5
    The table definition is:

    create table TableX (HistoryDate datetime, ItemAmount float)
    There is no index (yet), it's just a small table for testing.

    There is a row for each day of the year, and a corresponding item amount. I have to compute the difference of ItemAmount for the row with max(HistoryDate) and the row from 7 days prior, which would be max(HistoryDate)-7 days prior.

    Thank you.

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    select a.ItemAmount-b.ItemAmount from TableX a, TableX b,
    (select max(HistoryDate), dateadd(dd,-7,max(HistoryDate)) from TableX)c(md,md7)
    where a.HistoryDate=c.md
    and b.HistoryDate=c.md7

  5. #5
    Join Date
    Mar 2009
    Posts
    2
    please check below sql

    select ((select Amountx from tablex where HistoryDate = max(HistoryDate))- (select Amountx from tablex where HistoryDate = dateadd(dd, -7, max(HistoryDate)))) as Amountx

    thanks
    www.************.com

  6. #6
    Join Date
    Mar 2009
    Posts
    2
    select ((select Amountx from tablex where HistoryDate = max(HistoryDate))- (select Amountx from tablex where HistoryDate = dateadd(dd, -7, max(HistoryDate)))) as Amountx

    thanks
    ************.com

Posting Permissions

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