get sum? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

get sum?

Author

Topic

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-06-05 : 21:39:52

According to my procedure,
how can i get sum of gifts based on type(g1,g2,...)?

CREATE PROCEDURE dbo.trymatrix AS
DECLARE @SQLCommand varchar(4096), @column sysname

SET @SQLCommand = 'select c.CustomerID as Cust_ID,Name as Co_Name, ' + char(13)

DECLARE curGift CURSOR STATIC LOCAL FOR

select distinct GiftTitle from Redemption1

OPEN curGift

WHILE 1 = 1

BEGIN

FETCH curGift INTO @column

IF @@FETCH_STATUS <> 0

BREAK

SET @SQLCommand = @SQLCommand +

'sum(case GiftTitle when ''' +@column +''' then 1 else 0 end) as '+ @column + ','+char(13)

END

DEALLOCATE curGift

SET @SQLCommand = left (@SQLcommand, len(@SQLcommand)-2) + char(13)

SET @SQLCommand = @SQLCommand +

'from Customer c

inner join Redemption1 d

on c.CustomerID=d.CustomerID

group by Name,c.CustomerID'

PRINT @SQLCommand

EXEC (@SQLCommand)

GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-06 : 01:03:56

Can you provide some sample data? we dont know which table type field is in and how will be data.

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-06-09 : 01:43:33

Redemption1(CustomerID,GiftTitle,..)
Customer(CustomerID,Name,..)

I cannot predict Gifttitle in Redem1,it will be added by user dynamically.
Now i get like:
custid name g1 g2 g3
1 AA 2 0 1
2 BB 1 2 0

I want gift total at the footer of gifts.

quote:
Originally posted by visakh16

Can you provide some sample data? we dont know which table type field is in and how will be data.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-09 : 02:51:58

quote:
Originally posted by kwikwisi

Redemption1(CustomerID,GiftTitle,..)
Customer(CustomerID,Name,..)

I cannot predict Gifttitle in Redem1,it will be added by user dynamically.
Now i get like:
custid name g1 g2 g3
1 AA 2 0 1
2 BB 1 2 0

I want gift total at the footer of gifts.

quote:
Originally posted by visakh16

Can you provide some sample data? we dont know which table type field is in and how will be data.

And what does curGift field contain?

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-06-09 : 03:06:31

curgift is to get gifttitle from redemption because i can't predict giftitle ,that is dynamically added by user.
Actually i get this procedure from other forum and i changed a little to get my requirements.

Thanks.

quote:
Originally posted by visakh16

quote:
Originally posted by kwikwisi

Redemption1(CustomerID,GiftTitle,..)
Customer(CustomerID,Name,..)

I cannot predict Gifttitle in Redem1,it will be added by user dynamically.
Now i get like:
custid name g1 g2 g3
1 AA 2 0 1
2 BB 1 2 0

I want gift total at the footer of gifts.

quote:
Originally posted by visakh16

Can you provide some sample data? we dont know which table type field is in and how will be data.

And what does curGift field contain?

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2008-06-12 : 23:28:08

Dear kwikwisi,

Provide the structure of the tables invloved, some data, and the expected result.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources