项目中用到的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  & lt; & gt; ' 3 ' )
       AND  ((( @ResourceGroupID   & lt; & gt; ''   or   @ResourceGroupID   is   not   null )   and  ResourceBooking.ResourceGroupID =   @ResourceGroupID )  or  ( @ResourceGroupID = ''   or   @ResourceGroupID   is   null  ))
       AND  ((( @EmployeeID   & lt; & gt; ''   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 ))