One table, one query, two lines

[adale]

Is it possible to show records from a single table more than once in each line of the resulting query.
I need to do this to work out interest on entries up until the next date.

Here's the table. How can I tell there are 5 days between the first and second record so I can work out accrued interest.
(10GBP * 5%) * 5 days (difference between 5/1 and 1/1)
1/1 10GBP 5%
5/1 10GBP 6%
9/1 10GBP 7%

Thanks

 

[jbenson001]

I would convert the colum to a datetime and use datediff to find the number of days.

 

[adale]

I just typed it like that for ease.

The table has
01/01/2005 10GBP 5%
05/01/2005 10GBP 6%
10/01/2005 10GBP 7%

and I want a query that returns shows
01/01/2005 05/01/2005 10GBP 5%
05/01/2005 10/01/2005 10GBP 6%
10/01/2005 NULL 10GBP 7%

 

[jbenson001]

Ok then use
select @var = datediff(dd, <date1>, <date2>)
IF @var = 5
.. do something

 

[adale]

I understand datediff, but the first date is on a different record to the second date, and I may have 100's of these to look at. So I want the query to return the from/to dates in sequence for on-the-fly reporting.

 

[jbenson001]

Do you have a field and condition you can group on? Or you may have to loop though the rows of the table.