Trigger help or how to update id based on column content

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Trigger help or how to update id based on column content

Jose I. Cabrera


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-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Trigger help or how to update id based on column content

Igor Tandetnik-2
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

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Trigger help or how to update id based on column content

jose isaias cabrera


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-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Trigger help or how to update id based on column content

Igor Tandetnik-2
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-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Trigger help or how to update id based on column content

jose isaias cabrera

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-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Trigger help or how to update id based on column content

Igor Tandetnik-2
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-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Trigger help or how to update id based on column content

jose isaias cabrera

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-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users