September 30, 2004 at 12:38 pm
Can anyone tell me what I am doing wrong in this stored procedure? The basics of what I am trying to do is grab some values from a table and take 2 row cominations from that table and insert them as 1 row into another table. There is more going on after that, but I am having difficulties with that part first.
What it looks like is that either I am not getting new values sent to my "tester" select call or the variable that I am testing against to determine whether or not to do an update or insert is never changing or being reset. I have put print statements all over the place to see if I am getting new values from the cursor, but still can't locate my problem.
If you have suggestions for a better way for what I am trying to do I am open to those as well. Thanks in advance.
Here is the stored proc:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE SP_BUILD_TOTALS AS
--CREATE PROCEDURE SP_BUILD_TOTALS AS
SET NOCOUNT ON
CREATE TABLE ##TEMPTOTALS (
OrganizationID int,
Year int,
Month int,
Quarter int,
MonthlySumRev int,
MonthlySumPTP int,
QuarterTotRev int,
QuarterTotPTP int,
QuarterAvgRev int,
QuarterAvgPTP int
)
DECLARE @OrganizationID int
DECLARE @Year int
DECLARE @Month int
DECLARE @Quarter int
DECLARE @Type varchar(3)
DECLARE @MonthlySum int
DECLARE @MonthlySumRev int
DECLARE @MonthlySumExp int
DECLARE @QuarterTotRev int
DECLARE @QuarterTotExp int
DECLARE @QuarterAvgRev int
DECLARE @QuarterAvgExp int
DECLARE @MRevAmount int
DECLARE @QTRevAmount int
DECLARE @QARevAmount int
DECLARE @SQLSTR varchar(500)
DECLARE @count int
DECLARE IndexCursor CURSOR LOCAL
FOR
select a.organizationid, a.year, a.month, a.quarter, a.type, monthlysum,
(select sum(monthlysum) from allservicenumbers a2
where a2.organizationid=a.organizationid and a2.type=a.type and a2.type='rev'
and a2.year=a.year and a2.quarter=a.quarter
group by a2.organizationid, a2.type, a2.year, a2.quarter) as quartersumrev,
(select sum(monthlysum) from allservicenumbers a2
where a2.organizationid=a.organizationid and a2.type=a.type and a2.type='exp'
and a2.year=a.year and a2.quarter=a.quarter
group by a2.organizationid, a2.type, a2.year, a2.quarter) as quartersumexp,
(select avg(monthlysum) from allservicenumbers a2
where a2.organizationid=a.organizationid and a2.type=a.type and a2.type='rev'
and a2.year=a.year and a2.quarter=a.quarter
group by a2.organizationid, a2.type, a2.year, a2.quarter) as quarteravgrev,
(select avg(monthlysum) from allservicenumbers a2
where a2.organizationid=a.organizationid and a2.type=a.type and a2.type='exp'
and a2.year=a.year and a2.quarter=a.quarter
group by a2.organizationid, a2.type, a2.year, a2.quarter) as quarteravgexp
from allservicenumbers a
order by a.organizationid, a.year, a.month, a.type desc
OPEN IndexCursor
FETCH NEXT FROM IndexCursor INTO @OrganizationID, @Year, @Month, @Quarter, @Type, @MonthlySum,
@QuarterTotRev, @QuarterTotExp, @QuarterAvgRev, @QuarterAvgExp
Print 'First Fetch Values:'
Print 'orgID=' + convert(varchar, @OrganizationID)
Print 'year=' + convert(varchar, @Year)
Print 'month=' + convert(varchar, @Month)
Print 'quarter=' + convert(varchar, @Quarter)
Print 'type=' + convert(varchar, @Type)
Print 'monthlysum=' + convert(varchar, @MonthlySum)
Print 'quartertotrev=' + convert(varchar, @QuarterTotRev)
Print 'quartertotexp=' + convert(varchar, @QuarterTotExp)
Print 'quarteravgrev=' + convert(varchar, @QuarterAvgRev)
Print 'quarteravgexp=' + convert(varchar, @QuarterAvgExp)
WHILE (@@FETCH_STATUS <> -1)
BEGIN
Print 'Fetch Values inside while loop:'
Print 'orgID=' + convert(varchar, @OrganizationID)
Print 'year=' + convert(varchar, @Year)
Print 'month=' + convert(varchar, @Month)
Print 'quarter=' + convert(varchar, @Quarter)
Print 'type=' + convert(varchar, @Type)
Print 'monthlysum=' + convert(varchar, @MonthlySum)
Print 'quartertotrev=' + convert(varchar, @QuarterTotRev)
Print 'quartertotexp=' + convert(varchar, @QuarterTotExp)
Print 'quarteravgrev=' + convert(varchar, @QuarterAvgRev)
Print 'quarteravgexp=' + convert(varchar, @QuarterAvgExp)
select @MRevAmount = MonthlySumRev, @QTRevAmount = QuarterTotRev, @QARevAmount = QuarterAvgRev
from ##TEMPTOTALS where organizationid=@OrganizationID and [Year]=@Year and [Month]=@Month
Print 'values returned from select of temptotals'
Print 'mrevamount=' + convert(varchar, @MRevAmount)
Print 'qtrevamount=' + convert(varchar, @QTRevAmount)
Print 'qarevamount=' + convert(varchar, @QARevAmount)
if @MRevAmount IS NOT NULL
Begin
Print 'about to do an update'
update ##TEMPTOTALS set MonthlySumPTP=(@MRevAmount - @MonthlySum),
QuarterTotPTP=(@QTRevAmount - @QuarterTotExp), QuarterAvgPTP=(@QARevAmount - @QuarterAvgExp)
where rganizationID=@OrganizationID">OrganizationID=@OrganizationID and Year=@Year and Month=@Month
End
else
Begin
Print 'about to do an insert'
INSERT INTO ##TEMPTOTALS
(OrganizationID, Year, Month, Quarter,
MonthlySumRev, QuarterTotRev, QuarterAvgRev)
Values (@OrganizationID, @Year, @Month, @Quarter,
@MonthlySum, @QuarterTotRev, @QuarterAvgRev)
select @count = count(*) from ##TEMPTOTALS
Print @count
End
--Exec(@SQLSTR)
FETCH NEXT FROM IndexCursor INTO @OrganizationID, @Year, @Month, @Quarter, @Type, @MonthlySum,
@QuarterTotRev, @QuarterTotExp, @QuarterAvgRev, @QuarterAvgExp
END
CLOSE IndexCursor
DEALLOCATE IndexCursor
SELECT * FROM ##TEMPTOTALS
Drop Table ##TEMPTOTALS
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
September 30, 2004 at 1:49 pm
Ed,
I can't find your insert statement of your query into the temp table. If the temp table is empty, you won't get any results when selecting on the organisationid.
Marc
September 30, 2004 at 2:08 pm
Hi Marc,
Inside my "while" loop I have an "if/else" conditional. The insert is in the else branch. Inside that while loop I first select from the table to determine if a row is already there with an organizationid-year-month combination. If it is, then I go into the "if" and do an update, if not I go into the "else" and do an insert. My problem is as soon as I do the first insert, I never go back into that branch again. Even though my "print" statements tell me I have different orgID-year-month parameters going through the loop.
Thanks.
October 1, 2004 at 2:16 am
You need to add this line
SET @MRevAmount = null
before
select @MRevAmount = MonthlySumRev, @QTRevAmount = QuarterTotRev, @QARevAmount = QuarterAvgRev
from ##TEMPTOTALS where organizationid=@OrganizationID and [Year]=@Year and [Month]=@Month
because if there is no matching data in ##TEMPTOTALS then the varaiables will not change
Far away is close at hand in the images of elsewhere.
Anon.
October 1, 2004 at 10:13 am
Thanks David. That worked! I didn't realize they weren't getting reset. Or actually, I thought when nothing was found they would be set to null at that time (inside the select). Either way, I am working now. Thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
