Problem with stored proc - 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 2008 Forums

Transact-SQL (2008)

Problem with stored proc

Author

Topic

bh0526
Yak Posting Veteran

71 Posts

Posted - 2011-10-10 : 21:13:07

I have the following stored procedure:

ALTER PROCEDURE [dbo].[WFSGetCustomerAging]
@AgingDate date,
@Company varchar(10),
@CustGroup nvarchar(20)
AS
SET NOCOUNT ON

SELECT CG.Name AS GroupName
,CTR.ACCOUNTNUM

,sum(case when DateDiff("d", CTR.TRANSDATE, @AgingDate) <= 30 then CTR.AMOUNTMST else 0 end)
+ sum(case when DateDiff("d", CTR.TRANSDATE, @AgingDate) > 30 and DateDiff("d", CTR.TRANSDATE, @AgingDate) <= 60 then CTR.AMOUNTMST else 0 end)
+ sum(case when DateDiff("d", CTR.TRANSDATE, @AgingDate) > 60 and DateDiff("d", CTR.TRANSDATE, @AgingDate) <= 90 then CTR.AMOUNTMST else 0 end)
+ sum(case when DateDiff("d", CTR.TRANSDATE, @AgingDate) > 90 and DateDiff("d", CTR.TRANSDATE, @AgingDate) <= 120 then CTR.AMOUNTMST else 0 end)
+ sum(case when DateDiff("d", CTR.TRANSDATE, @AgingDate) > 120 and DateDiff("d", CTR.TRANSDATE, @AgingDate) <= 150 then CTR.AMOUNTMST else 0 end)
+ sum(case when DateDiff("d", CTR.TRANSDATE, @AgingDate) > 150 and DateDiff("d", CTR.TRANSDATE, @AgingDate) <= 180 then CTR.AMOUNTMST else 0 end)
+ sum(case when DateDiff("d", CTR.TRANSDATE, @AgingDate) > 180 then CTR.AMOUNTMST else 0 end) As CustTotal

,ISNULL(min(AMC), 0) as [Previous Month Balance]

FROM CUSTTrans CTR INNER JOIN
CUSTTABLE CT ON CTR.ACCOUNTNUM = CT.ACCOUNTNUM
AND CTR.DATAAREAID = CT.DATAAREAID INNER JOIN
CustGroup CG ON CT.CustGroup = CG.CustGroup
AND CTR.DATAAREAID = CG.DATAAREAID LEFT JOIN
PAYMTERM PT ON CT.PaymTermID = PT.PaymTermID
AND CTR.DATAAREAID = PT.DATAAREAID

Left join
(SELECT CTR2.AccountNum as AccNum,SUM(CTR2.AMOUNTMST) as AMC
FROM CUSTTRANS CTR2
WHERE CTR2.DATAAREAID = @Company AND CTR2.TRANSDATE <= DateAdd(m, -1, @AgingDate)GROUP BY CTR2.AccountNum) z
on z.AccNum = CTR.AccountNum
WHERE z.AccNum = CTR.AccountNum AND CTR.DATAAREAID = @Company AND CTR.transDate <= @AgingDate
GROUP BY CG.Name, CTR.ACCOUNTNUM, CT.NAME, PT.NumOfDays, CT.Currency
ORDER BY CG.Name, CTR.ACCOUNTNUM, CT.NAME, PT.NumOfDays, CT.Currency

I am using 8/31/2011 as the aging date. This works fine except that if no row exists prior to one month before this date (7/31/2011), then I get no rows back. I think the WHERE statement in bold is my problem. When there is no date older than 8/1/2011, then nothing is returned. What I want to do is make the value AMC = 0 if this happens. But I don't know how to make this work.

Bob

rdaunce
Starting Member

7 Posts

Posted - 2011-10-11 : 00:02:33

I'm not sure what part is bold since it doesn't appear that your formatting came through.

Anyway, it looks to me like you are on the right track looking at the where clause. You have a left join to a derived table that you aliased as z on z.AccNum = CTR.AccountNum, however, you also add the condition z.AccNum = CTR.AccountNum to your where clause. Adding this to your where clause effectively converts your left join to an inner join since it will filter out any rows where z.AccNum is NULL. I'm guessing that you don't really want to do that. If you remove the z.AccNum = CTR.AccountNum condition from your where clause I would suspect you will see some better results.

--Bob

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 01:19:28

Also one more thing we need clarification on is whether you've data in your base table CUSTTrans for the period before your AgingDate as specified by condition CTR.transDate <= @AgingDate. if there's no data it wont return any rows. if you attempth is show these period with 0 values for AMC then probably you need a calendar table which you should use as base and left join to your table.
Anyways if you could explain what you're trying to achieve using some sample data and output we will be able to help you more.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources