Hi Team,

I am using MS sql server 2008 R2,

Please find my query,

I have a table and the data is as follows,

actor_id	TRAN_DATE	tot_amt	breached_date
16887	3/30/2015 21:27	5000
16887	3/29/2015 20:35	5500	>10000
80205	3/30/2015 19:39	6139
80205	3/30/2015 21:19	2000
80205	3/31/2015 13:11	39340	>10000
80205	3/30/2015 6:59	15700
122576	3/30/2015 20:28	17375
122576	3/30/2015 15:53	1474.98
122576	3/31/2015 21:02	17240	>10000
122576	3/31/2015 23:02	17600

Here I have to write a query in which I should be able to retrive the actor_id and their breached dates

output shoule be: I should be able to pick the highlighted rows 

actor_id	TRAN_DATE	tot_amt	Cumilative of each ID	breached_date
16887	3/30/2015 21:27	5000	5000
16887	3/29/2015 20:35	5500	10500	>10000
80205	3/30/2015 19:39	6139	6139
80205	3/30/2015 21:19	2000	2000
80205	3/31/2015 13:11	39340	41340	>10000
80205	3/30/2015 6:59	15700	15700
122576	3/30/2015 20:28	1737	1737
122576	3/30/2015 15:53	1474.98	1474.98
122576	3/31/2015 21:02	17240	20451.98	>10000
122576	3/31/2015 23:02	17600	36314.98

Please could you help me in this regards.

Thanks,

Rajendra Lella

Please provide your example data in a table, and DDL, for example:

DECLARE @table TABLE (ID INT, date DATE, Event CHAR(3), CountryCode INT, TxnID INT)
INSERT INTO @table (ID, date, event, CountryCode, TxnID) VALUES 
(1,   '2015-05-22', 'ABC', 123, 111),
(2,   '2015-05-23', 'XYZ', 123, 111),
(3,   '2015-05-20', 'XYZ', 123, 222),
(4,   '2015-05-21', 'ABC', 123, 222)

Assuming the blank space is NULL (we have no DDL, so it's a guess) try:

SELECT *
  FROM tableName
 WHERE breached_date IS NOT NULL

 You will need to have an order to do the SUM 

in the current data - how do you force the order to do the sum..in other words, for actor 80205, the sum is greater than 10000 for data 03/30/2015(6139+2000+15700) and not just for 03/31/2015(6139+2000+15700+39430),..

try to understand the idea 

declare @table table (actor_id	int,TRAN_DATE date,tot_amt money,breached_date varchar(30))
insert into @table
values
(16887,'3/30/2015 21:27',5000,null),	 
(16887,'3/29/2015 20:35',5500,'>10000'),
(80205,'3/30/2015 19:39',6139,null),	 
(80205,'3/30/2015 21:19',2000,null),	 
(80205,'3/31/2015 13:11',39340,'>10000'),
(80205,'3/30/2015 6:59',15700,null),	 
(122576,'3/30/2015 20:28',17375,null),	 
(122576,'3/30/2015 15:53',1474.98,null),	 
(122576,'3/31/2015 21:02',17248,'>10000'),
(122576,'3/31/2015 23:02',17600,null)	 

select * from
(select *,sum(tot_amt) over (partition by actor_id  order by Tran_date,tot_amt) as SumAmt from @table) A
where SumAmt>10000

I missed the need to sum them. Try this:

DECLARE @tableName TABLE (actorID INT, tranDate DATETIME, totAmount DECIMAL(10,2), breachedDate VARCHAR(20))
INSERT INTO @tableName (actorID, tranDate, totAmount, breachedDate) VALUES

(16887,  '3/30/2015 21:27',	5000	, NULL	  ),
(16887,  '3/29/2015 20:35',	5500	, '>10000'),
(80205,  '3/30/2015 19:39',	6139	, NULL	  ),
(80205,  '3/30/2015 21:19',	2000	, NULL	  ),
(80205,  '3/31/2015 13:11',	39340	, '>10000'),
(80205,  '3/30/2015 06:59',	15700	, NULL	  ),
(122576, '3/30/2015 20:28',	17375	, NULL	  ),
(122576, '3/30/2015 15:53',	1474.98	, NULL	  ),
(122576, '3/31/2015 21:02',	17240	, '>10000'),
(122576, '3/31/2015 23:02',	17600	, NULL	  )

SELECT actorID, tranDate, (SELECT SUM(totAmount) FROM @tableName WHERE t.actorID = actorID) AS totAmount, breachedDate
  FROM @tableName t
 WHERE breachedDate IS NOT NULL

Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI-ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You failed. You should follow ISO-8601 rules for displaying temporal data. You failed again. We will need to re-write every timestamp in this rude ASCII picture you posted. We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI-ISO Standard SQL. 

Now we have to type the DDL you did not provide. We have to guess at keys, constraints, datatypes, etc. Do you always treat people like this?? Can I assume that your Breached date flag is not really a column? 

>> Here I have to write a query in which I should be able to retrieve the actor_id and their breached dates << 

CREATE TABLE Something_Transactions
(actor_id CHAR(6) NOT NULL,
 transaction_date DATETIME2(0) NOT NULL,
 PRIMARY KEY (actor_id, transaction_date),
 transaction_amt DECIMAL (8,2) NOT NULL));

WITH X1(actor_id, transaction_date, transaction_amt,
            transaction_amt_runtot)
AS 
(SELECT actor_id, transaction_date, transaction_amt,
     SUM(transaction_amt) 
      OVER (PARTITION BY actor_id
             ORDER BY transaction_date))
WITH X2 
AS
(SELECT actor_id, transaction_date, transaction_amt,
        MIN(transaction_date) OVER (PARTITION BY actor_id)
        AS first_overage_date
   FROM X1
  WHERE transaction_amt_runtot > 10000.00)

SELECT actor_id, transaction_date AS each_date
  FROM X2
 WHERE transaction_date = first_overage_date; 

>> I should be able to pick the highlighted rows <<

Besides bad manners, you still think in pictures and not abstractions. There is no highlight() predicate in  SQL. 

What all of the above appear to have missed is that it looks like the OP wants the total amount at the point it breaches, not total amount which can include sums after the date it breaches. what you can do is a rolling window (unbounded preceding to current) although not sure if that is possible in 2008 otherwise similar to what Patrick did but with a restriction on the subquery

t.actorID = actorID and t.tran_date <= a.tran_date

this is also assuming your "breached_date" is actually a column

Dear Patrick,

There is some sort of confusion I have created

Please find the table information,

CREATE TABLE [TRANSACTION_MASTER](

[ACTOR_ID] [numeric](10, 0) NULL,

[TRAN_DATE] [datetime] NOT NULL,

[ORIG_INPUT_AMT] [numeric](16, 3) NULL)

table data is as follows,

actor_id TRAN_DATE        tot_amt
16887 3/30/2015 21:27 5000
16887 3/29/2015 20:35 5500
80205 3/30/2015 19:39 6139
80205 3/30/2015 21:19 2000
80205 3/31/2015 13:11 39340
80205 3/30/2015 6:59 15700
122576 3/30/2015 20:28 1737
122576 3/30/2015 15:53 1474.98
122576 3/31/2015 21:02 17240
122576 3/31/2015 23:02 17600

Based on above data I should be able to find the cummilative of each actor_id as given below,

actor_id TRAN_DATE        tot_amt     Cumilative based on Actor_ID
16887 3/30/2015 21:27  5000      5000
16887 3/29/2015 20:35  5500     10500
80205 3/30/2015 19:39  6139      6139
80205 3/30/2015 21:19  2000      2000
80205 3/31/2015 13:11 39340     41340
80205 3/30/2015 6:59 15700     15700
122576 3/30/2015 20:28  1737      1737
122576 3/30/2015 15:53  1474.98      1474.98
122576 3/31/2015 21:02 17240     20451.98
122576 3/31/2015 23:02 17600     36314.98

Based on above data I should be able to extract the tran_date where the cummilative breaches >10000

Ex:

actor_id TRAN_DATE     tot_amt Cumilative based on Actor_ID
16887 3/29/2015 20:35 5500 10500
80205 3/31/2015 13:11 39340 41340
122576 3/31/2015 21:02 17240 20451.98

Please could you help me in this regrads,

Hi Rajendra,

Based on my understanding on your requirement, you can reference the below query.

CREATE TABLE [TRANSACTION_MASTER](
[ACTOR_ID] [numeric](10, 0) NULL,
[TRAN_DATE] [datetime] NOT NULL,
[ORIG_INPUT_AMT] [numeric](16, 3) NULL)


INSERT INTO TRANSACTION_MASTER VALUES
(16887,'3/30/2015 21:27:00',5000),
(16887,'3/29/2015 20:35:00',5500),
(80205,'3/30/2015 19:39:00',6139),
(80205,'3/30/2015 21:19:00',2000),
(80205,'3/31/2015 13:11:00',39340),
(80205,'3/30/2015 6:59:00',15700),
(122576,'3/30/2015 20:28:00',1737),
(122576,'3/30/2015 15:53:00',1474.98),
(122576,'3/31/2015 21:02:00',17240),
(122576,'3/31/2015 23:02:00',17600 );
 
 --accumulative result
;WITH Cte AS
(
SELECT * FROM TRANSACTION_MASTER T
CROSS APPLY
(
SELECT SUM(ORIG_INPUT_AMT) cumAMT FROM TRANSACTION_MASTER WHERE ACTOR_ID=T.ACTOR_ID AND TRAN_DATE<=T.TRAN_DATE
) AS CAT
)SELECT * FROM Cte ORDER BY ACTOR_ID,TRAN_DATE

--the row where accumulative result exceeds 1000
;WITH Cte AS
(
SELECT * FROM TRANSACTION_MASTER T
CROSS APPLY
(
SELECT SUM(ORIG_INPUT_AMT) cumAMT FROM TRANSACTION_MASTER WHERE ACTOR_ID=T.ACTOR_ID AND TRAN_DATE<=T.TRAN_DATE
) AS CAT
)
SELECT * FROM Cte C WHERE cumAMT>10000
AND NOT EXISTS(SELECT 1 FROM Cte WHERE cumAMT>10000 AND ACTOR_ID=C.ACTOR_ID AND TRAN_DATE < C.TRAN_DATE)

IIf you have any question, feel free to let me know.