create database center8
go

USE [center8]
GO

/****** Object:  Table [dbo].[Allacounts]    Script Date: 01/11/2011 15:27:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Allacounts](
	[row] [int] IDENTITY(1,1) NOT NULL,
	[account] [nchar](10) NULL
) ON [PRIMARY]

GO



/****** Object:  Table [dbo].[Gardesh]    Script Date: 01/11/2011 15:27:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Gardesh](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[amount] [decimal](18, 2) NULL,
	[tarikh] [nchar](10) NULL,
	[account] [nchar](10) NULL,
 CONSTRAINT [PK_Gardesh] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


INSERT INTO [dbo].[Allacounts]
           ([account])
     VALUES
          ('11'),
          ('12'),
          ('13')
          


INSERT INTO [dbo].[Gardesh]
           ([tarikh]
           ,[amount]
           ,[account])
     VALUES
('2010/01/02' ,50000 , '11'        ),
('2010/05/05' ,-3000 , '11'        ),
('2010/06/06' ,1000  , '11'        ),
('2010/01/03' ,65000 , '12'        ),
('2010/05/01' ,6000  , '12'        ),
('2010/01/01' ,100000, '13'        ),
('2010/09/07' ,-1000 , '13'        )
GO

---------------------------------------------
CREATE TABLE tblmandeh (tarikh nchar(12),account nvarchar(10), gardesh decimal(18,2), mandeh decimal(18,2))

delete from tblmandeh
DECLARE @tarikh nchar(12),
		@account nvarchar(10),
        @gardesh decimal(18,2),
        @mandeh decimal(18,2)




declare @id int set @id = 1

while @id<=(select COUNT(account)  from dbo.Allacounts)
begin

IF OBJECT_ID('tempdb..#mytemptable') IS NOT NULL
    DROP TABLE #mytemptable;

select a.tarikh,a.account,a.amount as gardesh  into #mytemptable from dbo.Allacounts b left  outer join dbo.Gardesh a
on  b.account=a.account
where @id=row
order by tarikh,account

SET @mandeh = 0

DECLARE rt_cursor CURSOR
FOR
SELECT tarikh,account, gardesh
FROM #mytemptable

OPEN rt_cursor

FETCH NEXT FROM rt_cursor INTO @tarikh,@account,@gardesh

WHILE @@FETCH_STATUS = 0
 BEGIN
  SET @mandeh = @mandeh + @gardesh
  INSERT dbo.tblmandeh VALUES (@tarikh,@account,@gardesh,@mandeh)
  FETCH NEXT FROM rt_cursor INTO @tarikh,@account,@gardesh
 END

CLOSE rt_cursor
DEALLOCATE rt_cursor



set @id=@id+1 
end
----------out put----------------------
select * from tblmandeh
tarikh	account	gardesh	mandeh
2010/01/02  	11        	50000.00	50000.00
2010/05/05  	11        	-3000.00	47000.00
2010/06/06  	11        	1000.00	48000.00
2010/01/03  	12        	65000.00	65000.00
2010/05/01  	12        	6000.00	71000.00
2010/01/01  	13        	100000.00	100000.00
2010/09/07  	13        	-1000.00	99000.00

quote:

---

See this article before Jeff Moden's head explodes.

---

quote:

---

See this article before Jeff Moden's head explodes. you can do this without a cursor.
http://www.sqlservercentral.com/articles/T-SQL/68467/

---

quote:

---

Originally posted by Lumbago

quote:

---

See this article before Jeff Moden's head explodes. you can do this without a cursor.
http://www.sqlservercentral.com/articles/T-SQL/68467/

---

VERY interesting publication date for that article :)

- Lumbago

My blog-> www.thefirstsql.com

---