Complex query - converting Excel to SQL Server query - would prefer a view

  • Hi,

    I've got an Excel "application" that I'm converting to Access front end (FE)/SQL Server back end (BE). The "main" Excel worksheet "tracks the money" and is a complex "query" which I need to try to convert into a view. Preferably, the view would be editable via the Access FE.

    The source tables:

    CLAIMS:

    CaseNumber [int]

    Applicable [bit] (Y/N = 1/0)

    Status [varchar] (dropdown list in FE, values are "Letter Sent" and "Letter Replied")

    Amount [money]

    PAYMENTS:

    CaseNumber [int]

    PaymentNumber [tinyint] (allows for partial payments)

    Amount [money]

    WRITEOFFS (this is the only table that would be edited via the view - if that's possible):

    CaseNumber [int]

    Agreed [money] (manual entry, always less than Applicable, see below)

    WriteOffReason [varchar] (dropdown list in the FE, needs to be editable, used for reporting metrics)

    The rows in the Excel Money worksheet is a one-liner rollup by CaseNumber.

    The columns in the Excel Money worksheet are (I'll express these as SQL; let me know if the Excel formula would help. FWIW, the Excel formulas are using SUMIFS, which SUMS over a given criteria):

    Total: sum(claims.amount) group by casenumber, i.e. all claim lines for this case number.

    Unprocessed: sum(claims.amount) where Applicable is NULL and Status is NULL group by casenumber. "New" claims source from our CRM system.

    SentOut: sum(claims.amount) where Status is NOT NULL group by casenumber

    NotApplicable: sum(claims.amount) where Applicable = 0 group by casenumber

    Applicable: sum(claims.amount) where Applicable = 1 group by casenumber

    Agreed: a manual entry into WRITEOFFS based on say negotiation with a third party

    WriteOff: a calculated field: case IsNull(writeoffs.Agreed,0) > 0 then IsNull(claims.Applicable,0) - IsNull(writeoffs.Agreed,0) else 0

    Paid: sum(payments.amount) group by casenumber

    Outstanding: a calculated field: min(applicable,agreed) - paid

    My questions:

    1) Can CTE's be used in a view?

    2) Do they perform better than sub-queries, or do they just make the syntax easier and more elegant?

    3) Is it possible to do the rollups on the claims and payments tables as above, join that with the writeoff table, and have that view still be editable for the writeoff columns?

    I hope I've explained this clearly. I don't expect working code (actually, I don't *expect* anything at all - bad word choice!), but pointers as to the best approach would be greatly appreciated.

    Let me know if I need to edit the message to make this clearer.

    Kind Regards,

    Scott

  • I've made progress on this issue, and have a view which works, except 1) I'd prefer it was editable (for the WriteOff table's columns), and 2) it would be good if it performed better.

    drop view vMoney

    go

    create view vMoney as

    with

    total as (

    select caseid, sum(benefitpaid) as total from dbo.fact_claims group by caseid

    ),

    unprocessed as (

    select caseid, sum(benefitpaid) as unprocessed from dbo.fact_claims where unprocessed=1 group by caseid

    ),

    sentout as (

    select caseid, sum(benefitpaid) as sentout from dbo.fact_claims where statusid=2 group by caseid

    ),

    notapplicable as (

    select caseid, sum(benefitpaid) as notapplicable from dbo.fact_claims where applicable=0 group by caseid

    ),

    applicable as (

    select caseid, sum(benefitpaid) as applicable from dbo.fact_claims where applicable=1 group by caseid

    ),

    paid as (

    select caseid, sum(total) as paid from dbo.fact_payments group by caseid

    )

    select c.caseid

    ,c.casenumber

    ,c.analyst

    ,c.covno

    ,c.suffix

    ,c.lastname

    ,c.firstname

    ,IsNull(total,0) as Total

    ,IsNull(unprocessed,0) as Unprocessed

    ,IsNull(sentout,0) as SentOut

    ,IsNull(notapplicable,0) as NotApplicable

    ,IsNull(applicable,0) as Applicable

    ,IsNull(agreed,0) as Agreed

    ,case when Agreed > 0 then Applicable-Agreed else 0 end as WriteOff

    ,WriteOffReason

    ,IsNull(paid,0) as Paid

    ,Coalesce(Agreed,Applicable) - Paid as Outstanding

    ,SettlementDate

    ,Notes

    from vFact_Cases c

    left join total t

    on c.caseid=t.caseid

    left join unprocessed u

    on c.caseid=u.caseid

    left join sentout s

    on c.caseid=s.caseid

    left join notapplicable n

    on c.caseid=n.caseid

    left join applicable a

    on c.caseid=a.caseid

    left join paid p

    on c.caseid=p.caseid

    left join dbo.fact_writeoffs w

    on c.caseid=w.caseid

    left join dbo.dim_WriteOffReason wr

    on w.WriteOffReasonID=wr.WriteOffReasonID

    go

    select * from vMoney

    I've indexed all the relevant columns, but it still takes a while to run given the current data volumes.

    If there is a way this view could be editable (can triggers be used to update the WriteOff table?), and perform better (Window Function/PARTITION (OVER...) ???), please point me in the right direction.

    Regards,

    Scott

  • Try this for a performance lift:

    ;WITH Aggregates AS (

    SELECT

    caseid,

    [total]= SUM(benefitpaid),

    [unprocessed]= SUM(CASE WHEN unprocessed = 1 THEN benefitpaid ELSE 0 END),

    [sentout]= SUM(CASE WHEN statusid = 2 THEN benefitpaid ELSE 0 END),

    [notapplicable] = SUM(CASE WHEN applicable = 0 THEN benefitpaid ELSE 0 END),

    [applicable]= SUM(CASE WHEN applicable = 1 THEN benefitpaid ELSE 0 END),

    [paid]= SUM(total)

    FROM dbo.fact_claims

    GROUP BY caseid

    )

    SELECT

    c.caseid,

    c.casenumber,

    c.analyst,

    c.covno,

    c.suffix,

    c.lastname,

    c.firstname,

    Total= ISNULL(total,0),

    Unprocessed = ISNULL(unprocessed,0),

    SentOut= ISNULL(sentout,0),

    NotApplicable = ISNULL(notapplicable,0),

    Applicable= ISNULL(applicable,0),

    Agreed= ISNULL(agreed,0),

    WriteOff= CASE WHEN Agreed > 0 THEN Applicable-Agreed ELSE 0 END,

    WriteOffReason,

    Paid= IsNull(paid,0),

    Outstanding = Coalesce(Agreed,Applicable) - Paid,

    SettlementDate,

    Notes

    FROM vFact_Cases c

    LEFT JOIN Aggregates t

    ON t.caseid = c.caseid

    LEFT JOIN dbo.fact_writeoffs w

    ON w.caseid = c.caseid

    LEFT JOIN dbo.dim_WriteOffReason wr

    ON wr.WriteOffReasonID = w.WriteOffReasonID

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Thanks that certainly helped performance - down from 40 seconds to 1 second!. And now that I see the code, it makes sense, reducing the passes over the data.

    Payments is actually a second table. Plus, I was getting a warning message "Warning: Null value is eliminated by an aggregate or other SET operation." So, for completeness, I'll include my final version of the view at the end of this message.

    So, the query is 1) A rollup of claims across various filtering criteria (by caseid), 2) a rollup of payments (by caseid), 3) a join of those two rollups with a 3rd WriteOffs table (one record per caseid), plus a couple calculated fields.

    It is handy for the end users to see the one-line rollup across caseid for the three tables, but be able to edit the WriteOff table from within that view. However, from SSMS, when I select "Edit Top 200 rows", it doesn't allow edits due to the structure of the join.

    Is it possible to use INSTEAD OF triggers to do an update of the WriteOffs table? In pseudocode:

    * User updates WriteOff (amount) or WriteOffReason fields in the view (I'll make the other columns readonly in the Access front-end)

    * INSTEAD OF trigger runs to update the fields in the WriteOffs table

    * Suppress any errors due to the view structure (normally it's not editable). Is this automatic behaviour in an INSTEAD OF trigger?

    * Refresh the view (which will retrieve the saved data from WriteOffs). Since the view is now so fast, this will have acceptable performance. I can trigger the view refresh from the Access FE, or it may be automatic due to a timestamp field in the WriteOffs table. I can test this and see later once I write the FE.

    I can probably write the trigger code (Google is my friend), so I'm just wanting to know if this is conceptually possible before I run down a rabbit hole, and if this is the best approach for this issue.

    Here's the final view code:

    DROP VIEW dbo.vMoney

    GO

    CREATE VIEW dbo.vMoney AS

    WITH

    Claims_Aggregates AS (

    SELECT

    caseid,

    [total] = SUM(IsNull(benefitpaid,0)),

    [unprocessed] = SUM(CASE WHEN unprocessed = 1 THEN IsNull(benefitpaid,0) ELSE 0 END),

    [sentout] = SUM(CASE WHEN statusid = 2 THEN IsNull(benefitpaid,0) ELSE 0 END),

    [notapplicable] = SUM(CASE WHEN applicable = 0 THEN IsNull(benefitpaid,0) ELSE 0 END),

    [applicable] = SUM(CASE WHEN applicable = 1 THEN IsNull(benefitpaid,0) ELSE 0 END)

    FROM dbo.fact_claims

    GROUP BY caseid

    ),

    Payments_Aggregates AS (

    SELECT

    caseid,

    [paid] = SUM(IsNull(Total,0))

    FROM dbo.fact_payments

    GROUP BY caseid

    )

    SELECT

    c.caseid,

    c.casenumber,

    c.analyst,

    c.covno,

    c.suffix,

    c.lastname,

    c.firstname,

    Total,

    Unprocessed,

    SentOut,

    NotApplicable,

    Applicable,

    Agreed,

    WriteOff = CASE WHEN IsNull(Agreed,0) > 0 THEN Applicable-Agreed ELSE 0 END,

    WriteOffReason,

    Paid,

    Outstanding = Coalesce(Agreed,Applicable) - Paid,

    SettlementDate,

    Notes

    FROM vFact_Cases c

    LEFT JOIN Claims_Aggregates t

    ON t.caseid = c.caseid

    LEFT JOIN Payments_Aggregates p

    ON t.caseid = p.caseid

    LEFT JOIN dbo.fact_writeoffs w

    ON w.caseid = c.caseid

    LEFT JOIN dbo.dim_WriteOffReason wr

    ON wr.WriteOffReasonID = w.WriteOffReasonID

    GO

    SELECT * FROM dbo.vMoney

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

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