SELECT `wp_category`.`id` AS `category:id`, `wp_category`.`name` AS `category:name`, `wp_category`.`created` AS `category:created`, `wp_category`.`modified` AS `category:modified`, `wp_lib_items`.* FROM `wp_lib_items` LEFT OUTER JOIN `wp_lib_borrowers` ON (`wp_lib_items`.`id` = (SELECT `wp_b`.`id` FROM `wp_lib_borrowers` AS `wp_b` WHERE `wp_b`.`item_id` = wp_lib_items.id ORDER BY `wp_b`.`status` ASC LIMIT 1)) LEFT OUTER JOIN `wp_congregate_members` ON (`wp_lib_borrowers`.`member_id` = `wp_congregate_members`.`id`) LEFT JOIN `wp_lib_categories` AS `wp_category` ON (`wp_category`.`id` = `wp_lib_items`.`category_id`) ORDER BY IFNULL(NULLIF(`wp_lib_borrowers`.`due_date`, '0000-00-00'), '3333-33-33') DESC, `wp_lib_items`.`title` ASC LIMIT 10 OFFSET 0

2 Responses so far.

1. Simeon says:

   September 23, 2010 at 9:21 am

   Try this for ugly:

   set ANSI_NULLS ON
   set QUOTED_IDENTIFIER ON
   go
   
   ALTER PROCEDURE [dbo].[CustomReports_ManagerLicenseReport]
   	@SessionID INT
   AS
   
   -- Don't wait for locks.
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
   
   UPDATE [kronos].[dbo].[CUST_RPT_USAGE_Count]
      SET [UsageCount] = (SELECT [UsageCount]
   					   FROM [kronos].[dbo].[CUST_RPT_USAGE_Count]
   					   WHERE [EXTERNAL_REPORT_NAME] = 'ManagerLicenseReport') + 1
   	WHERE [EXTERNAL_REPORT_NAME] = 'ManagerLicenseReport'
   
   DECLARE @Now DATETIME
   SET @Now = GETDATE()
   	DECLARE @sqlCommand VARCHAR(8000),
   	@Headcount INT,
   	@params nvarchar(4000),
   	@PERID INT
   
   	DELETE FROM TEMPCOUNT
   	DECLARE AG CURSOR FORWARD_ONLY
   	FOR
   	SELECT DISTINCT JA.PERSONID
   	FROM dbo.JAIDS AS JA INNER JOIN
                       dbo.MYWTKEMPLOYEE ON JA.EMPLOYEEID = dbo.MYWTKEMPLOYEE.EMPLOYEEID
   					  WHERE (JA.PERSONID > 0) AND (JA.DELETEDSW = 0) AND EXISTS
                             (SELECT     PERSONID, LICENSETYPEID
                               FROM          dbo.PRSNLICTYPEMM
                               WHERE      (LICENSETYPEID = 3) AND (PRSNLICTYPEMM.PERSONID = JA.PERSONID)) AND (dbo.MYWTKEMPLOYEE.SESSIONID = @SessionID)
   	OPEN AG
   	FETCH NEXT FROM AG INTO @PERID
   		WHILE @@FETCH_STATUS=0
   		BEGIN
   			SELECT @sqlCommand =[dbo].[CustomReports_GetAccessGroupBy_TestErr](@PERID)
   			CREATE TABLE #temp(mycount int)
   			SET @sqlCommand = 'INSERT INTO #temp ' + @sqlCommand
   			EXEC (@sqlCommand)
   			--INSERT INTO ResultSt select @sqlCommand
   			--Select @Headcount = mycount from #temp
   			--INSERT INTO TEMPCOUNT select @PERID, @Headcount
   			INSERT INTO TEMPCOUNT select @PERID, mycount from #temp
   			DROP TABLE #temp
   		FETCH NEXT FROM AG INTO @PERID
   		END
   	CLOSE AG
   	DEALLOCATE AG
   
   SELECT DISTINCT
                       JA.PERSONID AS PersonID,
   					PE.PERSONNUM AS PersonNum,
   					PE.FULLNM AS PersonFullName,
   					EA.EMAILADDRESSTXT AS EmailAddress,
                       LA.LABORLEV1NM AS OrgUnitNum,
   					LA.LABORLEV1DSC AS OrgUnitName,
   					LA.LABORLEV4NM AS PerAdmin,
   					LA.LABORLEV4DSC AS PerAdminDesc,
                       AP.PROFILENM,
                       CASE AP.PROFILENM WHEN '&SUPER_USER_ACCESS' THEN 'ALL HOMES' ELSE dbo.CustomReports_GetAccessGroup(PE.PERSONID)
                       END AS AccessGroup, CASE AP.PROFILENM WHEN '&SUPER_USER_ACCESS' THEN dbo.HeadCountSU()
                       ELSE dbo.TEMPCOUNT.HeadCOUNT END AS HeadCount,
   					PN.PHONENUM AS PhoneNumber,
   					dbo.CustomReports_GetAccessGroupBy(PE.PERSONID) AS AccessGroupBy,
   					MAX(dbo.AUDITITEM.ENTEREDONDTM) AS LoginDate
   FROM         dbo.PHONENUMBER AS PN RIGHT OUTER JOIN
                     dbo.EMAILADDRESS AS EA RIGHT OUTER JOIN
   				  dbo.JAIDS AS JA INNER JOIN
   				  dbo.PERSON AS PE ON JA.PERSONID = PE.PERSONID INNER JOIN
   				  dbo.HOMEACCTHIST AS HH ON JA.EMPLOYEEID = HH.EMPLOYEEID INNER JOIN
   				  dbo.LABORACCT AS LA ON HH.LABORACCTID = LA.LABORACCTID AND GETDATE() BETWEEN HH.EFFECTIVEDTM AND ISNULL(HH.EXPIRATIONDTM,
   				  GETDATE() + 1) - 1 INNER JOIN
   				  dbo.PRSNACCSASSIGN AS PA ON PE.PERSONID = PA.PERSONID INNER JOIN
   				  dbo.ACCESSPROFILE AS AP ON PA.ACCESSPROFID = AP.ACCESSPROFID ON EA.PERSONID = PE.PERSONID AND EA.EMAILADDRESSTXT =
   					  (SELECT     TOP (1) EMAILADDRESSTXT
                               FROM          dbo.EMAILADDRESS
                               WHERE      (PERSONID = PE.PERSONID)
                               ORDER BY UPDATEDTM DESC) ON PN.PERSONID = JA.PERSONID AND PN.PHONENUM =
                             (SELECT     TOP (1) PHONENUM
                               FROM          dbo.PHONENUMBER
                               WHERE      (PERSONID = JA.PERSONID) AND (CONTACTTYPEID = 4)
                               ORDER BY UPDATEDTM DESC) FULL OUTER JOIN
                     dbo.USERACCOUNT AS UA ON JA.PERSONID = UA.PERSONID INNER JOIN
                     dbo.MYWTKEMPLOYEE AS WE ON WE.PERSONID = UA.PERSONID INNER JOIN
                     dbo.DATASOURCE INNER JOIN
                     dbo.CLIENTCONTEXT ON dbo.DATASOURCE.CLIENTCONTEXTID = dbo.CLIENTCONTEXT.CLIENTCONTEXTID ON
                     UA.USERACCOUNTNM = dbo.CLIENTCONTEXT.USERNAME INNER JOIN
                     dbo.AUDITITEM ON dbo.DATASOURCE.DATASOURCEID = dbo.AUDITITEM.DATASOURCEID LEFT OUTER JOIN
                     dbo.TEMPCOUNT ON LTRIM(RTRIM(EA.PERSONID)) = LTRIM(RTRIM(dbo.TEMPCOUNT.PersonID))
   WHERE     (JA.PERSONID > 0) AND (JA.DELETEDSW = 0) AND (WE.SESSIONID = @SessionID) AND EXISTS
                             (SELECT     PERSONID, LICENSETYPEID
                               FROM          dbo.PRSNLICTYPEMM
                               WHERE      (LICENSETYPEID = 3) AND (PERSONID = JA.PERSONID)) AND (dbo.AUDITITEM.AUDITTYPEID = 10)
   GROUP BY JA.PERSONID, PE.PERSONNUM, PE.FULLNM, EA.EMAILADDRESSTXT, LA.LABORLEV1NM, LA.LABORLEV1DSC, LA.LABORLEV4NM,
                         LA.LABORLEV4DSC, AP.PROFILENM, PE.PERSONID, PN.PHONENUM, dbo.TEMPCOUNT.HeadCOUNT
2. Azuka says:

   September 23, 2010 at 9:23 am

   That’s more scary than ugly…