SELECT
distinct
transactionID = identity(int, 1,1),
  C.abrnchcod,C.Cfcifno, C.tarikh,C.account,b.row as AccountID,
  C.Tbdptype , s.gardesh 
  into TransactionDetail  
FROM
 dbo.Gardesh  C
left outer   JOIN 
(select  C.account, C.tarikh,SUM (amount )as gardesh 
from
 dbo.Gardesh C
 
GROUP BY
  C.tarikh,
 C.account
 )S
  ON C.account = S.account 
  and C.tarikh = S.tarikh
left outer join 
  
  dbo.Allacounts  b
  on C.account =b.account 
 
 order by C.account,C.tarikh 

id	amount	tarikh	account	abrnchcod	cfcifno	Tbdptype
1	50000.00	2010/01/05	1-11-1                                            	1         	11        	1         
2	-3000.00	2010/02/05	1-11-1                                            	1         	11        	1         
3	1000.00	2010/09/09	1-11-1                                            	1         	11        	1         
4	65000.00	2010/05/01	1-11-2                                            	1         	11        	2         
5	6000.00	2010/09/08	1-12-2                                            	1         	11        	2         
6	100000.00	2010/04/12	2-12-1                                            	2         	12        	1         
7	-1000.00	2010/09/21	2-12-1                                            	2         	12        	1         
64	1000.00	2010/01/05	1-11-1                                            	1         	11        	1         
65	2000.00	2010/12/06	1-11-1                                            	1         	11        	1         
66	1000.00	2010/12/23	1-11-1                                            	1         	11        	1             

row	account
101	1-11-1    
102	1-11-2    
103	1-12-2    
104	2-12-1    

alter table TransactionDetail add balance decimal(20,0)
alter table TransactionDetail add preBalance decimal(20,0)

 CREATE CLUSTERED INDEX IXC_Transaction_AccountID_Tarikh_TransactionID
     ON dbo.TransactionDetail (AccountID, tarikh, TransactionID)

  SET NOCOUNT ON

DECLARE @Amount              decimal(20,0)
DECLARE @CurAccountID        bigINT

DECLARE @PrevAccountID       bigINT
DECLARE @AccountRunningTotal decimal(20,0)
DECLARE @PreAccountRunningTotal   decimal(20,0)

DECLARE curRunningTotal CURSOR LOCAL FORWARD_ONLY
    FOR
 SELECT AccountID, gardesh
   FROM dbo.TransactionDetail
  ORDER BY AccountID, tarikh, TransactionID
   OPEN curRunningTotal

  FETCH NEXT FROM curRunningTotal
   INTO @CurAccountID, @Amount


  WHILE @@FETCH_STATUS = 0
  BEGIN
 
          
 SELECT    
  @AccountRunningTotal = CASE 
                               WHEN @CurAccountID = @PrevAccountID 
                               THEN @AccountRunningTotal + @Amount 
                               ELSE @Amount 
                               END,
        
 @PreAccountRunningTotal = CASE 
                               WHEN @CurAccountID = @PrevAccountID 
                               THEN @AccountRunningTotal - @Amount 
                           
                               ELSE 0 
                               END,
 @PrevAccountID = @CurAccountID
 UPDATE dbo.TransactionDetail
    SET balance = @AccountRunningTotal,
     Prebalance = @PreAccountRunningTotal
  WHERE CURRENT OF curRunningTotal

  FETCH NEXT FROM curRunningTotal
   INTO @CurAccountID, @Amount

    END

quote:

---

Originally posted by robvolk

Why are you storing running totals in a table?

---

quote:

---

Originally posted by robvolk

I hate to say this but you didn't answer any of my questions. Please read them again and respond with specifics.

As far as what is better to do, I can't say without a lot more information. But I can say what NOT to do:

- Don't summarize 70 million rows every time you need to generate a report.
- If the data is historical and rarely changes, don't recreate the same summaries on it. That means don't create a new temp table each time. Summarize it once and store it permanently.
- If this is intended as an ad-hoc reporting tool, don't use a summary table at all. Your users are not going to need all this data, so if they already have a specific customer, date range, or other criteria that limits the data they need, query the original table(s) with those criteria and summarize as needed.
- Don't do running totals in SQL Server, use a reporting tool to do it. The same applies to the other totals/sums.

---

DECLARE @PrevAccountID       bigINT = 0
DECLARE @AccountRunningTotal decimal(20,0) = 0

UPDATE dbo.TransactionDetail SET 
@AccountRunningTotal = balance = CASE 
WHEN AccountID = @PrevAccountID THEN @AccountRunningTotal + gardesh 
ELSE gardesh END, 
@PrevAccountID=AccountID

alter table TransactionDetail add preBalance AS Balance - gardesh

select distinct s.account ,row as accountID,s.tarikh ,s.gardesh
from
    (SELECT distinct b.account,b.amount, b.tarikh , sum(b.amount)OVER (PARTITION BY b.account,b.tarikh ) as gardesh FROM gardesh b)s
    inner join 
    allacounts a on a.account=s.account
    order by s.account ,s.tarikh

quote:

---

Originally posted by robvolk

What constitutes a duplicate? The totals match what you originally posted.

If OVER doesn't give you the results you want then use the original query.

---