项目中用到的sql语句

 SELECT *
      FROM(  SELECT ResourceBooking.*,
                    CustomerInformation.CustomerName as CustomerName,
                    CustomerInformation.Gender+'/'+ltrim(str(datediff(year,isnull(CustomerInformation.DateOfBirth,0),getDate()))) as AgeAndBirth,
                    ResourceBookingType.BookingName AS BookingName,
                    ResourceBookingType.BookingDescription AS BookingDescription,
                    RES.ResourceTypeID as ResourceTypeID,
                    RES.ResourceSubTypeID as ResourceSubTypeID,
                    RES.ResourceGroupID as ResourceGroupID,
                    RES.ResourceTableID as ResourceTableID,
                    RES.ResourcePKField as ResourcePKField,
                    RES.IDDescription as IDDescription,
                    RES.DicriptionName as EmployeeUserName
                    FROM ResourceBooking LEFT JOIN CustomerInformation
                    ON ltrim(rtrim(ResourceBooking.PatientID)) = ltrim(rtrim(CustomerInformation.CustomerID))
                    LEFT JOIN ResourceBookingType
                    ON ResourceBooking.BookingTypeID=ResourceBookingType.BookingTypeID
                    LEFT JOIN (SELECT Resource.*,
                    IDDescription =  CASE ResourceTables.ResourceTableName WHEN 'PayrollEmployees' THEN (SELECT EmployeeID From PayrollEmployees where EmployeeID = Resource.ResourcePKField )
                                                                           WHEN 'Facility' THEN (SELECT FacilityID FROM Facility WHERE FacilityID =Resource.ResourcePKField)
                                                                           END,
                    DicriptionName = CASE ResourceTables.ResourceTableName WHEN  'PayrollEmployees' then (SELECT EmployeeUserName From PayrollEmployees where EmployeeID = Resource.ResourcePKField )
                                                                           WHEN 'Facility' THEN (SELECT FacilityDescription FROM Facility WHERE FacilityID =Resource.ResourcePKField)
                                                                           END
                    FROM  Resource left join ResourceTables ON Resource.ResourceTableID = ResourceTables.ResourceTableID) AS RES
                    ON ResourceBooking.ResourceID = RES.ResourceID
      ) AS ResourceBooking
      WHERE (ResourceBooking.CompanyID = @CompanyID)
      AND (ResourceBooking.DivisionID = @DivisionID)
      AND (ResourceBooking.DepartmentID = @DepartmentID)
      AND (ResourceBooking.ResourceBookingState <>'3')
      AND (((@ResourceGroupID <>'' or @ResourceGroupID is not null)  and ResourceBooking.ResourceGroupID= @ResourceGroupID) or (@ResourceGroupID='' or @ResourceGroupID is null ))
      AND (((@EmployeeID <>'' or @EmployeeID is not null) and ResourceBooking.IDDescription=@EmployeeID) or (@EmployeeID='' or @EmployeeID is null))
      AND (cast(convert(nvarchar(30),ResourceBooking.FromDateTime,10)as DateTime)=cast(@FromDateTime as DateTime))