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

QN Best Practices

Hi everyone,

I wonder if somebody here could recommend a good article about MS Service Broker. I'm looking for some advice and tips in designing applications using SQL Service Broker, mainly QN. For instance, maintenance routines and common faulty scenarios I might find later when my solution is implemented. I have googled for a while but all I can find are recopied examples of QN.

Thanks

Are you interested in SSB best practices in general or specifically QN practices? The (typical) QN use of SSB is very limited (local only, a very limited message exchange pattern, conversations are created by the system for you etc etc). What are your main concerns?