July 10, 2014 at 2:06 am
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
July 17, 2014 at 11:23 pm
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
July 18, 2014 at 6:11 am
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
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
July 21, 2014 at 12:25 am
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