2 Comment so far

• 

  Simeon

  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
• 

  Azuka

  September 23, 2010 at 9:23 am

  That’s more scary than ugly…

Add Your Comment