Complex SQL 2005 Query Help Needed

  • First and foremost, the SQL is handled dynamically by the server - therefore, the coding in the WHERE clause will look odd to you. Please ignore this, as its not the issue.

    What my client needs is a General Ledger Report that has an off-setting Credit to a Debit OR Debit to a Credit.

    The Charges will be the "Credit" and is Identified in my SQL below with a "Insert Charges" before the Insert on the #Summary. The Payments and Adjustments will be the "Debit" and can be found in my SQL below following same concept.

    What I need help with is doing an insert with the exact same values (minus two fields, which I can edit afterwards) that will off-set the "Charge" or "Debit".

    For example, a charge was entered into the system today and in my dataset it would look like this:

    TicketNumber: 000584

    Visit: 2010-07-28 12:50:00.000

    EntityCode: 01

    Fiscal Year: 10

    Fiscal Period: 01

    Subsystem: GL

    SourceCode: JRNENT

    DeptSubAccount: 1973 0302

    Amount: 104.00

    Debit/Credit: C

    Description: Centricity Entry

    Proj Id:

    Filler:

    What I need is to copy this exact same result and In my Debit/Credit field I would change it from a "C" to a "D" and I would also need to change the DeptSubAccount field to a hardcoded value which I can add in that logic after I get this part addressed.

    Essentially, there are 3 components to the report: a charge or a Payment or an Adjustment. If I have one of these, I need to copy it and produce another row in my dataset and flip from either a "D" to a "C" or from a "C" to a "D". I hope this makes sense, if not ask me where your confused and I'll do my best to re-explain it.

    SET NOCOUNT ON

    /********* Determine the beginning of the fiscal year ***************************/

    DECLARE

    @otherlong INT

    SELECT

    @otherlong = ISNULL(otherlong , 1)

    FROM

    Medlists

    WHERE

    tablename = 'fiscalyear'

    /***********************************************************************************/

    CREATE TABLE #Summary

    (

    [TicketNumber]VARCHAR(20),

    [Visit]DATETIME,

    [EntityCode]VARCHAR(2),

    [Fiscal Year]VARCHAR(2),

    [Fiscal Period]VARCHAR(2),

    [Subsystem]VARCHAR(2),

    [Source Code]VARCHAR(6),

    [DeptSubAccount]VARCHAR(20),

    [Amount]VARCHAR(22),

    [Debit/Credit]VARCHAR(1),

    [Description]VARCHAR(50),

    [Proj Id]VARCHAR(6),

    [Filler]VARCHAR(16)

    )

    -- Insert Charges

    INSERT INTO #Summary

    SELECT

    pv.TicketNumber,

    pv.Visit,

    '01' AS EntityCode,

    RIGHT(YEAR(DATEADD(m , -( @otherlong - 1 ) , Visit)) , 2) AS [Fiscal Year],

    LEFT( CONVERT(VARCHAR, DATEADD(m , -( @otherlong - 1 ) , Visit), 101), 2) AS [Fiscal Period],

    'GL' AS [Subsystem],

    'JRNENT' AS [Source Code],

    LEFT(ISNULL(doc.Ledger , '') + SPACE(10) , 10) + LEFT(LEFT(ISNULL(fac.Ledger , '') , 2) + LEFT(ISNULL(fin.Ledger , '') , 2) + SPACE(10) , 10) AS [DeptSubAccount],

    LEFT(CONVERT(varchar, SUM(pvp.totalfee)) + SPACE(22), 22) AS [Amount],

    'C' AS [Debit/Credit],

    'Centricity Entry' + SPACE(34) AS [Description],

    SPACE(6) AS [Proj Id],

    SPACE(16) AS [Filler]

    FROM

    PatientVisit pv

    INNER JOIN DoctorFacility comp ON pv.CompanyId = comp.DoctorFacilityId

    INNER JOIN DoctorFacility fac ON pv.FacilityId = fac.DoctorFacilityId

    INNER JOIN DoctorFacility doc ON pv.DoctorId = doc.DoctorFacilityId

    LEFT OUTER JOIN Medlists fin ON pv.FinancialClassMId = fin.MedListsId

    INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId

    INNER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId

    INNER JOIN Batch b ON pvp.BatchID = b.BatchID

    INNER JOIN PatientVisitProcsAgg pvpa ON pvp.PatientVisitProcsID = pvpa.PatientVisitProcsID

    LEFT OUTER JOIN Procedures p ON pvp.ProceduresID = p.ProceduresID

    LEFT JOIN MedLists Procs ON p.DepartmentMId = procs.MedListsId

    WHERE

    b.entry >= ISNULL('07/01/2010','1/1/1900') AND b.Status <> 0

    AND b.entry < DATEADD(DAY,1,ISNULL('07/31/2010','1/1/3000')

    )

    AND --Filter on Facility

    (

    (NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR

    (NULL IS NULL)

    )

    AND --Filter on Company

    (

    (NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR

    (NULL IS NULL)

    )

    AND --Filter on Financial Class

    (

    (NULL IS NOT NULL AND pv.FinancialClassMID IN (NULL)) OR

    (NULL IS NULL)

    )

    GROUP BY

    pv.TicketNumber,

    pv.Visit,

    doc.Ledger,

    fac.Ledger,

    fin.Ledger

    HAVING

    SUM(pvp.TotalFee) <> 0

    -- Insert Payments

    INSERT INTO #Summary

    SELECT

    pv.TicketNumber,

    pv.Visit,

    '01' AS EntityCode,

    RIGHT(YEAR(DATEADD(m , -( @otherlong - 1 ) , b.Entry)) , 2) AS [Fiscal Year],

    LEFT(CONVERT(VARCHAR, DATEADD(m , -( @otherlong - 1 ) , b.Entry), 101), 2) AS [Fiscal Period],

    'GL' AS [Subsystem],

    'JRNENT' AS [Source Code],

    LEFT(ISNULL(pymt.Ledger , '') + SPACE(20) , 20) AS [DeptSubAccount],

    LEFT(CONVERT(varchar, t.amount) + SPACE(22), 22) AS [Amount],

    'D' AS [Debit/Credit],

    'Centricity Entry' + SPACE(34) AS [Description],

    SPACE(6) AS [Proj Id],

    SPACE(16) AS [Filler]

    FROM

    PatientVisit pv

    INNER JOIN DoctorFacility comp ON pv.CompanyId = comp.DoctorFacilityId

    INNER JOIN DoctorFacility fac ON pv.FacilityId = fac.DoctorFacilityId

    INNER JOIN DoctorFacility doc ON pv.DoctorId = doc.DoctorFacilityId

    LEFT OUTER JOIN Medlists fin ON pv.FinancialClassMId = fin.MedListsId

    INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId

    INNER JOIN VisitTransactions vt ON pv.PatientVisitId = vt.PatientVisitid

    INNER JOIN PaymentMethod pm ON vt.PaymentMethodId = pm.PaymentMethodId

    INNER JOIN Batch b ON pm.BatchID = b.BatchID

    INNER JOIN Transactions t ON vt.VisitTransactionsId = t.VisitTransactionsId

    LEFT OUTER JOIN MedLists at ON t.ActionTypeMId = at.MedListsId

    LEFT OUTER JOIN (select * from medlists where tablename = 'PaymentTypes') pymt ON t.ActionTypeMId = pymt.MedlistsId

    WHERE

    t.Action = 'P' AND b.Status <> 0

    AND NOT EXISTS (SELECT 1 FROM PaymentMethod pm1 WHERE vt.PaymentMethodId = pm1.PaymentMethodId AND pm1.InsuranceTransfer = 1)

    AND b.entry >= ISNULL('07/01/2010','1/1/1900')

    AND b.entry < DATEADD(DAY,1,ISNULL('07/31/2010','1/1/3000')

    )

    AND --Filter on Facility

    (

    (NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR

    (NULL IS NULL)

    )

    AND --Filter on Company

    (

    (NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR

    (NULL IS NULL)

    )

    AND --Filter on Financial Class

    (

    (NULL IS NOT NULL AND pv.FinancialClassMID IN (NULL)) OR

    (NULL IS NULL)

    )

    GROUP BY

    pv.TicketNumber,

    b.Entry,

    t.Amount,

    pv.Visit,

    doc.Ledger,

    fac.Ledger,

    fin.Ledger,

    pymt.Ledger

    HAVING

    t.amount <> 0

    -- Insert Adjustments

    INSERT INTO #Summary

    SELECT

    pv.TicketNumber,

    pv.Visit,

    '01' AS EntityCode,

    RIGHT(YEAR(DATEADD(m , -( @otherlong - 1 ) , b.Entry)) , 2) AS [Fiscal Year],

    LEFT( CONVERT(VARCHAR, DATEADD(m , -( @otherlong - 1 ) , b.Entry), 101), 2) AS [Fiscal Period],

    'GL' AS [Subsystem],

    'JRNENT' AS [Source Code],

    LEFT(ISNULL(adj.Ledger , '') + SPACE(20) , 20) AS [DeptSubAccount],

    LEFT(CONVERT(varchar, t.amount) + SPACE(22), 22) AS [Amount],

    'D' AS [Debit/Credit],

    'Centricity Entry' + SPACE(34) AS [Description],

    SPACE(6) AS [Proj Id],

    SPACE(16) AS [Filler]

    FROM

    PatientVisit pv

    INNER JOIN DoctorFacility comp ON pv.CompanyId = comp.DoctorFacilityId

    INNER JOIN DoctorFacility fac ON pv.FacilityId = fac.DoctorFacilityId

    INNER JOIN DoctorFacility doc ON pv.DoctorId = doc.DoctorFacilityId

    LEFT OUTER JOIN Medlists fin ON pv.FinancialClassMId = fin.MedListsId

    INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId

    INNER JOIN VisitTransactions vt ON pv.PatientVisitId = vt.PatientVisitid

    INNER JOIN PaymentMethod pm ON vt.PaymentMethodId = pm.PaymentMethodId

    INNER JOIN Batch b ON pm.BatchID = b.BatchID

    INNER JOIN Transactions t ON vt.VisitTransactionsId = t.VisitTransactionsId

    LEFT OUTER JOIN MedLists at ON t.ActionTypeMId = at.MedListsId

    LEFT OUTER JOIN (select * from medlists where tablename = 'AdjustmentTypes') adj ON t.ActionTypeMId = adj.MedlistsId

    WHERE

    t.Action = 'A' AND b.Status <> 0

    AND NOT EXISTS (SELECT 1 FROM PaymentMethod pm1 WHERE vt.PaymentMethodId = pm1.PaymentMethodId AND pm1.InsuranceTransfer = 1)

    AND b.entry >= ISNULL('07/01/2010','1/1/1900')

    AND b.entry < DATEADD(DAY,1,ISNULL('07/31/2010','1/1/3000')

    )

    AND --Filter on Facility

    (

    (NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR

    (NULL IS NULL)

    )

    AND --Filter on Company

    (

    (NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR

    (NULL IS NULL)

    )

    AND --Filter on Financial Class

    (

    (NULL IS NOT NULL AND pv.FinancialClassMID IN (NULL)) OR

    (NULL IS NULL)

    )

    GROUP BY

    pv.TicketNumber,

    b.Entry,

    t.Amount,

    pv.Visit,

    doc.Ledger,

    fac.Ledger,

    fin.Ledger,

    adj.Ledger

    HAVING

    t.amount <> 0

    -- Now group the items together for a total

    SELECT

    [TicketNumber],

    [Visit],

    [EntityCode],

    [Fiscal Year],

    [Fiscal Period],

    [Subsystem],

    [Source Code],

    [DeptSubAccount],

    [Amount],

    [Debit/Credit],

    [Description],

    [Proj Id],

    [Filler]

    FROM

    #Summary

    ORDER BY

    [TicketNumber]

    DROP TABLE #Summary

  • Not sure that I exactly understand the question but take a look at this and see if it answers any part of your question ...

    DECLARE

    @n_TicketNoint,

    @vc_Type varchar(1), --debit/credit

    @vc_Entity varchar(10),

    @d_Amount decimal(10,2))

    --set variable values (not sure if this is a proc

    --or just a T-SQL statement)

    --original entry

    INSERT INTO dbo.demoTable (TicketNo, TranType, EntityCode, TranAmount)

    SELECT @n_TicketNo,

    @vc_Type,

    @vc_Entity,

    @d_Amount;

    --reactionary entry

    INSERT INTO dbo.demoTable (TicketNo, TranType, EntityCode, TranAmount)

    SELECT @n_TicketNo,

    CASE @vc_Type

    WHEN 'C' THEN 'D'

    WHEN 'D' THEN 'C' END,

    @vc_Entity,

    @d_Amount;

    I would be curious to know what is generating your T-SQL.

    └> bt



    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

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

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