Crosstab Help in Sql Server

I need some assistance, i have this Stored Procedure that will take my table
and create a cross tab. Unfor, I am getting duplicate value from the output,
when checking table1, there are no dups, i dont' know what i am doing wrong.
Please help
below, code that I am using. thaks
=================================
DECLARE @.Month_1_V as varchar(20)
DECLARE @.Create_Date_V as Datetime
DECLARE @.Corp_V as Numeric(13)
DECLARE @.Source_V as Varchar(20)
DECLARE @.Category_V as Varchar(50)
DECLARE @.Description_1_V as Varchar(50)
DECLARE @.Cycle_V as Varchar(1)
DECLARE @.Count_1_V as Numeric(13)
DECLARE @.Month_1_V2 as varchar(20)
DECLARE @.Create_Date_V2 as Datetime
DECLARE @.Corp_V2 as Numeric(13)
DECLARE @.Source_V2 as Varchar(20)
DECLARE @.Category_V2 as Varchar(50)
DECLARE @.Description_1_V2 as Varchar(50)
DECLARE @.Cycle_V2 as Varchar(1)
DECLARE @.Count_1_V2 as Numeric(13)
DECLARE @.V1 as Numeric(13)
DECLARE @.V2 as Numeric(13)
DECLARE @.V3 as Numeric(13)
DECLARE @.V4 as Numeric(13)
DECLARE @.V5 as Numeric(13)
DECLARE @.V6 as Numeric(13)
DECLARE @.V7 as Numeric(13)
DECLARE @.V8 as Numeric(13)
DECLARE @.V9 as Numeric(13)
DECLARE @.V10 as Numeric(13)
DECLARE @.V11 as Numeric(13)
truncate table tbl_Agency
DECLARE t_Noble_Agency CURSOR
FOR
SELECT DISTINCT Month_1, Create_date, Corp, Source, Category, SUM(Count_1)
AS Count_1, Description_1, Cycle
FROM phonecol.temp_tblAgency
WHERE (Create_date BETWEEN CONVERT(DATETIME, '2006-05-12 00:00:00', 102)
AND CONVERT(DATETIME, '2006-05-12 00:00:00', 102))
GROUP BY Month_1, Create_date, Corp, Source, Category, Description_1, Cycle
ORDER BY Category, Corp, Source
OPEN t_Noble_Agency
FETCH NEXT FROM t_Noble_Agency INTO @.Month_1_V, @.Create_Date_V, @.Corp_V,
@.Source_V, @.Category_V, @.Count_1_V, @.Description_1_V, @.Cycle_V
IF @.@.FETCH_STATUS = 0
BEGIN
Set @.Month_1_V2 = @.Month_1_V
SET @.Create_Date_V2 = @.Create_Date_V
SET @.Corp_V2 = @.Corp_V
SET @.Source_V2 = @.Source_V
SET @.Category_V2 = @.Category_V
SET @.Cycle_V2 = @.Cycle_V
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
IF (@.Category_V2 <> @.Category_V)or(@.Corp_V2 <> @.Corp_V) or (@.Source_V2 <>
@.Source_V)
BEGIN
INSERT INTO tbl_Agency(Month_1, Type, Loaddate, Agency, Corp, Cycle,
Total_Accounts,Call_Backs, [Left_Msg(Machine)], [Left_Msg(Live)],
Promise_to_Pay, Full_Pay, Partial_Pay, Past_Due_Pay,
Wrong_Number,TriTones,Skip_Trace,Not_Rep
orted)
VALUES (@.Month_1_V2 , @.Category_V2, @.Create_Date_V2, @.Source_V2, @.Corp_V2,
@.Cycle_V2,
isnull(@.v1,0)+isnull(@.v2,0)+isnull(@.v3,0
)+isnull(@.v4,0)+isnull(@.v5,0)+isnull
(@.v6,0)+isnull(@.v7,0)+isnull(@.v8,0)+
isnull(@.v9,0)+isnull(@.v10,0)+isnull(@.v11
,0), isnull(@.v1,0), isnull(@.v2,0),
isnull(@.v3,0), isnull(@.v4,0), isnull(@.v5,0), isnull(@.v6,0), isnull(@.v7,0),
isnull(@.v8,0), isnull(@.v9,0),isnull(@.v10,0),isnull(@.v11
,0))
Set @.Month_1_V2 = @.Month_1_V
SET @.Create_Date_V2 = @.Create_Date_V
SET @.Corp_V2 = @.Corp_V
SET @.Source_V2 = @.Source_V
SET @.Category_V2 = @.Category_V
SET @.Cycle_V2 = @.Cycle_V
END
--@.V1 = 'Call_Backs'
--@.V2 = '[Left_Msg(Machine)]'
--@.V3 = '[Left_Msg(Live)]'
--@.V4 = 'Promise_to_Pay'
--@.V5 = 'Full_Pay'
--@.V6 = 'Partial_Pay'
--@.V7 = 'Past_Due_Pay'
--@.V8 = 'Wrong_Number'
--@.V9= 'TriTones'
--@.V10 = 'Skip_Trace'
--@.V11 = 'Not_Reported'
IF ltrim(rtrim(@.Description_1_V)) = 'Call Back'
Begin
SET @.V1 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Left Message (Answering Machine)'
Begin
SET @.V2 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Left Message (Live Person)'
Begin
SET @.V3 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Promise To Pay'
Begin
SET @.V4 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Received Payment (Full)'
Begin
SET @.V5 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Received Payment (Partial Pmt)'
Begin
SET @.V6 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Received Payment (Past Due)'
Begin
SET @.V7 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Wrong Number'
Begin
SET @.V8 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Tri-Tones'
Begin
SET @.V9 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Skip Trace Customers Removed Prior to
Contact'
Begin
SET @.V10 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Not Reported'
Begin
SET @.V11 = @.Count_1_V
END
END
FETCH NEXT FROM t_Noble_Agency INTO @.Month_1_V, @.Create_Date_V, @.Corp_V,
@.Source_V, @.Category_V, @.Count_1_V, @.Description_1_V, @.Cycle_V
END
INSERT INTO tbl_Agency(Month_1, Type, Loaddate, Agency, Corp, Cycle,
Total_Accounts,Call_Backs, [Left_Msg(Machine)], [Left_Msg(Live)],
Promise_to_Pay, Full_Pay, Partial_Pay, Past_Due_Pay,
Wrong_Number,TriTones,Skip_Trace,Not_Rep
orted)
VALUES (@.Month_1_V2 , @.Category_V2, @.Create_Date_V2, @.Source_V2, @.Corp_V2,
@.Cycle_V2,
isnull(@.v1,0)+isnull(@.v2,0)+isnull(@.v3,0
)+isnull(@.v4,0)+isnull(@.v5,0)+isnull
(@.v6,0)+isnull(@.v7,0)+isnull(@.v8,0)+
isnull(@.v9,0)+isnull(@.v10,0)+isnull(@.v11
,0), isnull(@.v1,0), isnull(@.v2,0),
isnull(@.v3,0), isnull(@.v4,0), isnull(@.v5,0), isnull(@.v6,0), isnull(@.v7,0),
isnull(@.v8,0), isnull(@.v9,0),isnull(@.v10,0),isnull(@.v11
,0))
END
CLOSE t_Noble_Agency
DEALLOCATE t_Noble_Agency
GOHi Justin,
I didn't look close into the SP. But from a high level I find that you
have two inserts for 1 fetch within the while loop (one inside the if
condition and one outside in the end). Can you tell why you are doing it.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Justin" wrote:

> I need some assistance, i have this Stored Procedure that will take my tab
le
> and create a cross tab. Unfor, I am getting duplicate value from the outpu
t,
> when checking table1, there are no dups, i dont' know what i am doing wron
g.
> Please help
> below, code that I am using. thaks
> =================================
> DECLARE @.Month_1_V as varchar(20)
> DECLARE @.Create_Date_V as Datetime
> DECLARE @.Corp_V as Numeric(13)
> DECLARE @.Source_V as Varchar(20)
> DECLARE @.Category_V as Varchar(50)
> DECLARE @.Description_1_V as Varchar(50)
> DECLARE @.Cycle_V as Varchar(1)
> DECLARE @.Count_1_V as Numeric(13)
> DECLARE @.Month_1_V2 as varchar(20)
> DECLARE @.Create_Date_V2 as Datetime
> DECLARE @.Corp_V2 as Numeric(13)
> DECLARE @.Source_V2 as Varchar(20)
> DECLARE @.Category_V2 as Varchar(50)
> DECLARE @.Description_1_V2 as Varchar(50)
> DECLARE @.Cycle_V2 as Varchar(1)
> DECLARE @.Count_1_V2 as Numeric(13)
> DECLARE @.V1 as Numeric(13)
> DECLARE @.V2 as Numeric(13)
> DECLARE @.V3 as Numeric(13)
> DECLARE @.V4 as Numeric(13)
> DECLARE @.V5 as Numeric(13)
> DECLARE @.V6 as Numeric(13)
> DECLARE @.V7 as Numeric(13)
> DECLARE @.V8 as Numeric(13)
> DECLARE @.V9 as Numeric(13)
> DECLARE @.V10 as Numeric(13)
> DECLARE @.V11 as Numeric(13)
> truncate table tbl_Agency
> DECLARE t_Noble_Agency CURSOR
> FOR
> SELECT DISTINCT Month_1, Create_date, Corp, Source, Category, SUM(Count_1)
> AS Count_1, Description_1, Cycle
> FROM phonecol.temp_tblAgency
> WHERE (Create_date BETWEEN CONVERT(DATETIME, '2006-05-12 00:00:00', 10
2)
> AND CONVERT(DATETIME, '2006-05-12 00:00:00', 102))
> GROUP BY Month_1, Create_date, Corp, Source, Category, Description_1, Cycl
e
> ORDER BY Category, Corp, Source
> OPEN t_Noble_Agency
> FETCH NEXT FROM t_Noble_Agency INTO @.Month_1_V, @.Create_Date_V, @.Corp_V,
> @.Source_V, @.Category_V, @.Count_1_V, @.Description_1_V, @.Cycle_V
> IF @.@.FETCH_STATUS = 0
> BEGIN
> Set @.Month_1_V2 = @.Month_1_V
> SET @.Create_Date_V2 = @.Create_Date_V
> SET @.Corp_V2 = @.Corp_V
> SET @.Source_V2 = @.Source_V
> SET @.Category_V2 = @.Category_V
> SET @.Cycle_V2 = @.Cycle_V
> WHILE (@.@.FETCH_STATUS <> -1)
> BEGIN
> IF (@.@.FETCH_STATUS <> -2)
> BEGIN
> IF (@.Category_V2 <> @.Category_V)or(@.Corp_V2 <> @.Corp_V) or (@.Source_V2 <>
> @.Source_V)
> BEGIN
> INSERT INTO tbl_Agency(Month_1, Type, Loaddate, Agency, Corp, Cycle,
> Total_Accounts,Call_Backs, [Left_Msg(Machine)], [Left_Msg(Live)],
> Promise_to_Pay, Full_Pay, Partial_Pay, Past_Due_Pay,
> Wrong_Number,TriTones,Skip_Trace,Not_Rep
orted)
> VALUES (@.Month_1_V2 , @.Category_V2, @.Create_Date_V2, @.Source_V2, @.Corp_V2,
> @.Cycle_V2,
> isnull(@.v1,0)+isnull(@.v2,0)+isnull(@.v3,0
)+isnull(@.v4,0)+isnull(@.v5,0)+isnu
ll(@.v6,0)+isnull(@.v7,0)+isnull(@.v8,0)+
> isnull(@.v9,0)+isnull(@.v10,0)+isnull(@.v11
,0), isnull(@.v1,0), isnull(@.v2,0),
> isnull(@.v3,0), isnull(@.v4,0), isnull(@.v5,0), isnull(@.v6,0), isnull(@.v7,0),
> isnull(@.v8,0), isnull(@.v9,0),isnull(@.v10,0),isnull(@.v11
,0))
> Set @.Month_1_V2 = @.Month_1_V
> SET @.Create_Date_V2 = @.Create_Date_V
> SET @.Corp_V2 = @.Corp_V
> SET @.Source_V2 = @.Source_V
> SET @.Category_V2 = @.Category_V
> SET @.Cycle_V2 = @.Cycle_V
> END
> --@.V1 = 'Call_Backs'
> --@.V2 = '[Left_Msg(Machine)]'
> --@.V3 = '[Left_Msg(Live)]'
> --@.V4 = 'Promise_to_Pay'
> --@.V5 = 'Full_Pay'
> --@.V6 = 'Partial_Pay'
> --@.V7 = 'Past_Due_Pay'
> --@.V8 = 'Wrong_Number'
> --@.V9= 'TriTones'
> --@.V10 = 'Skip_Trace'
> --@.V11 = 'Not_Reported'
> IF ltrim(rtrim(@.Description_1_V)) = 'Call Back'
> Begin
> SET @.V1 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Left Message (Answering Machine)'
> Begin
> SET @.V2 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Left Message (Live Person)'
> Begin
> SET @.V3 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Promise To Pay'
> Begin
> SET @.V4 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Received Payment (Full)'
> Begin
> SET @.V5 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Received Payment (Partial Pmt)'
> Begin
> SET @.V6 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Received Payment (Past Due)'
> Begin
> SET @.V7 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Wrong Number'
> Begin
> SET @.V8 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Tri-Tones'
> Begin
> SET @.V9 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Skip Trace Customers Removed Prior to
> Contact'
> Begin
> SET @.V10 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Not Reported'
> Begin
> SET @.V11 = @.Count_1_V
> END
> END
> FETCH NEXT FROM t_Noble_Agency INTO @.Month_1_V, @.Create_Date_V, @.Corp_V,
> @.Source_V, @.Category_V, @.Count_1_V, @.Description_1_V, @.Cycle_V
> END
> INSERT INTO tbl_Agency(Month_1, Type, Loaddate, Agency, Corp, Cycle,
> Total_Accounts,Call_Backs, [Left_Msg(Machine)], [Left_Msg(Live)],
> Promise_to_Pay, Full_Pay, Partial_Pay, Past_Due_Pay,
> Wrong_Number,TriTones,Skip_Trace,Not_Rep
orted)
> VALUES (@.Month_1_V2 , @.Category_V2, @.Create_Date_V2, @.Source_V2, @.Corp_V2,
> @.Cycle_V2,
> isnull(@.v1,0)+isnull(@.v2,0)+isnull(@.v3,0
)+isnull(@.v4,0)+isnull(@.v5,0)+isnu
ll(@.v6,0)+isnull(@.v7,0)+isnull(@.v8,0)+
> isnull(@.v9,0)+isnull(@.v10,0)+isnull(@.v11
,0), isnull(@.v1,0), isnull(@.v2,0),
> isnull(@.v3,0), isnull(@.v4,0), isnull(@.v5,0), isnull(@.v6,0), isnull(@.v7,0),
> isnull(@.v8,0), isnull(@.v9,0),isnull(@.v10,0),isnull(@.v11
,0))
> END
> CLOSE t_Noble_Agency
> DEALLOCATE t_Noble_Agency
> GO|||sure,
what happens first is that data will be imported to a temporary table
from there, the SP should take the data from that table and change it to a
crosstab
inserting output to finalize table
unfor, in the finalize table, i am getting duplicated values
"Omnibuzz" wrote:
> Hi Justin,
> I didn't look close into the SP. But from a high level I find that you
> have two inserts for 1 fetch within the while loop (one inside the if
> condition and one outside in the end). Can you tell why you are doing it.
>
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "Justin" wrote:
>|||Where is the insert script for the finalise table?
I assume that tbl_Agency is the temp table you are referring to and it can
have duplicates. Can you post the cross tab query?
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Justin" wrote:
> sure,
> what happens first is that data will be imported to a temporary table
> from there, the SP should take the data from that table and change it to a
> crosstab
> inserting output to finalize table
> unfor, in the finalize table, i am getting duplicated values
>
> "Omnibuzz" wrote:
>