Crosstab in SQL 2000
- From: Justin <Justin@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 12 Jun 2006 10:04:02 -0700
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_Reported)
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_Reported)
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
GO
.
- Prev by Date: Re: Date Query
- Next by Date: Re: distinct with all the columns
- Previous by thread: Re: Date Query
- Next by thread: Re: distinct with all the columns
- Index(es):