# Thread: sql for date-7 days?

1. Registered User
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. Registered User
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 01:01.

3. Registered User
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. Registered User
Join Date
May 2005
Location
South Africa
Posts
1,369
select a.ItemAmount-b.ItemAmount from TableX a, TableX b,
where a.HistoryDate=c.md
and b.HistoryDate=c.md7

5. Registered User
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
www.************.com

6. Registered User
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
•