Can Anyone help me? - 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)

Can Anyone help me?

Author

Topic

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-05-29 : 02:54:13

CREATE PROCEDURE [dbo].[r_SumRpt] AS
select Redemption1.CustomerID as custid,Customer.Name as custname,count(Redemption1.GiftTitle) as count
from Redemption1
inner join Customer
on Redemption1.CustomerID=Customer.CustomerID
group by Redemption1.CustomerID

I want to get count of GiftTitle from Redemption1 based on CustomerID and GiftTitle in Redemption1.I also want CustomerName from Customer table.
I want like that:

CustID CustName Gift1 Gift2 .....
1 AAA 1
2 BBB 1 1

How can i do that?
with my procedure,i've got error....
Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-29 : 06:12:07

which field contains gift types? i.e values gift1,gift2,... or where do you get them from?

Johnho008
Starting Member

24 Posts

Posted - 2008-05-29 : 07:26:27

Kwikqisi,

If I understand you correctly ,try this

select r.CustomerID as custid,c.Name as custname,Count(r.GiftTitle)as [Total gift], r1.GiftTitle
from Redemption1 r
inner join Customer c on r.CustomerID=c.CustomerID
inner join Redemption1 r1 on r.CustomerID=r1.CustomerID
GROUP BY r.CustomerID,c.Name,r1.GiftTitle

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-29 : 08:02:45

quote:
Originally posted by Johnho008

Kwikqisi,

If I understand you correctly ,try this

select r.CustomerID as custid,c.Name as custname,Count(r.GiftTitle)as [Total gift], r1.GiftTitlefrom Redemption1 r
inner join Customer c on r.CustomerID=c.CustomerID
inner join Redemption1 r1 on r.CustomerID=r1.CustomerID
GROUP BY r.CustomerID,c.Name,r1.GiftTitle

You are grouping by GiftTitle and again taking count on it i think it will return 1 always. Did you meant this?

Select r.CustomerID as custid,c.Name as custname,Count(*)as [Total gift],  r1.GiftTitlefrom Redemption1 r
inner join Customer c on r.CustomerID=c.CustomerID
inner join Redemption1 r1 on r.CustomerID=r1.CustomerID
GROUP BY r.CustomerID,c.Name,r1.GiftTitle

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-05-29 : 09:10:05

Hi,

I want to count gifts for Customer.
Condition is (count same gifts )
Like that:
if Customer1 have two g1 and one g2,will show >>
Custid(1) Name(AAA) g1(2) g2(1)
I get gift types from Redemption1,CustomerId also from Redemption1
all distict gifts from Redem will be in column headers and all distinct customerId will be shown row by row,then check for each customer has how many gifts(base on gift type) and show the gift count under specific gift type column.
I'm confusing...

quote:
Originally posted by Johnho008

Kwikqisi,

If I understand you correctly ,try this

select r.CustomerID as custid,c.Name as custname,Count(r.GiftTitle)as [Total gift], r1.GiftTitle
from Redemption1 r
inner join Customer c on r.CustomerID=c.CustomerID
inner join Redemption1 r1 on r.CustomerID=r1.CustomerID
GROUP BY r.CustomerID,c.Name,r1.GiftTitle

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-29 : 12:35:18

I guess you're looking for cross tab result. Have a look at this article. it has some links that deals with this scenario.

http://www.mssqltips.com/tip.asp?tip=937

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-05-30 : 03:20:18

As i learn ,i do like this but i've got error
I don't keep in touch with crosstab and never used before.
.Please suggest me if u can.

create procedure crosstab
as
execute crosstab 'select r.CustomerID,c.CustomerName from Redmeption r inner join Customer c on (r.CustomerID=c.CustomerID)
group by r.CustomerID,r.GiftTitle','count(r.GiftTitle)'

Johnho008
Starting Member

24 Posts

Posted - 2008-05-30 : 04:49:08

kwikwisi,
Not sure about the Cross table but I done it this way maybe abit of a overkill,

--Create a temp table to store your required data
DECLARE @TEMP TABLE
(custid int,
custname varchar(50),
totalGift int,
Gifttype Varchar(1000))
--Declare Variables
DECLARE @CustomerID Varchar(2), @get_CustomerID CURSOR

--Insert into your temp table the first part of the data
INSERT INTO @TEMP (custid,custname,totalGift)
Select r.CustomerID as custid,c.name as custname, Count (*)as [Total gift]
from Redemption1 r
inner join Customer c on r.CustomerID=c.CustomerID
GROUP BY r.CustomerID,c.Name

--Using cusors to join gifttype with same CustID
SET @get_CustomerID = CURSOR FOR
SELECT Distinct CustomerID FROM Redemption1

OPEN @get_CustomerID
FETCH NEXT FROM @get_CustomerID INTO @CustomerID

WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE @TEMP
SET Gifttype = CASE WHEN totalGift > 1 THEN r.Gifttitle+ ', ' +r1.Gifttitle ELSE r.Gifttitle END
FROM Redemption1 r
Inner join Redemption1 r1 on r.Customerid=r1.Customerid and r.Gifttitle<>r1.Gifttitle
WHERE Custid = @CustomerID

FETCH NEXT FROM @get_CustomerID INTO @CustomerID
END
CLOSE @get_CustomerID
DEALLOCATE @get_CustomerID

--Display Data
SELECT * FROM @TEMP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources