Query advice

I need a suggestion.
I have a table with
Columns
(Rec_ID, Amt_Recieved, Amt_given, Rec_Status, Rec_Frequency , Rec_date) here
Rec_ID and Rec_date are primary key
I need a resultset out of this table which has columns Rec_ID,
Amt_Recieved, Amt_given from the day previous to Rec_Date and Rec_Status,
Rec_Frequency , Rec_date corresponds to the Rec_date
For Eg.
(Rec_ID, Amt_Recieved, Amt_given, Rec_Status, Rec_Frequency , Rec_date)
1, 100,50,A,5,01/21/2005
1, 200,60,B,6,01/22/2005
1, 300,70,C,7,01/23/2005
2, 200,45,B,6,01/23/2005
2, 250,50,C,4,01/24/2005
I need out of it
1, 100,50,B,6,01/22/2005
1, 200,60,C,7,01/23/2005
1, 300,70,B,6,01/23/2005
2, 200,45,C,4,01/24/2005
I have tried inline queries for each column but it takes a lot of time.
Any advice shall be appreciated.
Thanks,
siajset dateformat mdy
create table #temp(Rec_ID int, Amt_Recieved int, Amt_given int,
Rec_Status char(1), Rec_Frequency int, Rec_date
smalldatetime)
insert into #temp(Rec_ID, Amt_Recieved, Amt_given, Rec_Status,
Rec_Frequency , Rec_date)
values (1, 100,50,'A',5,'01/21/2005')
insert into #temp(Rec_ID, Amt_Recieved, Amt_given, Rec_Status,
Rec_Frequency , Rec_date)
values (1, 200,60,'B',6,'01/22/2005')
insert into #temp(Rec_ID, Amt_Recieved, Amt_given, Rec_Status,
Rec_Frequency , Rec_date)
values (1, 300,70,'C',7,'01/23/2005')
insert into #temp(Rec_ID, Amt_Recieved, Amt_given, Rec_Status,
Rec_Frequency , Rec_date)
values (2, 200,45,'B',6,'01/23/2005')
insert into #temp(Rec_ID, Amt_Recieved, Amt_given, Rec_Status,
Rec_Frequency , Rec_date)
values (2, 250,50,'C',4,'01/24/2005')
select a.Rec_ID, a.Amt_Recieved, a.Amt_given,
b.Rec_Status, b.Rec_Frequency , b.Rec_date
from #temp a
inner join #temp b on a.Rec_Date=dateadd(day,-1,b.Rec_Date)
drop table #temp|||Thanks...Mark for the Reply..
There is a bit more to this the Previous day. The Previous date may not be
the immediate previous I am lloking for the maximum data previous to
Rec_date. Meaning if there is Record for 01/22 and 01/24 and not for 01/23
then we want data for 01/22
siaj
"markc600@.hotmail.com" wrote:

> set dateformat mdy
> create table #temp(Rec_ID int, Amt_Recieved int, Amt_given int,
> Rec_Status char(1), Rec_Frequency int, Rec_date
> smalldatetime)
> insert into #temp(Rec_ID, Amt_Recieved, Amt_given, Rec_Status,
> Rec_Frequency , Rec_date)
> values (1, 100,50,'A',5,'01/21/2005')
> insert into #temp(Rec_ID, Amt_Recieved, Amt_given, Rec_Status,
> Rec_Frequency , Rec_date)
> values (1, 200,60,'B',6,'01/22/2005')
> insert into #temp(Rec_ID, Amt_Recieved, Amt_given, Rec_Status,
> Rec_Frequency , Rec_date)
> values (1, 300,70,'C',7,'01/23/2005')
> insert into #temp(Rec_ID, Amt_Recieved, Amt_given, Rec_Status,
> Rec_Frequency , Rec_date)
> values (2, 200,45,'B',6,'01/23/2005')
> insert into #temp(Rec_ID, Amt_Recieved, Amt_given, Rec_Status,
> Rec_Frequency , Rec_date)
> values (2, 250,50,'C',4,'01/24/2005')
> select a.Rec_ID, a.Amt_Recieved, a.Amt_given,
> b.Rec_Status, b.Rec_Frequency , b.Rec_date
> from #temp a
> inner join #temp b on a.Rec_Date=dateadd(day,-1,b.Rec_Date)
> drop table #temp
>|||Can you try this:
select T1.Rec_ID,
T1.Amt_Received,
T1.Amt_given,
T2.Rec_Status,
T2.Rec_Frequency ,
T2.Rec_date,
T2.Rec_date
from TryRec T1,
TryRec T2
where (T1.Rec_date < T2.Rec_date
and not exists (select *
from TryRec T3
where (T3.Rec_date > T1.Rec_date and T3.Rec_date <
T2.Rec_date)
or (T3.Rec_date = T2.Rec_date and T3.Rec_ID <
T2.Rec_ID)
)
and T1.Rec_ID = (select max(Rec_ID)
from TryRec T4
where T1.Rec_date = T4.Rec_date))
or (T1.Rec_date = T2.Rec_date
and T1.Rec_ID < T2.Rec_ID
and not exists (select *
from TryRec T5
where (T5.Rec_date = T1.Rec_date
and T5.Rec_ID > T1.Rec_ID
and T5.Rec_ID < T2.Rec_ID)
)
)
order by 1, 2
Perayu
"siaj" <siaj@.discussions.microsoft.com> wrote in message
news:E417D534-1B46-4957-B352-0E3CFA0ACEC3@.microsoft.com...
>I need a suggestion.
> I have a table with
> Columns
> (Rec_ID, Amt_Recieved, Amt_given, Rec_Status, Rec_Frequency , Rec_date)
> here
> Rec_ID and Rec_date are primary key
>
> I need a resultset out of this table which has columns Rec_ID,
> Amt_Recieved, Amt_given from the day previous to Rec_Date and Rec_Status,
> Rec_Frequency , Rec_date corresponds to the Rec_date
>
> For Eg.
> (Rec_ID, Amt_Recieved, Amt_given, Rec_Status, Rec_Frequency , Rec_date)
> 1, 100,50,A,5,01/21/2005
> 1, 200,60,B,6,01/22/2005
> 1, 300,70,C,7,01/23/2005
> 2, 200,45,B,6,01/23/2005
> 2, 250,50,C,4,01/24/2005
> I need out of it
> 1, 100,50,B,6,01/22/2005
> 1, 200,60,C,7,01/23/2005
> 1, 300,70,B,6,01/23/2005
> 2, 200,45,C,4,01/24/2005
>
> I have tried inline queries for each column but it takes a lot of time.
> Any advice shall be appreciated.
>
> Thanks,
> siaj|||thanks Much ...It helps..
siaj
"Perayu" wrote:

> Can you try this:
> select T1.Rec_ID,
> T1.Amt_Received,
> T1.Amt_given,
> T2.Rec_Status,
> T2.Rec_Frequency ,
> T2.Rec_date,
> T2.Rec_date
> from TryRec T1,
> TryRec T2
> where (T1.Rec_date < T2.Rec_date
> and not exists (select *
> from TryRec T3
> where (T3.Rec_date > T1.Rec_date and T3.Rec_date
<
> T2.Rec_date)
> or (T3.Rec_date = T2.Rec_date and T3.Rec_ID <
> T2.Rec_ID)
> )
> and T1.Rec_ID = (select max(Rec_ID)
> from TryRec T4
> where T1.Rec_date = T4.Rec_date))
> or (T1.Rec_date = T2.Rec_date
> and T1.Rec_ID < T2.Rec_ID
> and not exists (select *
> from TryRec T5
> where (T5.Rec_date = T1.Rec_date
> and T5.Rec_ID > T1.Rec_ID
> and T5.Rec_ID < T2.Rec_ID)
> )
> )
> order by 1, 2
> Perayu
>
> "siaj" <siaj@.discussions.microsoft.com> wrote in message
> news:E417D534-1B46-4957-B352-0E3CFA0ACEC3@.microsoft.com...
>
>sql