Derived Attributes 1

[squidster]

Hi,

Sorry if I'm asking this question in the wrong forum, its either SQL or programming and probably a bit of both. I've been trying to work out the best way to do this and would appreciate any advice. Basically I need to have a flag/indicator attribute and am not clear of :-

a) the best place/table to put it in.
b) how this gets updated to show correct value

say I have two tables:-

[tt]CONTACT
contactId
....etc

PLACEMENT
placementId
contactId (FK)
startDate
endDate[/tt]

I need an idicator to show if a contact has an active placement which could be derived from the placement.startDate

The thing is the indicator is to be used for the contact detail, so which table should it go in?

Also how will it get updated. If it goes in the Placement table it will have an initial value of N as no endDate will initially be supplied, when the endDate is updated with a value then the indicator can be updated to Y as well but if no further update is done how will it be updated to show that the placement has expired (X)?

But if it goes in the contact table how will changes to the Placemnt records be reflected? Will this require two seperate updates?

I hope this makes sense.

 

[swampBoogie]

I don't see any real good reason to have it all. Especially as you will have some problems keeping it up to date. In order to do that you would need to schedule a daily job to update the status. (Wish should be placed in the contact table if it should be of any benefit.)

You can get the status when selecting with

select c.contactId,case when max(endDate) is null then 'N' else 'Y' end
from contact c left join placement p
on c.contactId = p.contactId
and current_date between startDate and endDate
where c.contactId = 4711
group by c.contactId

 

[squidster]

Thanks swampBoogie

I did briefly look into the scheduled job option which I understand can be done as a cron job but I'm not yet sure if this is feasible for me.

I wasn't sure if it could be done via SQl but what you have suggested works a treat!!

thanks again!!

 

[squidster]

Sorry one last question (just a small one!!)

the select returns colums for :-
contactId, case when max(placementEnd) is null then 'N' else 'Y' end.

Is it possible to return for contactId, statusInd.

I've tried status AS case when max(placementEnd) is null then 'N' else 'Y' end, but this returns an error.

 

[swampBoogie]

case when max(placementEnd) is null then 'N' else 'Y' end as StatusId

 

[squidster]

Doh!

thanks