[sqlite] Trigger help or how to update id based on column content
Greetings! I have this table CREATE TABLE LSOpenJobs ( id integer primary key, ProjID integer, PSubClass, lang, ProjFund, RateType ); Imagine this set of records... 171421|132959|DOC-Trans|DE-DE|860.69|PER-WORD 171422|132959|DOC-Trans|ES-LA|624.96|PER-WORD 171423|132959|DOC-Trans|IT-IT|712.28|PER-WORD 171424|132959|DOC-Trans|PT-BR|738.91|PER-WORD 171425|132959|File-Proc|DE-DE|10.63|HOURS 171426|132959|File-Proc|ES-LA|10.63|HOURS 171427|132959|File-Proc|IT-IT|10.63|HOURS 171428|132959|File-Proc|PT-BR|10.63|HOURS 171429|132959|PM|DE-DE|116.86|10% 171430|132959|PM|ES-LA|96.91|10% 171431|132959|PM|IT-IT|102.02|10% 171432|132959|PM|PT-BR|119.55|10% 171433|132959|Trans-Create|DE-DE|297.28|HOURS 171434|132959|Trans-Create|ES-LA|333.52|HOURS 171435|132959|Trans-Create|IT-IT|297.28|HOURS 171436|132959|Trans-Create|PT-BR|445.92|HOURS 171437|132959|QuoteAppr|DE-DE ES-LA IT-IT PT-BR||HOURS 171438|132959|XTranslate|DE-DE ES-LA IT-IT PT-BR||HOURS 171439|132959|Delivery|DE-DE ES-LA IT-IT PT-BR||HOURS 171440|132959|Q-Notes|DE-DE ES-LA IT-IT PT-BR||Deliverable 171932|132959|Validation|DE-DE||PER-WORD 171933|132959|Validation|ES-LA||PER-WORD 171934|132959|Validation|IT-IT||PER-WORD 171935|132959|Validation|PT-BR||PER-WORD 172979|132959|TTX-Update|DE-DE|0.00|PER-WORD What I would like to do is to Update the ProjFund of all PM PSubClass automatically. I have a two step process, but I am wondering if there is a way to do this without the two steps or every time I update one of these records. these are my two steps: 1. SELECT RateType FROM LSOpenJobs WHERE ProjID=132959 AND PSubClass='PM' AND lang='DE-DE'; 2. Use the RateType, let's say it's 10%, to create a this update BEGIN; UPDATE LSOpenJobs SET ProjFund = ( SELECT round(sum(ProjFund) * 0.20,2) FROM LSOpenJobs WHERE ProjID = 132959 AND lang = 'DE-DE' AND PSubClass != 'PM' ) WHERE ProjID = 132959 AND lang = 'DE-DE' AND RateType = '10%' AND PSubClass = 'PM';COMMIT TRANSACTION; So, is there any way to kill this two-step process into one, or even better, create a trigger for PM that have "%" on the RateType? Any help would be greatly appreciated. Thanks. jos?
[sqlite] Trigger help or how to update id based on column content
On 2/18/2015 6:48 PM, Igor Tandetnik wrote: > On 2/18/2015 5:10 PM, jose i cabrera wrote: >> So, in reality, all the tasks of the project of >> like "lang" minus the PM, have to be added and 10% of that total be >> calculated to the (on this instance) DE-DE PM task. > > UPDATE LSOpenJobs SET ProjFund = > ( >SELECT round(sum(t2.ProjFund) * cast(LSOpenJobs.RateType as > integer)/100.0 , 2) >FROM LSOpenJobs t2 >where LSOpenJobs.ProjID=t2.ProjID and LSOpenJobs.lang=t2.lang >and t2.PSubClass != 'PM' > ) > WHERE PSubClass = 'PM'; > > This updates all PM projects at once, in the whole table. To be more > selective, add conditions in the last WHERE clause to taste. If I ever meet you, I will buy you lunch. ;-) Thanks, man.
[sqlite] Trigger help or how to update id based on column content
On 2/18/2015 5:10 PM, jose i cabrera wrote: > So, in reality, all the tasks of the project of > like "lang" minus the PM, have to be added and 10% of that total be > calculated to the (on this instance) DE-DE PM task. UPDATE LSOpenJobs SET ProjFund = ( SELECT round(sum(t2.ProjFund) * cast(LSOpenJobs.RateType as integer)/100.0 , 2) FROM LSOpenJobs t2 where LSOpenJobs.ProjID=t2.ProjID and LSOpenJobs.lang=t2.lang and t2.PSubClass != 'PM' ) WHERE PSubClass = 'PM'; This updates all PM projects at once, in the whole table. To be more selective, add conditions in the last WHERE clause to taste. -- Igor Tandetnik
[sqlite] Trigger help or how to update id based on column content
On 2/18/2015 4:37 PM, Igor Tandetnik wrote: > On 2/18/2015 4:19 PM, jose i cabrera wrote: >> I need to know what the percentage for this specific project ID, 132959, >> and language is going to be calculated. This may be different depending >> on the project. So, it may be 3%, 5%, 10%, 20%, 25%, etc. That is my >> problem, I don't know how to grab that without this step. > > Where is this information coming from? How do you know that RateType = > '10%' corresponds to 0.20 multiplier? How is your hypothetical trigger > supposed to know that? Ok, maybe a little explanation is needed... The PM task may be calculated by a percentage, which is what I am trying to figure out with this set, or other by other rates like HOURLY, FIX Amounts, etc. The percentage calculation is based on all the tasks for the project of like "lang" excluding the PM task. So, let's use an example: The user chooses to calculate the DOC-Trans task for DE-DE and after reading a log with word counts and figuring the amount of cost for the DOC-Trans task, a final price for the task is set to 860.69. What should happen now is that the DE-DE PM task be updated by the descriptive value set in RateType, which on this instance is 10%, but maybe different in different projects or by different languages. So, now, just with this 860.69 value for DE-DE DOC-Trans, the PM value must be updated with the calculation of the percentage being help by RateType on 860.69. On this instance, again, is 10%. So, in reality, all the tasks of the project of like "lang" minus the PM, have to be added and 10% of that total be calculated to the (on this instance) DE-DE PM task. So, if there were only these two tasks in this project, the records would be, 171421|132959|DOC-Trans|DE-DE|860.69|PER-WORD 171429|132959|PM|DE-DE|86.07|10% I hope this helps.
[sqlite] Trigger help or how to update id based on column content
On 2/18/2015 4:19 PM, jose i cabrera wrote: > I need to know what the percentage for this specific project ID, 132959, > and language is going to be calculated. This may be different depending > on the project. So, it may be 3%, 5%, 10%, 20%, 25%, etc. That is my > problem, I don't know how to grab that without this step. Where is this information coming from? How do you know that RateType = '10%' corresponds to 0.20 multiplier? How is your hypothetical trigger supposed to know that? -- Igor Tandetnik
[sqlite] Trigger help or how to update id based on column content
On 2/18/2015 3:59 PM, Igor Tandetnik wrote: > On 2/18/2015 2:36 PM, Jose I. Cabrera wrote: >> these are my two steps: >> 1. SELECT RateType FROM LSOpenJobs WHERE ProjID=132959 AND >> PSubClass='PM' AND lang='DE-DE'; > > What is the point of this step? I don't see where and how the value > you obtain therefrom is required for step 2. I need to know what the percentage for this specific project ID, 132959, and language is going to be calculated. This may be different depending on the project. So, it may be 3%, 5%, 10%, 20%, 25%, etc. That is my problem, I don't know how to grab that without this step. > >> 2. Use the RateType, let's say it's 10%, to create a this update >> UPDATE LSOpenJobs SET ProjFund =... >> WHERE >>ProjID = 132959 AND lang = 'DE-DE' AND RateType = '10%' AND >> PSubClass = 'PM'; > > If (ProjID=132959 AND PSubClass='PM' AND lang='DE-DE') condition was > good enough to retrieve a unique RateType in step 1, then it should be > good enough to identify a unique record to update in step 2. Why do > you believe an extra condition of (RateType = '10%') is necessary? I have to change the 20% to .20 to create the UPDATE. Correct?
[sqlite] Trigger help or how to update id based on column content
On 2/18/2015 2:36 PM, Jose I. Cabrera wrote: > these are my two steps: > 1. SELECT RateType FROM LSOpenJobs WHERE ProjID=132959 AND PSubClass='PM' AND > lang='DE-DE'; What is the point of this step? I don't see where and how the value you obtain therefrom is required for step 2. > 2. Use the RateType, let's say it's 10%, to create a this update > UPDATE LSOpenJobs SET ProjFund =... > WHERE >ProjID = 132959 AND lang = 'DE-DE' AND RateType = '10%' AND PSubClass = > 'PM'; If (ProjID=132959 AND PSubClass='PM' AND lang='DE-DE') condition was good enough to retrieve a unique RateType in step 1, then it should be good enough to identify a unique record to update in step 2. Why do you believe an extra condition of (RateType = '10%') is necessary? -- Igor Tandetnik
