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)
 conditional update - decrement value

Author  Topic 

Ms.Longstocking
Starting Member

9 Posts
Posted - 2008-11-18 : 10:57:54
The objective is to decrement the existing value of one field based on a flag set in another table. The catch here is to make this reversible.

The two tables in question are as follows (I realize that there is some redundancy in the columns, but this should not change if it doesn't have to):

TEST_QUESTIONS
(testid[char], questionid[char], qremoved[bit])

ANSWER_SHEET
(answersheetid[char], testid[char], questionid[char], answer[char], weight[tinyint])

Every answer submitted for every question will take up one row in ANSWER_SHEET. Here is some test data to illustrate:

TEST_QUESTIONS
testid-----------questionid --------qremoved
LBCF00000001---LB00000601-------0
LBCF00000001---LB00000602-------0
LBCF00000001---LB00000603-------0
LBCF00000001---LB00000604-------0
LBCF00000001---LB00000605-------0
LBCF00000001---LB00000606-------0
LBCF00000001---LB00000607-------1
LBCF00000001---LB00000608-------0
LBCF00000001---LB00000609-------0
LBCF00000001---LB00000610-------0
LBCF00000001---LB00000611-------0
LBCF00000001---LB00000612-------0
LBCF00000001---LB00000613-------0
LBCF00000001---LB00000614-------0

ANSWER_SHEET
answersheetid ----testid --------questionid ---answer --weight
LBCFTK000000001 LBCF00000001 LB00000601 ---04 -------3
LBCFTK000000001 LBCF00000001 LB00000602 ---20 -------4
LBCFTK000000001 LBCF00000001 LB00000603 ---06 -------1
LBCFTK000000001 LBCF00000001 LB00000604 ---25 -------5
LBCFTK000000001 LBCF00000001 LB00000605 ---16 -------2
LBCFTK000000001 LBCF00000001 LB00000606 ---17 -------7
LBCFTK000000001 LBCF00000001 LB00000607 ---27 -------8
LBCFTK000000001 LBCF00000001 LB00000608 ---29 -------6
LBCFTK000000001 LBCF00000001 LB00000609 ---13 -------11
LBCFTK000000001 LBCF00000001 LB00000610 ---14 -------9
LBCFTK000000001 LBCF00000001 LB00000611 ---22 -------10
LBCFTK000000001 LBCF00000001 LB00000612 ---32 -------14
LBCFTK000000001 LBCF00000001 LB00000613 ---28 -------12
LBCFTK000000001 LBCF00000001 LB00000614 ---19 -------13
LBCFTK000000002 LBCF00000001 LB00000601 ---04 -------2
LBCFTK000000002 LBCF00000001 LB00000602 ---05 -------14
LBCFTK000000002 LBCF00000001 LB00000603 ---06 -------11
LBCFTK000000002 LBCF00000001 LB00000604 ---25 -------4
LBCFTK000000002 LBCF00000001 LB00000605 ---31 -------5
LBCFTK000000002 LBCF00000001 LB00000606 ---03 -------10
LBCFTK000000002 LBCF00000001 LB00000607 ---21 -------6
LBCFTK000000002 LBCF00000001 LB00000608 ---09 -------13
LBCFTK000000002 LBCF00000001 LB00000609 ---13 -------1
LBCFTK000000002 LBCF00000001 LB00000610 ---14 -------9
LBCFTK000000002 LBCF00000001 LB00000611 ---23 -------8
LBCFTK000000002 LBCF00000001 LB00000612 ---32 -------12
LBCFTK000000002 LBCF00000001 LB00000613 ---28 -------7
LBCFTK000000002 LBCF00000001 LB00000614 ---19 -------3
LBCFTK000000003 LBCF00000001 LB00000601 ---04 -------5
LBCFTK000000003 LBCF00000001 LB00000602 ---05 -------6
LBCFTK000000003 LBCF00000001 LB00000603 ---18 -------14
LBCFTK000000003 LBCF00000001 LB00000604 ---07 -------8
LBCFTK000000003 LBCF00000001 LB00000605 ---31 -------10
LBCFTK000000003 LBCF00000001 LB00000606 ---03 -------3
LBCFTK000000003 LBCF00000001 LB00000607 ---21 -------4
LBCFTK000000003 LBCF00000001 LB00000608 ---09 -------2
LBCFTK000000003 LBCF00000001 LB00000609 ---13 -------7
LBCFTK000000003 LBCF00000001 LB00000610 ---12 -------12
LBCFTK000000003 LBCF00000001 LB00000611 ---23 -------1
LBCFTK000000003 LBCF00000001 LB00000612 ---08 -------13
LBCFTK000000003 LBCF00000001 LB00000613 ---28 -------11
LBCFTK000000003 LBCF00000001 LB00000614 ---19 -------9

You'll notice that qremoved is a bit field and that one of the fields is marked 'true'.

What I need to do is to write a query that will decrease the weight(column 5 of 2nd table) of all the existing answers whose weight is greater than the one being removed by 1. Originally, there were 14 answers weighted 1 - 14. After one of the questions is removed the weights will be distributed 1 - 13.

Now, the trick to this will be to make this change reversible at some point in the future without adding another column or tables to the DB.

I need some help with how to approach this problem and the query(queries) itself.

Thanks in advance.

Regards,
MizPippz

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-11-18 : 11:08:41
do you mean this?

SELECT answersheetid, testid, questionid, answer, weight,
CASE WHEN weight>tq.MaxRemoved THEN weight-COALESCE(Remcount,0) ELSE weight END AS ChangedWeight
FROM ANSWER_SHEET as
OUTER APPLY (SELECT COUNT(*) AS Remcount,MAX(weight) AS MaxRemoved 
             FROM TEST_QUESTIONS
             WHERE testid=as.testid
             AND questionid<as.questionid
             AND qremoved=1)tq
Go to Top of Page

Ms.Longstocking
Starting Member

9 Posts
Posted - 2008-11-18 : 11:34:06
I'm not sure if I should have mentioned the tools I was using in my first post. SQL Server 2005. I apologise for the bad etiquette.

I had to change the alias 'as' to something else. Looks like there was an issue with using a reserved word.

Truth be told, I'm not even close to understanding the dynamics of this code. I tried executing it for fun
and received the following error message:
Aggregates on the right side of an APPLY cannot reference columns from the left side.

Also, I'm not sure why we're testing ---> questionid<as.questionid
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-11-18 : 11:47:23
the questionid comparison is to see how many preceding questions have been removed.

SELECT answersheetid, testid, questionid, answer, weight,
CASE WHEN questionid>tq.MaxRemoved THEN weight-COALESCE(Remcount,0) ELSE weight END AS ChangedWeight
FROM ANSWER_SHEET as
OUTER APPLY (SELECT COUNT(*) AS Remcount,MAX(questionid) AS MaxRemoved 
             FROM TEST_QUESTIONS
             WHERE testid=as.testid
             AND questionid<as.questionid
             AND qremoved=1)tq
Go to Top of Page

Ms.Longstocking
Starting Member

9 Posts
Posted - 2008-11-18 : 12:07:20
This gives me something, but no effect on the required column...

answersheetid------testid-------questionid---answer-–weight------ChangedWeight
LBCFTK000000001 LBCF00000001 LB00000601 ----04 ------3 --------------3
LBCFTK000000001 LBCF00000001 LB00000602 ----20 ------4 --------------4
LBCFTK000000001 LBCF00000001 LB00000603 ----06 ------1 --------------1
LBCFTK000000001 LBCF00000001 LB00000604 ----25 ------5 --------------5
LBCFTK000000001 LBCF00000001 LB00000605 ----16 ------2 --------------2
LBCFTK000000001 LBCF00000001 LB00000606 ----17 ------7 --------------7
LBCFTK000000001 LBCF00000001 LB00000607 ----27 ------8 --------------8
LBCFTK000000001 LBCF00000001 LB00000608 ----29 ------6 --------------6
LBCFTK000000001 LBCF00000001 LB00000609 ----13 ------11 ------------11
LBCFTK000000001 LBCF00000001 LB00000610 ----14 ------9 --------------9
LBCFTK000000001 LBCF00000001 LB00000611 ----22 ------10 ------------10
LBCFTK000000001 LBCF00000001 LB00000612 ----32 ------14 ------------14
LBCFTK000000001 LBCF00000001 LB00000613 ----28 ------12 ------------12
LBCFTK000000001 LBCF00000001 LB00000614 ----19 ------13 ------------13
LBCFTK000000002 LBCF00000001 LB00000601 ----04 ------2 --------------2
LBCFTK000000002 LBCF00000001 LB00000602 ----05 ------14 ------------14
LBCFTK000000002 LBCF00000001 LB00000603 ----06 ------11 ------------11
LBCFTK000000002 LBCF00000001 LB00000604 ----25 ------4 --------------4
LBCFTK000000002 LBCF00000001 LB00000605 ----31 ------5 --------------5
LBCFTK000000002 LBCF00000001 LB00000606 ----03 ------10 ------------10
LBCFTK000000002 LBCF00000001 LB00000607 ----21 ------6 --------------6
LBCFTK000000002 LBCF00000001 LB00000608 ----09 ------13 ------------13
LBCFTK000000002 LBCF00000001 LB00000609 ----13 ------1 --------------1
LBCFTK000000002 LBCF00000001 LB00000610 ----14 ------9 --------------9
LBCFTK000000002 LBCF00000001 LB00000611 ----23 ------8 --------------8
LBCFTK000000002 LBCF00000001 LB00000612 ----32 ------12 ------------12
LBCFTK000000002 LBCF00000001 LB00000613 ----28 ------7 --------------7
LBCFTK000000002 LBCF00000001 LB00000614 ----19 ------3 --------------3
LBCFTK000000003 LBCF00000001 LB00000601 ----04 ------5 --------------5
LBCFTK000000003 LBCF00000001 LB00000602 ----05 ------6 --------------6
LBCFTK000000003 LBCF00000001 LB00000603 ----18 ------14 ------------14
LBCFTK000000003 LBCF00000001 LB00000604 ----07 ------8 --------------8
LBCFTK000000003 LBCF00000001 LB00000605 ----31 ------10 ------------10
LBCFTK000000003 LBCF00000001 LB00000606 ----03 ------3 --------------3
LBCFTK000000003 LBCF00000001 LB00000607 ----21 ------4 --------------4
LBCFTK000000003 LBCF00000001 LB00000608 ----09 ------2 --------------2
LBCFTK000000003 LBCF00000001 LB00000609 ----13 ------7 --------------7
LBCFTK000000003 LBCF00000001 LB00000610 ----12 ------12 ------------12
LBCFTK000000003 LBCF00000001 LB00000611 ----23 ------1 --------------1
LBCFTK000000003 LBCF00000001 LB00000612 ----08 ------13 ------------13
LBCFTK000000003 LBCF00000001 LB00000613 ----28 ------11 ------------11
LBCFTK000000003 LBCF00000001 LB00000614 ----19 ------9 --------------9
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-11-18 : 12:30:37
quote:
Originally posted by Ms.Longstocking

This gives me something, but no effect on the required column...

answersheetid------testid-------questionid---answer-–weight------ChangedWeight
LBCFTK000000001 LBCF00000001 LB00000601 ----04 ------3 --------------3
LBCFTK000000001 LBCF00000001 LB00000602 ----20 ------4 --------------4
LBCFTK000000001 LBCF00000001 LB00000603 ----06 ------1 --------------1
LBCFTK000000001 LBCF00000001 LB00000604 ----25 ------5 --------------5
LBCFTK000000001 LBCF00000001 LB00000605 ----16 ------2 --------------2
LBCFTK000000001 LBCF00000001 LB00000606 ----17 ------7 --------------7
LBCFTK000000001 LBCF00000001 LB00000607 ----27 ------8 --------------8
LBCFTK000000001 LBCF00000001 LB00000608 ----29 ------6 --------------6
LBCFTK000000001 LBCF00000001 LB00000609 ----13 ------11 ------------11
LBCFTK000000001 LBCF00000001 LB00000610 ----14 ------9 --------------9
LBCFTK000000001 LBCF00000001 LB00000611 ----22 ------10 ------------10
LBCFTK000000001 LBCF00000001 LB00000612 ----32 ------14 ------------14
LBCFTK000000001 LBCF00000001 LB00000613 ----28 ------12 ------------12
LBCFTK000000001 LBCF00000001 LB00000614 ----19 ------13 ------------13
LBCFTK000000002 LBCF00000001 LB00000601 ----04 ------2 --------------2
LBCFTK000000002 LBCF00000001 LB00000602 ----05 ------14 ------------14
LBCFTK000000002 LBCF00000001 LB00000603 ----06 ------11 ------------11
LBCFTK000000002 LBCF00000001 LB00000604 ----25 ------4 --------------4
LBCFTK000000002 LBCF00000001 LB00000605 ----31 ------5 --------------5
LBCFTK000000002 LBCF00000001 LB00000606 ----03 ------10 ------------10
LBCFTK000000002 LBCF00000001 LB00000607 ----21 ------6 --------------6
LBCFTK000000002 LBCF00000001 LB00000608 ----09 ------13 ------------13
LBCFTK000000002 LBCF00000001 LB00000609 ----13 ------1 --------------1
LBCFTK000000002 LBCF00000001 LB00000610 ----14 ------9 --------------9
LBCFTK000000002 LBCF00000001 LB00000611 ----23 ------8 --------------8
LBCFTK000000002 LBCF00000001 LB00000612 ----32 ------12 ------------12
LBCFTK000000002 LBCF00000001 LB00000613 ----28 ------7 --------------7
LBCFTK000000002 LBCF00000001 LB00000614 ----19 ------3 --------------3
LBCFTK000000003 LBCF00000001 LB00000601 ----04 ------5 --------------5
LBCFTK000000003 LBCF00000001 LB00000602 ----05 ------6 --------------6
LBCFTK000000003 LBCF00000001 LB00000603 ----18 ------14 ------------14
LBCFTK000000003 LBCF00000001 LB00000604 ----07 ------8 --------------8
LBCFTK000000003 LBCF00000001 LB00000605 ----31 ------10 ------------10
LBCFTK000000003 LBCF00000001 LB00000606 ----03 ------3 --------------3
LBCFTK000000003 LBCF00000001 LB00000607 ----21 ------4 --------------4
LBCFTK000000003 LBCF00000001 LB00000608 ----09 ------2 --------------2
LBCFTK000000003 LBCF00000001 LB00000609 ----13 ------7 --------------7
LBCFTK000000003 LBCF00000001 LB00000610 ----12 ------12 ------------12
LBCFTK000000003 LBCF00000001 LB00000611 ----23 ------1 --------------1
LBCFTK000000003 LBCF00000001 LB00000612 ----08 ------13 ------------13
LBCFTK000000003 LBCF00000001 LB00000613 ----28 ------11 ------------11
LBCFTK000000003 LBCF00000001 LB00000614 ----19 ------9 --------------9


in real data wont questionid be continuous?
Go to Top of Page

Ms.Longstocking
Starting Member

9 Posts
Posted - 2008-11-18 : 16:23:50
I'm not quite sure what you mean by continuous.
The example above illustrates 3 answer sheets in the db (LBCKTK000000001, LBCKTK000000002, LBCKTK000000003)
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site