Query advice

2 Sep 2005 1:44 PM

siaj

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

2 Sep 2005 2:42 PM

markc600

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

2 Sep 2005 3:22 PM

siaj

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

Show quote

"markc***@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
>
>

2 Sep 2005 4:03 PM

Perayu

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

Show quote

"siaj" <s***@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

2 Sep 2005 9:09 PM

siaj

thanks Much ...It helps..
siaj

Show quote

"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" <s***@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
>
>
>