ALTER PROCEDURE [dbo].[FetchGroupComm]
(@AgentCode int,@FromDate datetime,@ToDate datetime)
AS
BEGIN	
DECLARE @RowNo int,
@MainID int,
@tAmount float,@eComm float,@eTDS float,@eNet float

SET @RowNo = 1

DECLARE @DateTable AS TABLE (RowNo int IDENTITY(1,1),MainID int)
DECLARE @OutTable AS TABLE (RowNo int IDENTITY(1,1),tAmount float,eComm float,eTDS float,eNet float)

INSERT INTO @DateTable
Select MainID from Comms where IssueDate >=@FromDate and IssueDate <=@ToDate and AgentCode = @AgentCode group by MainID;
SELECT * FROM @DateTable
WHILE @RowNo < = ( SELECT MAX(RowNo) FROM @DateTable)
	BEGIN		
		SET @tAmount=0
		SET @eComm =0
		SET @eTDS =0
		SET @eNet =0
		Select @MainID=MainID from @DateTable Where RowNo = @RowNo
		Select @tAmount =COALESCE(round(sum(vAmount),2),0),@eComm = COALESCE(round(sum(Comm),2),0) ,@eTDS = COALESCE(round(sum(LessTDS),2),0) ,@eComm = COALESCE(sum(round(NetComm,2)),0) from Comms where IssueDate >=@FromDate and IssueDate <=@ToDate and MainID = @MainID and AgentCode = @AgentCode;
		if @tAmount > 0
			BEGIN
				INSERT INTO @OutTable (tAmount,eComm,eTDS,eNet) VALUES (@tAmount ,@eComm ,@eTDS,@eNet)
			END
		SET @RowNo = @RowNo +1
	END		
	Select * from @OutTable	
END

Select 	MainID,	
	tAmount = COALESCE(round(sum(vAmount),2),0),
	eComm 	= COALESCE(round(sum(Comm),2),0),
	eTDS 	= COALESCE(round(sum(LessTDS),2),0),
	eNet 	= COALESCE(sum(round(NetComm,2)),0) 
from 	Comms 
where 	IssueDate >=@FromDate 
and 	IssueDate <=@ToDate 
and 	AgentCode = @AgentCode;
group by MainID

quote:

---

Originally posted by khtan

you don't need to use while loop

Select 	MainID,	
	tAmount = COALESCE(round(sum(vAmount),2),0),
	eComm 	= COALESCE(round(sum(Comm),2),0),
	eTDS 	= COALESCE(round(sum(LessTDS),2),0),
	eNet 	= COALESCE(sum(round(NetComm,2)),0) 
from 	Comms 
where 	IssueDate >=@FromDate 
and 	IssueDate <=@ToDate 
and 	AgentCode = @AgentCode;
group by MainID

---

KH
[spoiler]Time is always against us[/spoiler]

---

quote:

---

No, I must have to use loop

---

quote:

---

Kindly suggest me why it's not returning data from temp table

---