SQL 2005 - Sub-Query Assistance needed

  • Please note, the SQL is handled dynamically by the server, therefore some of

    the items in my WHERE clauses will look odd to you. I know the NULL IS NOT

    NULL ... looks odd and does not make sense, please ignore these.

    My question has to deal with adding in a new field - which I believe will end

    up being a sub-query. I need to add in the last three dates a patient

    received a statement to this report. If you look at my query, I do a

    statement count ...

    =====================================================================

    (

    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

    =====================================================================

    This is working great. I believe I must add in the dates via a sub-query now
    on the last select statement. This seem right? If so, can anyone lend me a
    hand?

    [code]
    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
    /*
    GROUP BY
    gr.GuarantorId,
    pp.PatientProfileId,
    ppa.InsBalance,
    ppa.PatBalance,
    ppa.Balance,
    pc.Description,
    pv.PatientVisitId,
    pva.InsPayment,
    pva.PatPayment,
    pva.InsBalance,
    pva.PatBalance,
    pv.BillStatus,
    cs.Description

    HAVING
    (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 Gua

    (

    (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
    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

    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

    DROP TABLE #A

    DROP TABLE #B

    DROP TABLE #C

    DROP TABLE #GPAll

    [/code]

  • I have no time now to go into this, but you might wish to take a look at the function ROW_NUMBER(). Here is an excellent article on the subject: http://www.sqlservercentral.com/articles/T-SQL/66512/

    Greetz,
    Hans Brouwer

  • Jeff,

    I tried to run your query. You join with a table PatientProfile, but there is no such table. Could you provide this table with some data? Are there other missing objects?

    Greetz,
    Hans Brouwer

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply