I have been working on a Maintenance Application. My company builds a specific type of system using specfic types of components. What I need to be able to do is keep track of which component is in which system given a certain event.
ComponentType Table: Ident, Type ( example1: 1, hard drive example2: Motherboard)
EventType Table: Ident, Type(example1: 1,installed example2:2, uninstalled)
System Table: Ident, SerialNum(example1: 1, 1234A example2:2, A4321)
Component Table: Ident, ComponentTypeIdent, SystemIdent, Slot(A system can have 2 HDD, but one motherboard for instance)(example1: 1, 1,1,2 example2:1,2,NULL(not in a system), NULL)
Event Table: Ident,EventTypeIdent,ComponentIdent,SystemIdent (example1: 1,1,1,1 example2 2,2,NULL(This should be set on postback of the component selection dropdown)
I need a query or stored procedure that will check a given EventTypeIdent, know if SystemIdent should be set to null(uninstall) or produce a list of Systems (install)that do not already have all slots filled for a given componentType and SystemIdent=NULL in Component Table.
Here is my latest attempt
IF @EventTypeIdent=1 SELECT Components.Ident FROM Components WHERE Components.SystemIdent IS NULL AND Components.ComponentTypeIdent=@ComponentTypeIdent