Find the first day the account passes a threshold

Using daily transactions, I need to find the first day an account deposits
more than 20 dollars (+ or -)
please see the DDL below, a sql statement, and the desired output. Thanks
create table #bankaccounts
(
accountid int not null
, transdate datetime not null
, primary key (accountid, transdate)
, amttran decimal(19,2) not null
)
set nocount on
insert #bankaccounts
values (1, '20060101', 10)
insert #bankaccounts
values (1, '20060102', 5)
insert #bankaccounts
values (1, '20060103', 12)
insert #bankaccounts
values (1, '20060106', 15.50)
insert #bankaccounts
values (1, '20060107', 13)
insert #bankaccounts
values (2, '20060101', 15)
insert #bankaccounts
values (2, '20060102', -6)
insert #bankaccounts
values (2, '20060103', 2)
insert #bankaccounts
values (2, '20060106', 2.75)
insert #bankaccounts
values (2, '20060107', 4)
insert #bankaccounts
values (2, '20060111', 5)
insert #bankaccounts
values (2, '20060116', 9)
insert #bankaccounts
values (3, '20060115', 7)
insert #bankaccounts
values (3, '20060116', 8)
insert #bankaccounts
values (3, '20060122', 9)
insert #bankaccounts
values (3, '20060126', 10)
insert #bankaccounts
values (4, '20060108', 9)
insert #bankaccounts
values (4, '20060112', 10)
insert #bankaccounts
values (5, '20060107', -16)
insert #bankaccounts
values (5, '20060108', 3)
insert #bankaccounts
values (5, '20060109', -12)
insert #bankaccounts
values (5, '20060111', -16)
select accountid
, sum(amttran) [sum]
, min(transdate) [mindate]
, max(transdate) [maxdate]
, ' date over the 20 dollar threshold ' [thresholddate]
from #bankaccounts
group by accountid
having abs(sum(amttran)) > 20
1 55.50 2006-01-01 2006-01-07 2006-01-03
2 31.75 2006-01-01 2006-01-16 2006-01-11
3 34.00 2006-01-15 2006-01-26 2006-01-22
5 -41.00 2006-01-07 2006-01-11 2006-01-09Thom,
this works, but can be improved
select accountid
, [sum]
, [mindate]
, [maxdate]
, (
select min(transdate) from #bankaccounts b
where b.accountid = t.accountid
and ((select sum(amttran) from #bankaccounts b1 where b1.transdate
<= b.transdate
and b.accountid = b1.accountid
)>20
or
(select sum(amttran) from #bankaccounts b1 where b1.transdate <=
b.transdate
and b.accountid = b1.accountid
)<-20)
) as thresholddate
from(
select accountid
, sum(amttran) [sum]
, min(transdate) [mindate]
, max(transdate) [maxdate]
from #bankaccounts
group by accountid ) t

Find out transactions not replicated

Hi,
I have set up a transactional replication with Publisher,
Distributor and Subscriber on diff servers.
I want to find out (if any) transactions that have not
been replicated from publisher to distributor , and from
distributor to subscriber.
Can you tell me which tables / sprocs I can use to find
this info.
Thank you
Shrikant
use the view MS_distributionstatus to figure out how many commands have to
be replicated; have a look at the UndelivCmdsInDistDB column. You can find
this view in the distribution database.
To get an idea of the commands remaining in the distribution database do
this.
1) connect to your subscriber and query this table.
declare @.varbinary varbinary(300)
select @.varbinary=transaction_timestamp From MSreplication_subscriptions
print @.varbinary
2) with the value for varbinary paste it into the below query - this are the
command waiting to be applied
use distribution
--select From msrepl_transactions where xact_seqno =@.varbinary
exec sp_browsereplcmds @.xact_seqno_start ='0x0001BD2A000055990010'
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"SP" <anonymous@.discussions.microsoft.com> wrote in message
news:0b7701c4e109$48cc99a0$a501280a@.phx.gbl...
> Hi,
> I have set up a transactional replication with Publisher,
> Distributor and Subscriber on diff servers.
> I want to find out (if any) transactions that have not
> been replicated from publisher to distributor , and from
> distributor to subscriber.
> Can you tell me which tables / sprocs I can use to find
> this info.
> Thank you
> Shrikant

Find open transactions

Is there an easy way (SQL Query or enterprise manager) to see if there is an open transaction pending?

yes, you can look into Management Activity monitor.
I do not know why you need this but remember that transaction is active as long as connection is active so if you look for not finalized transaction which were done in dropped connection it probably will not work.
I am not 100% sure about this but I found it somewhere.

Thanks

|||

I missed it when I looked before. FYI, I needed the information because I was debugging a long script that for some reason wasn't executing the statements after a certain line. I thought it might have been due to a transaction not being closed. But it was bad data preventing a unique index from being created.

Thanks for the answer.