Help with Case statement - 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 2005 Forums

Transact-SQL (2005)

Help with Case statement

Author

Topic

bmahony993
Yak Posting Veteran

58 Posts

Posted - 2008-06-06 : 11:48:01

I'm not sure if I am doing this the right way. In my table I have project ids. Each project id has several activities associated with it. Each project has a starting balance. Each activity posts an expense to the total balance of the project. If the project has enough money to handle the charges made by the activities, all the activity expenses can be "posted". If there isn't enough money, I want to loop through the activities, check to see if there is enough of a balance to "post" the first one, If there is, then I want to re-adjust the balance and check the second activity. I want to scroll through each project/activity to see what can be "posted". Here is what I have so far, but I cannot work out how to change the total balance amount. Hopefully what I am trying to do makes sense!

declare @testId nchar(6)
declare @RowNum int
declare @newBalance int
select top 1 @testId=projID from #ProjIds
set @RowNum = 0

WHILE @RowNum <= (Select Max(temp_id) from #ProjIds)
BEGIN
set @RowNum = @RowNum + 1
set @newBalance = (select top 1 Bal_2300
from #RevRecData
where @testId=projId
order by projID, activity)

select projId, activity, postCr, Bal_2300,
'New_Status' = Case when (postCr <= Bal_2300) then 'Can Clear' else 'Still Check' END,
'New_Balance' = Case when (postCr <= @newBalance) then (@newBalance - postCr) else @newBalance End
from #RevRecData
where @testId=projId
order by projID, activity

select top 1 @testId=projId from #ProjIds
where projId > @testID
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-06 : 11:52:09

Can you supplement your requireement with some sample data & reqd o/p out of them?

bmahony993
Yak Posting Veteran

58 Posts

Posted - 2008-06-06 : 12:05:37

Hopefully this will be clear. Below is what I would like to get as a final result. the New Status and New Balance is what I am trying to create. With my current code, the New Balance is always the (Bal_2300 - postCr) for each record, which is not what I want.

projId activity postCR Bal_2300 New Status New Balance
77700 101 666.07 1,934,037.72 Can Clear 1,933,370.93
77700 112 1,043.74 1,934,037.72 Can Clear 1,932,327.19
77700 115 17.23 1,934,037.72 Can Clear 1,932,309.96
77700 201 - 1,934,037.72 Can Clear 1,932,309.96

77900 0 3.40 15,387.40 Can Clear 15,384.00
77900 200 1.78 15,387.40 Can Clear 15,382.22
77900 202 11.74 15,387.40 Can Clear 15,370.48
77900 203 18,876.37 15,387.40 Still Check 15,370.48

ron2112
Starting Member

44 Posts

Posted - 2008-06-06 : 12:07:12

I don't have an answer for your question (at least not yet), but I noticed you're incrementing your counter at the beginning of your loop. You might want to consider initializing the counter to 1 and incrementing at the end of the loop. The way you have it now, the last time the loop is executed, @RowNum will be one greater than MAX(temp_id). Either that or change your WHILE condition to < instead of <=.

I also don't see where you're using your counter within the loop, so it appears you're just doing the same action over and over on the same data -- is that what you intended? It seems like I should be seeing something along the lines of "FROM #ProjIds WHERE temp_id = @RowNum" somewhere, but maybe I'm not grasping the intent.

Ron Moses
ConEst Software Systems

bmahony993
Yak Posting Veteran

58 Posts

Posted - 2008-06-06 : 12:15:39

Thanks Ron,
The temp_id is an auto number, so I thought that the following would take care of the counter:
WHILE @RowNum <= (Select Max(temp_id) from #ProjIds)
BEGIN
set @RowNum = @RowNum + 1
...
END.

Currently, I get the basic information I was expecting, but I wonder if that is just pure luck!? I will try your suggestions just to see the difference.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-06 : 12:22:49

Here's one way of doing it

UPDATE t
SET t.New_Balance=t.Bal_2300 -b.PostCRSum,
    t.New_Status= CASE WHEN t.Bal_2300 -b.PostCRSum >=0 THEN 'Can Clear'
                       ELSE 'Still Check'
                  END
FROM Table t
CROSS APPLY (SELECT Sum(postCR) AS PostCRSum
             FROM Table 
             WHERE projId =t.projId 
             AND activity<=t.activity)b

ron2112
Starting Member

44 Posts

Posted - 2008-06-06 : 12:28:10

quote:
The temp_id is an auto number, so I thought that the following would take care of the counter

That does put you through the loop once for each temp_id, but it doesn't affect what specific data you're operating on. So if you have 100 temp_id values, the loop will run 100 times, but will perform the function on the same data each time.

Consider these two examples:

WHILE @i <= (SELECT MAX(RowNum) FROM tblTable)
BEGIN
    UPDATE tblTable
    SET FieldValue = @i
    
    SET @i = @i + 1
END

--===========================

WHILE @i <= (SELECT MAX(RowNum) FROM tblTable)
BEGIN
    UPDATE tblTable
    SET FieldValue = @i
    WHERE RowNum = @i

    SET @i = @i + 1
END

The first one will set all FieldValue records = MAX(RowNum) because they're all being updated each time, which is clearly pointless. The second will set each FieldValue to its respective RowNum value.

It seems to me you're missing something along those lines. Unless, again, I'm missing the point, which is entirely possible. But it does look to me like "select top 1 @testId=projId from #ProjIds where projId > @testID" is going to give you the same result each time through the loop.

Ron Moses
ConEst Software Systems

bmahony993
Yak Posting Veteran

58 Posts

Posted - 2008-06-06 : 12:48:49

Hmm, I copied the code, but get Incorrect syntax near 'APPLY'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-06 : 12:53:00

quote:
Originally posted by bmahony993

Hmm, I copied the code, but get Incorrect syntax near 'APPLY'.

Are you using SQL 2005?

bmahony993
Yak Posting Veteran

58 Posts

Posted - 2008-06-06 : 12:53:50

Yes, 2005 Standard

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-06 : 12:57:38

quote:
Originally posted by bmahony993

Yes, 2005 Standard

Are you sure you've compatibility level set to 90. For APPLy to work, compatibility level should be 90

use this check

EXEC sp_dbcmptlevel 'Yourdbname'

bmahony993
Yak Posting Veteran

58 Posts

Posted - 2008-06-06 : 13:21:52

Aha! Thanks so much for all the help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-06 : 13:26:03

You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources