use SuperCamps_MSCRM
GO
SELECT distinct    po.new_positiondefinitionidName, co.FullName, co.Address1_Name, co.Address1_Line1, co.Address1_Line2, co.Address1_City, co.Address1_PostalCode,
                      co.new_regionidName, co.Address1_County, co.MobilePhone, co.Telephone2, co.EMailAddress1, co.New_CRBcleared, co.New_Disclosuredate,
                      co.New_Disclosurenumber, co.New_SCCRBsenttoapplicant, co.New_IDcheckedandreturnedtoapplicant, co.New_SCCRBDate, co.New_SCCRBDisclosurenumber,
                      co.New_Reference1returned, co.New_Reference2returned, pr.New_name, co.ContactId,po.New_Employeeover21,po.New_Description
FROM          New_position AS po
INNER JOIN New_scproduct AS pr
ON po.new_productid = pr.New_scproductId
INNER JOIN Contact AS co ON po.new_employeeid = co.ContactId

WHERE     --(po.New_Occupiedfrom >= @datefrom) AND (po.New_Occupiedto <= @dateto) AND
pr.New_scproductId = '8381EC4D-D91C-E011-969B-B8AC6F8A2B34'

and po.New_positionId in
(
select distinct a.new_positionid from New_attendance as a
where a.New_Date >= '07/18/2011' and a.New_Date <= '07/22/2011' and a.DeletionStateCode = 0
)