Technicians Attached to a Labour Designation

The technicians attached to a designation are stored in tblbill:

• TblInvmas.product_code = tblBill.parent_code (for the designation)
• tblBill.product_code = tblinvmas.product_code (for the technician)
• tblbill.contactid links the tech in the contact table

The query below will show ALL techs attached to AVTECH designation.

**    Change the AVTECH to the name of the designation you want to report on **

DECLARE @MyProd Varchar(8) = 'AVTECH'

select
        c.ContactName AS [CONTACT NAME],

        c.FirstName AS [FIRSTNAME],

        c.MidName AS [MIDDLE NAME],

        c.Surname AS [SURNAME],

        c.Adr1 AS [ADDRESS 1],

        c.Adr2 AS [ADDRESS 1],

        c.Adr3 AS [ADDRESS 1],

        c.State AS [STATE],

        c.Country AS [COUNTRY],

        c.Postcode AS [POSTAL CODE],

        c.Email AS [EMAIL]
   
from tblbill b
left outer join tblcontact c on c.id = b.contactid
Where (b.parent_code = @MyProd )
Order By c.contactname

 /* The query below will show all FREELANCE techs attached to a designation.

  Change the AVTECH to the name of the designation you want to report on

*/

DECLARE @MyProd Varchar(8) = 'AVTECH'

select
        c.ContactName AS [CONTACT NAME],

        c.FirstName AS [FIRSTNAME],

        c.MidName AS [MIDDLE NAME],

        c.Surname AS [SURNAME],

        c.Adr1 AS [ADDRESS 1],

        c.Adr2 AS [ADDRESS 1],

        c.Adr3 AS [ADDRESS 1],

        c.State AS [STATE],

        c.Country AS [COUNTRY],

        c.Postcode AS [POSTAL CODE],

        c.Email AS [EMAIL]
   
from tblbill b
left outer join tblcontact c on c.id = b.contactid
Where (b.parent_code = @MyProd and c.bFreeLanceContact = 1 )
Order By c.contactname