select table_name
       ,stuff(oa.Last3Cols, 1,2,'') as Last3Cols  --stuff is just removing the first ', '
from   information_schema.tables t
outer apply (
       select top 3 ', ' + column_name
       from   information_schema.columns
       where  table_name = t.table_name
       order by ordinal_position desc
       for xml path('')     --this line turns the 3 rows into a single xml string - the path('') ensures that the xml markup is excluded
       ) oa (last3Cols)     --(last3Cols) is the declared column list of my derived table - in this case from an outer apply
where  table_name in (select top 2 table_name from information_schema.tables)

SELECT
       ... 
       ,stuff(oa.StatementDates, 1, 2, '') as Last3Statements 
FROM   
       ... 
outer apply ( 
       SELECT TOP 3 al.Created
       FROM   ActivityLog al
       INNER JOIN PatientProfile pp2 ON al.patientprofileID = pp2.patientprofileid
       LEFT JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId
       WHERE  pp2.GuarantorId = RefB.GuarantorId
       AND    al.created >= @startdate
       AND    al.created < @enddate
       AND    functionname LIKE '%PrintStatements%'
       ORDER BY al.Created DESC 
       for xml path('')
       )AS oa (StatementDates) 

WHERE
     ...
GROUP BY
     ...
ORDER BY
     ...

SELECT DISTINCT
    	dbo.FormatName(gr.Prefix , gr.First , gr.Middle , gr.Last , gr.Suffix) AS GuarantorName ,
	gr.GuarantorId ,
    	gr.First AS GuarantorFirst ,
    	gr.Last AS GuarantorLast , 
    	ISNULL(Convert(VarChar(20), gr.birthdate, 101),'')as GuarantorDOB,
    	LEFT(gr.SSN , 3) + '-' + SUBSTRING(gr.SSN , 4 , 2) + '-' + RIGHT(gr.SSN , 4) AS GuarantorSSN ,
    	'(' + LEFT(gr.Phone1 , 3) + ') ' + SUBSTRING(gr.Phone1 , 4 , 3) + '-' + SUBSTRING(gr.Phone1 , 7 , 4) AS Phone ,
    	'(' + LEFT(gr.Phone2 , 3) + ') ' + SUBSTRING(gr.Phone2 , 4 , 3) + '-' + SUBSTRING(gr.Phone2 , 7 , 4) AS Phone2 ,
    	RefA.TotalInsBalance AS GuarantorInsBalance ,
    	RefA.TotalPatBalance AS GuarantorPatBalance ,
    	RefA.TotalBalance AS GuarantorBalance ,
    	pp.PatientId ,
    	dbo.FormatName(pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix) AS PatientName ,
    	pp.First AS PatientFirst , 
    	pp.Last AS PatientLast ,  
    	ISNULL(Convert(VarChar(20), pp.birthdate, 101),'')as PatientDOB,
    	LEFT(pp.SSN , 3) + '-' + SUBSTRING(pp.SSN , 4 , 2) + '-' + RIGHT(pp.SSN , 4) AS PatientSSN ,
    	ISNULL(dbo.formatphone(pp.phone1 , 1) , '') AS PatientPhone1 ,
    	ISNULL(pp.Phone1Type , ' ') AS PatientPhone1Type ,
    	ISNULL(dbo.formatphone(pp.phone2 , 1) , '') AS PatientPhone2 ,
    	ISNULL(pp.Phone2Type , ' ') AS PatientPhone2Type ,
    	ISNULL(pp.Address1,'') AS PatientAddress1 , 
    	ISNULL(pp.Address2, '') AS PatientAddress2 , 
    	ISNULL(pp.City,'') AS PatientCity , 
    	ISNULL(pp.State,'') AS PatientState ,  
    	ISNULL(pp.Zip,'') AS PatientZip ,
    	RefB.InsBalance ,
    	RefB.PatBalance ,
    	RefC.LastPaymentDate ,
    	RefC.Amount ,
    	RefB.Balance ,
    	pv.Visit ,
    	RefB.Description ,
    	pv.TicketNumber ,
    	RefB.VisitInsPayment ,
    	RefB.VisitPatPayment ,
    	RefB.VisitInsBalance ,
    	RefB.VisitPatBalance ,
    	RefB.CollectionsStatus ,
		ISNULL(ic.ListName, '') AS [Insurance Carrier] , 
    	ISNULL(pi.InsuredId , '') AS InsuredId ,
    	RefB.StatementCount
       ,stuff(oa.StatementDates, 1, 2, '') as Last3Statements
FROM 	
	#A as RefA 
	INNER JOIN #B as RefB ON RefB.GuarantorId = RefA.GuarantorId 
	LEFT OUTER JOIN #C as RefC ON RefA.GuarantorId = RefC.GuarantorId
	INNER JOIN Guarantor gr ON gr.GuarantorId = RefA.GuarantorId 
	INNER JOIN PatientProfile pp ON pp.PatientProfileId = RefB.PatientProfileId
	INNER JOIN PatientVisit pv ON pv.PatientVisitId = RefB.PatientVisitId 
	LEFT JOIN InsuranceCarriers ic ON pv.PrimaryInsuranceCarriersId = ic.InsuranceCarriersId 
	LEFT JOIN PatientInsurance pi ON pp.PatientProfileID = pi.PatientProfileID
                                                  AND (
                                                        pi.Inactive IS NULL
                                                        OR pi.Inactive = 0
                                                      )
                                                  AND pi.OrderForClaims = 1
outer apply (
       SELECT TOP 3 al.Created
       FROM   ActivityLog al
       INNER JOIN PatientProfile pp2 ON al.patientprofileID = pp2.patientprofileid
       LEFT JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId
       WHERE  pp2.GuarantorId = RefB.GuarantorId
       AND    al.created >= @startdate
       AND    al.created < @enddate
       AND    functionname LIKE '%PrintStatements%'
       ORDER BY al.Created DESC
       for xml path('')
       )AS oa (StatementDates)
WHERE 
	(pv.Visit >= ISNULL(NULL,'1/1/1900') 
	AND pv.Visit < DATEADD(d, 1, ISNULL(NULL,'1/1/3000'))
	)
	AND 
	(
	1 < 2 OR 1 > 3
	OR (CASE WHEN 1 = 2 THEN ABS(RefB.InsBalance) END > NULL)
	OR (CASE WHEN 1 = 2 THEN ABS(RefB.PatBalance) END > NULL) 
	OR (CASE WHEN 1 = 3 THEN ABS(RefB.InsBalance) END < NULL)
	OR (CASE WHEN 1 = 3 THEN ABS(RefB.PatBalance) END < NULL)
	)
	AND
	( 
	(NULL IS NOT NULL AND pv.VisitOwnerMID IN(NULL)) 
	 OR NULL IS NULL 
	)
	AND -- Filter on Number of Statements Received
	((RefB.StatementCount >= 2) OR (2 IS NULL))

/*
GROUP BY
	dbo.FormatName(gr.Prefix, gr.First, gr.Middle, gr.Last, gr.Suffix),
	LEFT(gr.SSN, 3) + '-' + SUBSTRING(gr.SSN, 4, 2) + '-' + RIGHT(gr.SSN, 4), 
	'(' + LEFT(gr.Phone1, 3) + ') ' + SUBSTRING(gr.Phone1, 4, 3) + '-' + SUBSTRING(gr.Phone1, 7, 4), 
	'(' + LEFT(gr.Phone2, 3) + ') ' + SUBSTRING(gr.Phone2, 4, 3) + '-' + SUBSTRING(gr.Phone2, 7, 4),
	RefA.TotalInsBalance, 
	RefA.TotalPatBalance, 
	RefA.TotalBalance,
	dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix),
	RefB.InsBalance, 
	RefB.PatBalance, 
	RefC.LastPaymentDate, 
	RefC.Amount,
	RefB.Balance, 
	pv.Visit,
	RefB.Description, 
	pv.TicketNumber,
	RefB.VisitInsPayment, 
	RefB.VisitPatPayment, 
	RefB.VisitInsBalance, 
	RefB.VisitPatBalance, 
	RefB.CollectionsStatus
*/

ORDER BY 
	GuarantorName, 
	PatientName

SET NOCOUNT ON

DECLARE
        @startdate DATETIME ,
        @enddate DATETIME

SET @startdate = ISNULL(NULL , '1/1/1900') 
SET @enddate = DATEADD(DAY , 1 , ISNULL(NULL , '1/1/3000'))

IF (
     SELECT OBJECT_ID ('tempdb..#A', 'U')
   ) IS NOT NULL 
   DROP TABLE #A
IF (
     SELECT OBJECT_ID ('tempdb..#B', 'U')
   ) IS NOT NULL 
   DROP TABLE #B
IF (
     SELECT OBJECT_ID ('tempdb..#C', 'U')
   ) IS NOT NULL 
   DROP TABLE #C
IF (
     SELECT OBJECT_ID ('tempdb..#GPAll', 'U')
   ) IS NOT NULL 
   DROP TABLE #GPAll

CREATE TABLE #A
       (
         GuarantorId INT NOT NULL ,
         TotalInsBalance MONEY NULL ,
         TotalPatBalance MONEY NULL ,
         TotalBalance MONEY NULL,
       	
       )

CREATE TABLE #B
       (
         GuarantorId INT NOT NULL ,
         PatientProfileId INT NOT NULL ,
         InsBalance MONEY NULL ,
         PatBalance MONEY NULL ,
         Balance MONEY NULL ,
         Description VARCHAR(255) NULL ,
         PatientVisitId INT NULL ,
         VisitInsPayment MONEY NULL ,
         VisitPatPayment MONEY NULL ,
         VisitInsBalance MONEY NULL ,
         VisitPatBalance MONEY NULL ,
         CollectionsStatus VARCHAR(255) NULL ,
         StatementCount INT
       )

CREATE TABLE #GPAll
       (
         GuarantorId INT NOT NULL ,
         PaymentDate DATETIME NOT NULL ,
         Amount MONEY NULL
       )

CREATE TABLE #C
       (
         GuarantorId INT NULL ,
         LastPaymentDate DATETIME NULL ,
         PaymentAge INT NULL ,
         Amount MONEY NULL,
       	
       )

INSERT
    #A
    SELECT
        gr.GuarantorId ,
        SUM(ppa.InsBalance) AS TotalInsBalance ,
        SUM(ppa.PatBalance) AS TotalPatBalance ,
        SUM(ppa.Balance) AS TotalBalance
    FROM
        PatientProfile pp
        INNER JOIN Guarantor gr ON pp.GuarantorId = gr.GuarantorId
        INNER JOIN PatientProfileAgg ppa ON pp.PatientProfileId = ppa.PatientProfileId
    WHERE
        (
          (
            NULL IS NOT NULL
            AND gr.GuarantorId IN ( NULL )
          )
          OR NULL IS NULL
        )
    GROUP BY
        gr.GuarantorId
    ORDER BY
        gr.GuarantorId

INSERT
    #B
    SELECT
        gr.GuarantorId AS GuarantorId ,
        pp.PatientProfileId AS PatientProfileId ,
        ppa.InsBalance AS InsBalance ,
        ppa.PatBalance AS PatBalance ,
        ppa.Balance AS Balance ,
        pc.Description AS Description ,
        pv.PatientVisitId AS PatientVisitId ,
        pva.InsPayment AS VisitInsPayment ,
        pva.PatPayment AS VisitPatPayment ,
        pva.InsBalance AS VisitInsBalance ,
        pva.PatBalance AS VisitPatBalance ,
        cs.Description AS CollectionsStatus ,
        (
          SELECT
            COUNT(*)
          FROM
            edistatement es
            INNER JOIN edistatementfile esf2 ON es.edistatementfileID = esf2.edistatementfileID
          WHERE
            esf2.filetransmitted >= @startdate
            AND esf2.filetransmitted < @enddate
            AND es.guarantorId = gr.guarantorID
        ) + (
              SELECT
                COUNT(*)
              FROM
                activitylog al
                INNER JOIN dbo.PatientProfile pp2 ON al.patientprofileID = pp2.patientprofileid
              WHERE
                pp2.GuarantorId = pp.GuarantorId
                AND al.created >= @startdate
                AND al.created < @enddate
                AND functionname LIKE '%PrintStatements%'
            ) AS StatementCount
    FROM
        PatientProfile pp
        INNER JOIN Guarantor gr ON pp.GuarantorId = gr.GuarantorId
        INNER JOIN PatientProfileAgg ppa ON pp.PatientProfileId = ppa.PatientProfileId
        INNER JOIN PatientCorrespondence pc ON pp.PatientProfileId = pc.PatientProfileId
        INNER JOIN PatientVisit pv ON pp.PatientProfileId = pv.PatientProfileId
                                      AND pc.PatientVisitId = pv.PatientVisitId
        INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
        INNER JOIN MedLists cs ON pv.CollectionsStatusMId = cs.MedListsId
    WHERE
        -- Filter on Guarantor 
        (
          (
            NULL IS NOT NULL
            AND gr.GuarantorId IN ( NULL )
          )
          OR NULL IS NULL
        )
        AND -- Filter on Visit Collection Status
        (
          (
            NULL IS NOT NULL
            AND pv.CollectionsStatusMId IN ( NULL )
          )
          OR NULL IS NULL
        )
        AND pv.Visit >= ISNULL(NULL , '1/1/1900')
        AND pv.Visit < DATEADD(day , 1 , ISNULL(NULL , '1/1/3000'))
        AND pv.CollectionsNextContactDate >= ISNULL(NULL , '1/1/1900')
        AND pv.CollectionsNextContactDate < DATEADD(day , 1 , ISNULL(NULL , '1/1/3000'))
        AND pv.BillStatus = 13

INSERT
    #GPAll
    SELECT
        px.GuarantorId ,
        px.PaymentDate AS PaymentDate ,
        SUM(px.Amount) AS Amount
    FROM
        (
          SELECT
            gr.GuarantorId ,
            b.Entry AS PaymentDate ,
            pm.Amount
          FROM
            PaymentMethod pm
            INNER JOIN Batch b ON pm.BatchId = b.BatchId
            INNER JOIN patientprofile pp ON pm.PayerId = pp.PatientProfileId
            INNER JOIN Guarantor gr ON pp.GuarantorId = gr.GuarantorId
          WHERE
            pm.PayerType = 'Patient'
            AND pm.Amount <> 0
            AND --- Filter on Guarantor
            (
              (
                NULL IS NOT NULL
                AND gr.GuarantorId IN ( NULL )
              )
              OR NULL IS NULL
            )
          UNION ALL
          SELECT
            gr.GuarantorId ,
            b.Entry AS PaymentDate ,
            pm.Amount
          FROM
            PaymentMethod pm
            INNER JOIN Batch b ON pm.BatchId = b.BatchId
            INNER JOIN Guarantor gr ON pm.PayerId = gr.GuarantorId
          WHERE
            pm.PayerType = 'Guarantor'
            AND pm.Amount <> 0
            AND (
                  (
                    NULL IS NOT NULL
                    AND gr.GuarantorId IN ( NULL )
                  )
                  OR NULL IS NULL
                )
        ) AS px
    GROUP BY
        GuarantorId ,
        PaymentDate
    ORDER BY
        GuarantorId

INSERT
    #C
    SELECT
        gp.GuarantorId ,
        gp.PaymentDate AS LastPaymentDate ,
        DATEDIFF(day , gp.PaymentDate , GETDATE()) AS PaymentAge ,
        gp.Amount
    FROM
        #GPAll gp
    WHERE
        gp.PaymentDate = (
                           SELECT MAX (PaymentDate) FROM #GPAll gpm WHERE gp . GuarantorId = gpm . GuarantorId
                         )
    ORDER BY
        GuarantorId

-- Delete any guarantor where payment made in last 90 days.
IF 0 = 1 
   BEGIN
         SELECT
            pp.GuarantorID
         INTO
            #UniqueDelete
         FROM
            VisitTransactions vt
            JOIN PaymentMethod pm ON vt.PaymentMethodID = pm.PaymentMethodID
            JOIN PatientVisit pv ON vt.PatientVisitID = pv.PatientVisitID
            JOIN PatientProfile pp ON pv.PatientProfileID = pp.PatientProfileID
            JOIN Transactions tc ON vt.VisitTransactionsId = tc.VisitTransactionsId
         WHERE
            pm.Source = 1
            AND tc.Type = 'P'
            AND DATEDIFF(dd , pm.DateofEntry , GETDATE()) < 90
            AND GuarantorID --= @pGuarantorID
			IN ( SELECT DISTINCT
                        GuarantorID
                       FROM
                        #A
                       UNION
                       SELECT DISTINCT
                        GuarantorID
                       FROM
                        #B
                       UNION
                       SELECT DISTINCT
                        GuarantorID
                       FROM
                        #C
                       UNION
                       SELECT DISTINCT
                        GuarantorID
                       FROM
                        #GPAll )
            AND pp.GuarantorID IS NOT NULL
				
         DELETE FROM
            #A
         WHERE
            GuarantorID IN ( SELECT
                                *
                             FROM
                                #UniqueDelete )
         DELETE FROM
            #B
         WHERE
            GuarantorID IN ( SELECT
                                *
                             FROM
                                #UniqueDelete )
         DELETE FROM
            #C
         WHERE
            GuarantorID IN ( SELECT
                                *
                             FROM
                                #UniqueDelete )
         DELETE FROM
            #GPAll
         WHERE
            GuarantorID IN ( SELECT
                                *
                             FROM
                                #UniqueDelete )
         DROP TABLE #UniqueDelete
   END
-- End of Last Payment Deletion

SELECT DISTINCT
    dbo.FormatName(gr.Prefix , gr.First , gr.Middle , gr.Last , gr.Suffix) AS GuarantorName ,
    gr.GuarantorId ,
    gr.First AS GuarantorFirst ,
    gr.Last AS GuarantorLast ,
    ISNULL(CONVERT(VARCHAR(20) , gr.birthdate , 101) , '') AS GuarantorDOB ,
    LEFT(gr.SSN , 3) + '-' + SUBSTRING(gr.SSN , 4 , 2) + '-' + RIGHT(gr.SSN , 4) AS GuarantorSSN ,
    '(' + LEFT(gr.Phone1 , 3) + ') ' + SUBSTRING(gr.Phone1 , 4 , 3) + '-' + SUBSTRING(gr.Phone1 , 7 , 4) AS Phone ,
    '(' + LEFT(gr.Phone2 , 3) + ') ' + SUBSTRING(gr.Phone2 , 4 , 3) + '-' + SUBSTRING(gr.Phone2 , 7 , 4) AS Phone2 ,
    RefA.TotalInsBalance AS GuarantorInsBalance ,
    RefA.TotalPatBalance AS GuarantorPatBalance ,
    RefA.TotalBalance AS GuarantorBalance ,
    pp.PatientId ,
    dbo.FormatName(pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix) AS PatientName ,
    pp.First AS PatientFirst ,
    pp.Last AS PatientLast ,
    ISNULL(CONVERT(VARCHAR(20) , pp.birthdate , 101) , '') AS PatientDOB ,
    LEFT(pp.SSN , 3) + '-' + SUBSTRING(pp.SSN , 4 , 2) + '-' + RIGHT(pp.SSN , 4) AS PatientSSN ,
    ISNULL(dbo.formatphone(pp.phone1 , 1) , '') AS PatientPhone1 ,
    ISNULL(pp.Phone1Type , ' ') AS PatientPhone1Type ,
    ISNULL(dbo.formatphone(pp.phone2 , 1) , '') AS PatientPhone2 ,
    ISNULL(pp.Phone2Type , ' ') AS PatientPhone2Type ,
    ISNULL(pp.Address1 , '') AS PatientAddress1 ,
    ISNULL(pp.Address2 , '') AS PatientAddress2 ,
    ISNULL(pp.City , '') AS PatientCity ,
    ISNULL(pp.State , '') AS PatientState ,
    ISNULL(pp.Zip , '') AS PatientZip ,
    RefB.InsBalance ,
    RefB.PatBalance ,
    RefC.LastPaymentDate ,
    RefC.Amount ,
    RefB.Balance ,
    pv.Visit ,
    RefB.Description ,
    pv.TicketNumber ,
    RefB.VisitInsPayment ,
    RefB.VisitPatPayment ,
    RefB.VisitInsBalance ,
    RefB.VisitPatBalance ,
    RefB.CollectionsStatus ,
    ISNULL(ic.ListName , '') AS [Insurance Carrier] ,
    ISNULL(pi.InsuredId , '') AS InsuredId ,
    RefB.StatementCount ,
    STUFF(oa.StatementDates , 1 , 2 , '') AS Last3Statements
FROM
    #A AS RefA
    INNER JOIN #B AS RefB ON RefB.GuarantorId = RefA.GuarantorId
    LEFT OUTER JOIN #C AS RefC ON RefA.GuarantorId = RefC.GuarantorId
    INNER JOIN Guarantor gr ON gr.GuarantorId = RefA.GuarantorId
    INNER JOIN PatientProfile pp ON pp.PatientProfileId = RefB.PatientProfileId
    INNER JOIN PatientVisit pv ON pv.PatientVisitId = RefB.PatientVisitId
    LEFT JOIN InsuranceCarriers ic ON pv.PrimaryInsuranceCarriersId = ic.InsuranceCarriersId
    LEFT JOIN PatientInsurance pi ON pp.PatientProfileID = pi.PatientProfileID
                                     AND (
                                           pi.Inactive IS NULL
                                           OR pi.Inactive = 0
                                         )
                                     AND pi.OrderForClaims = 1
    OUTER APPLY (
                  SELECT TOP 3
                    al.Created
                  FROM
                    ActivityLog al
                    INNER JOIN PatientProfile pp2 ON al.patientprofileID = pp2.patientprofileid
                    LEFT JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId
                  WHERE
                    pp2.GuarantorId = RefB.GuarantorId
                    AND al.created >= @startdate
                    AND al.created < @enddate
                    AND functionname LIKE '%PrintStatements%'
                  ORDER BY
                    al.Created DESC
                FOR
                  XML PATH('')
                ) AS oa ( StatementDates )
WHERE
    (
      pv.Visit >= ISNULL(NULL , '1/1/1900')
      AND pv.Visit < DATEADD(d , 1 , ISNULL(NULL , '1/1/3000'))
    )
    AND (
          1 < 2
          OR 1 > 3
          OR ( CASE WHEN 1 = 2 THEN ABS(RefB.InsBalance)
               END > NULL )
          OR ( CASE WHEN 1 = 2 THEN ABS(RefB.PatBalance)
               END > NULL )
          OR ( CASE WHEN 1 = 3 THEN ABS(RefB.InsBalance)
               END < NULL )
          OR ( CASE WHEN 1 = 3 THEN ABS(RefB.PatBalance)
               END < NULL )
        )
    AND (
          (
            NULL IS NOT NULL
            AND pv.VisitOwnerMID IN ( NULL )
          )
          OR NULL IS NULL
        )
    AND -- Filter on Number of Statements Received
    (
      ( RefB.StatementCount >= 2 )
      OR ( 2 IS NULL )
    )
ORDER BY
    GuarantorName ,
    PatientName

DROP TABLE #A
DROP TABLE #B
DROP TABLE #C
DROP TABLE #GPAll

   OUTER APPLY (
                  SELECT TOP 3
                    ', ' + convert(varchar(40), al.Created, 101) 
                  FROM
                    ActivityLog al
                    INNER JOIN PatientProfile pp2 ON al.patientprofileID = pp2.patientprofileid
                    LEFT JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId
                  WHERE
                    pp2.GuarantorId = RefB.GuarantorId
                    AND al.created >= @startdate
                    AND al.created < @enddate
                    AND functionname LIKE '%PrintStatements%'
                  ORDER BY
                    al.Created DESC
                FOR
                  XML PATH('')
                ) AS oa ( StatementDates )

quote:

---

Originally posted by JeffS23

TG -

YOU ARE AMAZING! I am in heaven!

---