- CREATE OR REPLACE VIEW vwstmACC AS
-
SELECT DISTINCT ON (stmnt.chargeservicelineid) stmnt.created, stmnt.createdby, stmnt.lastupdated, stmnt.updatedby, stmnt.accountid, stmnt.practiceid, stmnt.chargegroupid, stmnt.chargeservicelineid, stmnt.patientid, stmnt.paymentpostingid, stmnt.chargeadjustmentid, stmnt.locationid, stmnt.specialityid, stmnt.placeofserviceid, stmnt.billtopatientpayerid, stmnt.paymentid, stmnt.patientdisplayid, stmnt.dateofservice, stmnt.posteddate, stmnt.status, stmnt.voideddate, stmnt.originalclaimreferencenumber, stmnt.adjustmentreason, stmnt.adjdescription, stmnt.adjposteddate, stmnt.name, stmnt.patientaspayer, stmnt.patfirstname, stmnt.patmiddlename, stmnt.patlastname, stmnt.chartnum, stmnt.sex, stmnt.healthcardnumber, stmnt.dob, stmnt.units, stmnt.fee, stmnt.chargeamount, stmnt.allowedamount, stmnt.deductibleamount, stmnt.coinsuranceamount, stmnt.copaymentamount, stmnt.totalpayments, stmnt.totaladjustments, stmnt.balanceamount, stmnt.voidedchargelinkid, stmnt.roworder, stmnt.methodofpayment, stmnt.payment_posteddate, stmnt.claimnumber, stmnt.claimsubstatus, stmnt.onholdreason, stmnt.claimvoideddate, stmnt.servicecodename, stmnt.servicecodenamedesc, stmnt.locationname, stmnt.locationdescription, stmnt.payer, stmnt.payercode, stmnt.payerlastname, stmnt.payerfirstname
-
FROM ( SELECT DISTINCT ON (csl.chargeservicelineid, pAYpOSTT.paymentpostingid, tva.chargeadjustmentid) cg.created, cg.createdby, cg.lastupdated, cg.updatedby, cg.accountid, cg.practiceid, cg.chargegroupid, csl.chargeservicelineid, cg.patientid, pAYpOSTT.paymentpostingid, tva.chargeadjustmentid, cg.locationid, cg.specialityid, cg.placeofserviceid, cg.billtopatientpayerid, pAYpOSTT.paymentid, pat_info.patientdisplayid, csl.dateofservicestart AS dateofservice, cg.posteddate, cg.status, cg.voideddate, cg.originalclaimreferencenumber, tva.adjustmentreason, tva.description AS adjdescription, tva.posteddate AS adjposteddate, practice.name,
-
CASE
-
WHEN cg.patientaspayer = true THEN 'Patient'::text
-
ELSE '3RD PARTY'::text
-
END AS patientaspayer, pat_info.firstname AS patfirstname, pat_info.mi AS patmiddlename, pat_info.lastname AS patlastname, pat_info.chartnum, pat_info.sex, pat_info.healthcardnumber, pat_info.dob, csl.units, csl.fee, csl.chargeamount, csl.allowedamount, csl.deductibleamount, csl.coinsuranceamount, csl.copaymentamount, ( SELECT sum(p1.postedamount) AS sum
-
FROM pAYpOSTT p1
-
WHERE cg.accountid = p1.accountid AND cg.practiceid = p1.practiceid AND csl.chargeservicelineid = p1.chargeservicelineid AND p1.status::text = 'POSTED'::text) AS totalpayments, ( SELECT sum(a1.amount) AS sum
-
FROM vwadjustments a1
-
WHERE cg.accountid = a1.accountid AND cg.practiceid = a1.practiceid AND csl.chargeservicelineid = a1.chargeservicelineid) AS totaladjustments, csl.balanceamount, csl.voidedchargelinkid, csl.roworder, payments.methodofpayment, pAYpOSTT.posteddate AS payment_posteddate, claim.claimnumber, claim.substatus AS claimsubstatus, claim.onholdreason, claim.voideddate AS claimvoideddate, enterprise_servicecodes.codename AS servicecodename, enterprise_servicecodes.description AS servicecodenamedesc, pRACloc.locationname, pRACloc.description AS locationdescription, vwpatientpayers.payer, vwpatientpayers.payercode, vwpatientpayers.lastname AS payerlastname, vwpatientpayers.firstname AS payerfirstname
-
FROM charge_group cg
-
JOIN charge_service_line csl ON cg.accountid = csl.accountid AND cg.practiceid = csl.practiceid AND cg.chargegroupid = csl.chargegroupid AND cg.status::text = csl.status::text AND csl.substatus::text <> '2'::text AND cg.status::text = 'POSTED'::text
-
LEFT JOIN enterprise_servicecodes ON enterprise_servicecodes.entservicecodeid = csl.entservicecodeid
-
LEFT JOIN pAYpOSTT ON cg.accountid = pAYpOSTT.accountid AND cg.practiceid = pAYpOSTT.practiceid AND csl.chargeservicelineid = pAYpOSTT.chargeservicelineid AND pAYpOSTT.status::text = 'POSTED'::text
-
LEFT JOIN payments ON cg.accountid = payments.accountid AND cg.practiceid = payments.practiceid AND payments.paymentid = pAYpOSTT.paymentid AND payments.status::text = 'POSTED'::text
-
LEFT JOIN claim ON cg.accountid = claim.accountid AND cg.practiceid = claim.practiceid AND cg.chargegroupid = claim.chargegroupid
-
JOIN pRACloc ON cg.accountid = pRACloc.accountid AND cg.practiceid = pRACloc.practiceid AND cg.locationid = pRACloc.locationid
-
LEFT JOIN vwpatientpayers ON cg.accountid = vwpatientpayers.accountid AND cg.patientid = vwpatientpayers.patientid AND cg.billtopatientpayerid = vwpatientpayers.patientpayerid
-
JOIN pat_info ON cg.accountid = pat_info.accountid AND cg.patientid = pat_info.patientid
-
LEFT JOIN vwadjustments tva ON cg.accountid = tva.accountid AND cg.practiceid = tva.practiceid AND csl.chargeservicelineid = tva.chargeservicelineid
-
JOIN practice ON cg.accountid = practice.accountid AND cg.practiceid = practice.practiceid
-
WHERE (date(csl.posteddate) <> date(csl.voideddate) OR csl.voideddate IS NULL OR csl.substatus::text = '1'::text) AND cg.status::text = 'POSTED'::text
-
ORDER BY csl.chargeservicelineid, pAYpOSTT.paymentpostingid, tva.chargeadjustmentid, cg.chargegroupid, cg.dateofservice, csl.roworder) stmnt
-
ORDER BY stmnt.chargeservicelineid, stmnt.paymentpostingid, stmnt.chargeadjustmentid, stmnt.created DESC;
-
-
ALTER TABLE vwstmACC
-
OWNER TO postgr;
1 2170 Rabbit 12,516
Recognized Expert Moderator MVP
Your code is formatted terribly. It makes it very hard to diagnose so I had to reformat it just to be able to understand it.
There's lots of reasons your code is going to run slow. Too many to try to go over at this very moment. Instead, read our article on optimizing SQL: http://bytes.com/topic/sql-server/in...ze-sql-queries
It was written for SQL Server but many of the same concepts apply to all SQL engines. The syntax will be slightly different but equivalents exist in all the major database management systems.
After applying the concepts in that article, please post the updated SQL for further help. And for Pete's sake, please format the SQL so it is easier to read. - SELECT DISTINCT ON (stmnt.chargeservicelineid)
-
stmnt.created,
-
stmnt.createdby,
-
stmnt.lastupdated,
-
stmnt.updatedby,
-
stmnt.accountid,
-
stmnt.practiceid,
-
stmnt.chargegroupid,
-
stmnt.chargeservicelineid,
-
stmnt.patientid,
-
stmnt.paymentpostingid,
-
stmnt.chargeadjustmentid,
-
stmnt.locationid,
-
stmnt.specialityid,
-
stmnt.placeofserviceid,
-
stmnt.billtopatientpayerid,
-
stmnt.paymentid,
-
stmnt.patientdisplayid,
-
stmnt.dateofservice,
-
stmnt.posteddate,
-
stmnt.status,
-
stmnt.voideddate,
-
stmnt.originalclaimreferencenumber,
-
stmnt.adjustmentreason,
-
stmnt.adjdescription,
-
stmnt.adjposteddate,
-
stmnt.name,
-
stmnt.patientaspayer,
-
stmnt.patfirstname,
-
stmnt.patmiddlename,
-
stmnt.patlastname,
-
stmnt.chartnum,
-
stmnt.sex,
-
stmnt.healthcardnumber,
-
stmnt.dob,
-
stmnt.units,
-
stmnt.fee,
-
stmnt.chargeamount,
-
stmnt.allowedamount,
-
stmnt.deductibleamount,
-
stmnt.coinsuranceamount,
-
stmnt.copaymentamount,
-
stmnt.totalpayments,
-
stmnt.totaladjustments,
-
stmnt.balanceamount,
-
stmnt.voidedchargelinkid,
-
stmnt.roworder,
-
stmnt.methodofpayment,
-
stmnt.payment_posteddate,
-
stmnt.claimnumber,
-
stmnt.claimsubstatus,
-
stmnt.onholdreason,
-
stmnt.claimvoideddate,
-
stmnt.servicecodename,
-
stmnt.servicecodenamedesc,
-
stmnt.locationname,
-
stmnt.locationdescription,
-
stmnt.payer,
-
stmnt.payercode,
-
stmnt.payerlastname,
-
stmnt.payerfirstname
-
-
FROM
-
(
-
SELECT DISTINCT ON (csl.chargeservicelineid, pAYpOSTT.paymentpostingid, tva.chargeadjustmentid)
-
cg.created,
-
cg.createdby,
-
cg.lastupdated,
-
cg.updatedby,
-
cg.accountid,
-
cg.practiceid,
-
cg.chargegroupid,
-
csl.chargeservicelineid,
-
cg.patientid,
-
pAYpOSTT.paymentpostingid,
-
tva.chargeadjustmentid,
-
cg.locationid,
-
cg.specialityid,
-
cg.placeofserviceid,
-
cg.billtopatientpayerid,
-
pAYpOSTT.paymentid,
-
pat_info.patientdisplayid,
-
csl.dateofservicestart AS dateofservice,
-
cg.posteddate,
-
cg.status,
-
cg.voideddate,
-
cg.originalclaimreferencenumber,
-
tva.adjustmentreason,
-
tva.description AS adjdescription,
-
tva.posteddate AS adjposteddate,
-
practice.name,
-
-
CASE
-
WHEN cg.patientaspayer = true THEN 'Patient'::text
-
ELSE '3RD PARTY'::text
-
END AS patientaspayer,
-
-
pat_info.firstname AS patfirstname,
-
pat_info.mi AS patmiddlename,
-
pat_info.lastname AS patlastname,
-
pat_info.chartnum,
-
pat_info.sex,
-
pat_info.healthcardnumber,
-
pat_info.dob,
-
csl.units,
-
csl.fee,
-
csl.chargeamount,
-
csl.allowedamount,
-
csl.deductibleamount,
-
csl.coinsuranceamount,
-
csl.copaymentamount,
-
-
(
-
SELECT sum(p1.postedamount) AS sum
-
FROM pAYpOSTT p1
-
WHERE
-
cg.accountid = p1.accountid AND
-
cg.practiceid = p1.practiceid AND
-
csl.chargeservicelineid = p1.chargeservicelineid AND
-
p1.status::text = 'POSTED'::text
-
) AS totalpayments,
-
-
(
-
SELECT sum(a1.amount) AS sum
-
FROM vwadjustments a1
-
WHERE
-
cg.accountid = a1.accountid AND
-
cg.practiceid = a1.practiceid AND
-
csl.chargeservicelineid = a1.chargeservicelineid
-
) AS totaladjustments,
-
-
csl.balanceamount,
-
csl.voidedchargelinkid,
-
csl.roworder,
-
payments.methodofpayment,
-
pAYpOSTT.posteddate AS payment_posteddate,
-
claim.claimnumber,
-
claim.substatus AS claimsubstatus,
-
claim.onholdreason,
-
claim.voideddate AS claimvoideddate,
-
enterprise_servicecodes.codename AS servicecodename,
-
enterprise_servicecodes.description AS servicecodenamedesc,
-
pRACloc.locationname,
-
pRACloc.description AS locationdescription,
-
vwpatientpayers.payer,
-
vwpatientpayers.payercode,
-
vwpatientpayers.lastname AS payerlastname,
-
vwpatientpayers.firstname AS payerfirstname
-
-
FROM
-
charge_group cg
-
-
JOIN charge_service_line csl ON
-
cg.accountid = csl.accountid AND
-
cg.practiceid = csl.practiceid AND
-
cg.chargegroupid = csl.chargegroupid AND
-
cg.status::text = csl.status::text AND
-
csl.substatus::text <> '2'::text AND
-
cg.status::text = 'POSTED'::text
-
-
LEFT JOIN enterprise_servicecodes ON
-
enterprise_servicecodes.entservicecodeid = csl.entservicecodeid
-
-
LEFT JOIN pAYpOSTT ON
-
cg.accountid = pAYpOSTT.accountid AND
-
cg.practiceid = pAYpOSTT.practiceid AND
-
csl.chargeservicelineid = pAYpOSTT.chargeservicelineid AND
-
pAYpOSTT.status::text = 'POSTED'::text
-
-
LEFT JOIN payments ON
-
cg.accountid = payments.accountid AND
-
cg.practiceid = payments.practiceid AND
-
payments.paymentid = pAYpOSTT.paymentid AND
-
payments.status::text = 'POSTED'::text
-
-
LEFT JOIN claim ON
-
cg.accountid = claim.accountid AND
-
cg.practiceid = claim.practiceid AND
-
cg.chargegroupid = claim.chargegroupid
-
-
JOIN pRACloc ON
-
cg.accountid = pRACloc.accountid AND
-
cg.practiceid = pRACloc.practiceid AND
-
cg.locationid = pRACloc.locationid
-
-
LEFT JOIN vwpatientpayers ON
-
cg.accountid = vwpatientpayers.accountid AND
-
cg.patientid = vwpatientpayers.patientid AND
-
cg.billtopatientpayerid = vwpatientpayers.patientpayerid
-
-
JOIN pat_info ON
-
cg.accountid = pat_info.accountid AND
-
cg.patientid = pat_info.patientid
-
-
LEFT JOIN vwadjustments tva ON
-
cg.accountid = tva.accountid AND
-
cg.practiceid = tva.practiceid AND
-
csl.chargeservicelineid = tva.chargeservicelineid
-
-
JOIN practice ON
-
cg.accountid = practice.accountid AND
-
cg.practiceid = practice.practiceid
-
-
WHERE
-
(
-
date(csl.posteddate) <> date(csl.voideddate) OR
-
csl.voideddate IS NULL OR
-
csl.substatus::text = '1'::text
-
) AND
-
cg.status::text = 'POSTED'::text
-
-
ORDER BY
-
csl.chargeservicelineid,
-
pAYpOSTT.paymentpostingid,
-
tva.chargeadjustmentid,
-
cg.chargegroupid,
-
cg.dateofservice,
-
csl.roworder
-
) stmnt
-
-
ORDER BY
-
stmnt.chargeservicelineid,
-
stmnt.paymentpostingid,
-
stmnt.chargeadjustmentid,
-
stmnt.created DESC;
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Neil |
last post by:
I've been discussing here a SQL 7 view which scrolls slowly when linked to
an Access 2000 MDB. After trying various things, I've distilled it down to
the following: when the linked view has a virtual primary key, it is slow;
when there is no virtual primary key, it scrolls quickly.
Looking at Profiler, I see that when there is no virtual primary key, Access
sends a simple select command to SQL Server. However, when there is a
virtual...
|
by: Neil |
last post by:
I previously posted re. this, but thought I'd try again with a summary of
facts.
I have an Access 2000 MDB with a SQL Server 7 back end. There is a view that
is linked to the database via ODBC and has been in place for several years
without any performance problems.
Recently I added a couple of fields to the output of the view, and it became
very slow when scrolling. When just opened in the database window, the
linked view takes about...
|
by: Steve K |
last post by:
Hi,
I'm on a Mac, OSX version 10.3.5, 1.25GB SDRAM, 400 MHz PowerPC G4
I have DreamweaverMX 2004, version 7.0.1.
It is painfully slow!! It's very slow working in design view, and almost
just as slow working in code view. I'm always in split view so I can see
what I've done in the code. So I click into the design view and it takes
almost 5-8 seconds for the changes I just made in the code to "happen."
|
by: AnandaSim |
last post by:
Hi All,
I've had Access 97, 2000 connections to the corporate Oracle database
for a few years now - but seldom use it. When I did use it years ago,
performance was not fast but the features were good enough. Now, the
version of Access is 2003 with Office SP1 applied, Windows XP against
Oracle 9.2. And attached tables are real slow - 5 mins to get to
datasheet view and data is incorrect - the same row is repeated several
times even in...
|
by: VM |
last post by:
I have a 35000 line datagrid and updating one field in all 35000 rows takes
an eternity. How come? Since I don't have the datasource available (it may
be a table or view), in my example I do it like this:
for (int i=0;i<34000;i++) //the first 2000 records took 2 mins.
{
dataGrid_auditAddress = "MARKED";
}
Any help would be appreciated.
| |
by: diffuser78 |
last post by:
I have just started to learn python. Some said that its slow. Can
somebody pin point the issue.
Thans
|
by: Sham |
last post by:
I am trying to perform the following query on a table that has been
indexed using Full Text Search.
The table contains multiple columns than have been indexed. (Below,
all xml columns are indexed).
dbo.maintable(ProfileID int pk)
dbo.fts_table(ProfileID int pk fk, col1 xml, col2 xml, col3 xml)
I want to perform a query that will return any rows that contain ‘x’
and ‘y’ in any columns. I.e. ‘x’ could be in col1 and ‘y’ could be in
|
by: existential.philosophy |
last post by:
This is a new problem for me: I have some queries that open very
slowly in design view.
My benchmark query takes about 20 minutes to open in design view. That
same query takes about 20 minutes to open in datasheet view. As an
experiment, I deleted all rows in all tables; after that, the query
took only seconds to open in both design view and datasheet view. From
these facts, I conclude that Access is evaluating the query when I go
to...
|
by: muradjuber |
last post by:
hello all
i have view on live database take big time
and i dont have other ideas to solve it
it consist of two tables
and they are joined right
and i make analyze the two tables and it not worked
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |