ASP. NET vs. PL/SQL

Hey guys, I am trying to rewrite an application with an Active Server Pages front-end that calls Oracle WebDB v2.2 reports. The data is all stored in an Oracle 9i database. What are some of the benefits and drawbacks of redesigning the application using an ASP .NET or PL/SQL
implementation. The redesigned application should still retrieve the data from the Oracle 9i database. Any help with this is greatly appreciated.When it comes to database development PL/SQL has the advantage since it is very flexible with Oracle and it seperates the business logic from the interface. But it lacks presentation in a nice way.

ASP.NET on the other hand can be developed to display data in very nice way. Performance wise PL/SQL would be better since it directly runs in the database server.

If the presentation of the report is not an issue then go for PL/SQL, or else you can go for ASP.NET

But remember to eveluate your skill level also on both and decide on one.

Asking userid and password while deploying reports

Hi All,

While deploying reports on the reports server, it is asking to supply user name and password. When I entered the same, it didn't accept them. What do I need to do? Is there any specific setting which I need to configure?

An immediate reply will be highly appreciated.

Thanks

Sekhar

check out the steps outlined here:

http://msdn2.microsoft.com/en-us/library/ms155802.aspx

and make sure that permissions have been set properly for the user

http://msdn2.microsoft.com/en-us/library/ms156014.aspx

|||To deploy the reports, you need to have specific permissions. the username and password you are giving may not belongs to administrator group. Just give username and password of the administrator of the deployment server.

Asking the user a question

Hello all,

I have a client with an application that they have purchased, it
stores its data on SQL server 2000 and so I am able to do reports and
some small VB apps that use the data. I can't however make any
significant changes to the app itself, just what it allows you to
configure.

The system records jobs, when a job is closed the client want a prompt
to pop-up asking if they want to record that the job is closed in a
special log file. I suggested that we use a checkbox (We can add
custom fields to the app) to record this, then I could write a trigger
that checks that field and adds it to the log file if it's 1, but no
they want a yes/no message box type thing popping up.

Is it possible to display (on the users screen) and get input back
from a yes/no message box in a SQL Server trigger? As far as I can
tell it isn't but it would be great if I were wrong.

Any help/suggestions greatly appreciated!!!

Thanks,

Bob.SQL Server can't invoke anything in your UI. That's something you have to do
in client-side code.

--
David Portas
SQL Server MVP
--|||Robert Hogan (busrhogan@.hotmail.com) writes:
> Is it possible to display (on the users screen) and get input back
> from a yes/no message box in a SQL Server trigger? As far as I can
> tell it isn't but it would be great if I were wrong.

In theory, yes. But for crying out loud, don't do it!

You could use sp_OAmethod and friends to run a remote OLE object
that would wait for the reponse.

But while waiting for user input - which could take several hours or
days - you are in the middle of a transaction, data will be uncommitted
and locks will be held. Could have sever impact on the situation.

A variant would be to have the OLE thing asynchronous, so that the
trigger does not wait for input, and the OLE thing would then update
the database itself.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
"Robert Hogan" <busrhogan@.hotmail.com> wrote in message
news:eb62f112.0501171614.cb68c36@.posting.google.co m...
> Hello all,
> I have a client with an application that they have purchased, it
> stores its data on SQL server 2000 and so I am able to do reports and
> some small VB apps that use the data. I can't however make any
> significant changes to the app itself, just what it allows you to
> configure.
> The system records jobs, when a job is closed the client want a prompt
> to pop-up asking if they want to record that the job is closed in a
> special log file.

Well...
This is the problem with buying a package.
They do what the people wrote them guessed you wanted.
How close that is to what you do depends on how good he guessed.

> I suggested that we use a checkbox (We can add
> custom fields to the app) to record this, then I could write a trigger
> that checks that field and adds it to the log file if it's 1, but no
> they want a yes/no message box type thing popping up.

No chance.
You/They have 4 practical options:

1) Bung the field on and use a trigger to write the stuff as you suggest.

2) Contact the people wrote the package and pay them to make a custom
change.
If they're happy to do this then remember upgrades and expect to pay
through the nose for that confirmation box.

3) Re-write the package from scratch.
Probably financially unattractive unless they have a lot of reports and
customisation in mind.

4) Work out what that screen does and write one yourself that replaces it.
Some packages allow you to have custom screens, I'd guess maybe this
isn't one so maybe not so practical.
If that one screen and your other additions is all whoever closes jobs
uses then maybe it is a go-er.
> Is it possible to display (on the users screen) and get input back
> from a yes/no message box in a SQL Server trigger? As far as I can
> tell it isn't but it would be great if I were wrong.
> Any help/suggestions greatly appreciated!!!

Next time they buy a package..
Remember the hidden costs.
It's can be far cheaper to go for a bespoke app in the long run.

> Thanks,
> Bob.

--
Regards,
Andy O'Neillsql

ASAP Help needed Need Sql Guru to help with massive script issue

I need some help I have this massive sql script the problem is I tried to put it in to the query string box in my sql reports and it will not take it this script will run if I break it up but I think it is to large is there a sql guru out there that can show me how to reduce the size of this script maybe by using an out parameter to a stored proceedure. I just dont know what to do and need to produce the report from this script. Below is the entire script

SELECT
'Prior Year All ' as 'qtr',
COUNT(JOB.JOBID) AS 'transcount',
COUNT(DISTINCT JOB.PATIENTID) AS 'patientcount',
SUM(JOB.TRANSPORTATION_TCOST) AS 'tcost',
SUM(JOB.TRANSPORTATION_DISC_COST) AS 'dtcost',
AVG(JOB.TRANSPORTATION_DISC) AS 'avgTDisc',
SUM(JOB.TRANSPORTATION_TCOST) + SUM(JOB.TRANSPORTATION_DISC_COST) AS 'TGrossAmtBilled',
SUM(JOB.TRANSPORTATION_TCOST) / COUNT(DISTINCT JOB.PATIENTID) AS 'PatAvgT',
SUM(JOB.TRANSPORTATION_DISC) AS 'avgPercentDiscT',
SUM(JOB.TRANSPORTATION_TCOST) / COUNT(JOB.JOBID) AS 'RefAvgT',
JOB.JURISDICTION,
PAYER.PAY_GROUPNAME,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.PATIENTID,
JOB.INVOICE_DATE,
JOB.JOBOUTCOMEID,
JOB.SERVICEOUTCOME,
INVOICE_AR.INVOICE_NO,
INVOICE_AR.INVOICE_DATE AS Expr1,
INVOICE_AR.AMOUNT_DUE,
INVOICE_AR.CLAIMNUMBER,
PATIENT.LASTNAME,
PATIENT.FIRSTNAME,
PATIENT.EMPLOYERNAME,
JOB_OUTCOME.DESCRIPTION,
SERVICE_TYPE.DESCRIPTION,
PAT_SERVICES_HISTORY.TRANSPORT_TYPE,

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed Successfully') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@.startate) and DATEADD(year,0,@.endate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedSuccessfullyItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with complaint') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@.startate) and DATEADD(year,0,@.endate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithComplaintItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with No Show') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@.startate) and DATEADD(year,0,@.endate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithNoShowItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with No Charge') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@.startate) and DATEADD(year,0,@.endate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithNoChargeItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with Situation') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@.startate) and DATEADD(year,0,@.endate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithSituationItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Not Completed') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@.startate) and DATEADD(year,0,@.endate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'NotCompletedItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Cancelled Prior to service') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@.startate) and DATEADD(year,0,@.endate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CancelledPriorToServiceItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Cancelled During Service') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@.startate) and DATEADD(year,0,@.endate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CancelledDuringServiceItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed Successfully') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@.startate) and DATEADD(year,0,@.endate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'AwaitingforcompletionItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Pending for review') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@.startate) and DATEADD(year,0,@.endate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like'%T ')) AS 'PendingforreviewItems'

FROM JOB
INNER JOIN INVOICE_AR
ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER
ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES
ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT
ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME
ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE
ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY
ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID

WHERE
(INVOICE_AR.AMOUNT_DUE > 0)
AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@.startate) and DATEADD(year,0,@.endate))
AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12))
AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%')
AND
(INVOICE_AR.INVOICE_NO like '%T')

GROUP BY
JOB.JURISDICTION,
PAYER.PAY_GROUPNAME,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.PATIENTID,
JOB.INVOICE_DATE,
JOB.JOBOUTCOMEID,
JOB.SERVICEOUTCOME,
INVOICE_AR.INVOICE_NO,
INVOICE_AR.INVOICE_DATE,
INVOICE_AR.AMOUNT_DUE,
INVOICE_AR.CLAIMNUMBER,
PATIENT.LASTNAME,
PATIENT.FIRSTNAME,
PATIENT.EMPLOYERNAME,
JOB_OUTCOME.DESCRIPTION,
SERVICE_TYPE.DESCRIPTION,
PAT_SERVICES_HISTORY.TRANSPORT_TYPE

UNION ALL

SELECT
'Current Year 2007 All ' as 'qtr',
COUNT(JOB.JOBID) AS 'transcount',
COUNT(DISTINCT JOB.PATIENTID) AS 'patientcount',
SUM(JOB.TRANSPORTATION_TCOST) AS 'tcost',
SUM(JOB.TRANSPORTATION_DISC_COST) AS 'dtcost',
AVG(JOB.TRANSPORTATION_DISC) AS 'avgTDisc',
SUM(JOB.TRANSPORTATION_TCOST) + SUM(JOB.TRANSPORTATION_DISC_COST) AS 'TGrossAmtBilled',
SUM(JOB.TRANSPORTATION_TCOST) / COUNT(DISTINCT JOB.PATIENTID) AS 'PatAvgT',
SUM(JOB.TRANSPORTATION_DISC) AS 'avgPercentDiscT',
SUM(JOB.TRANSPORTATION_TCOST) / COUNT(JOB.JOBID) AS 'RefAvgT',
JOB.JURISDICTION,
PAYER.PAY_GROUPNAME,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.PATIENTID,
JOB.INVOICE_DATE,
JOB.JOBOUTCOMEID,
JOB.SERVICEOUTCOME,
INVOICE_AR.INVOICE_NO,
INVOICE_AR.INVOICE_DATE AS Expr1,
INVOICE_AR.AMOUNT_DUE,
INVOICE_AR.CLAIMNUMBER,
PATIENT.LASTNAME,
PATIENT.FIRSTNAME,
PATIENT.EMPLOYERNAME,
JOB_OUTCOME.DESCRIPTION,
SERVICE_TYPE.DESCRIPTION,
PAT_SERVICES_HISTORY.TRANSPORT_TYPE,

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed Successfully') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startDate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedSuccessfullyItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with complaint') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithComplaintItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with No Show') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithNoShowItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with No Charge') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithNoChargeItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with Situation') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithSituationItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Not Completed') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'NotCompletedItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Cancelled Prior to service') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CancelledPriorToServiceItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Cancelled During Service') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CancelledDuringServiceItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed Successfully') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'AwaitingforcompletionItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Pending for review') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like'%T ')) AS 'PendingforreviewItems'

FROM JOB
INNER JOIN INVOICE_AR
ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER
ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES
ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT
ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME
ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE
ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY
ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID

WHERE
(INVOICE_AR.AMOUNT_DUE > 0)
AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate))
AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12))
AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%')
AND
(INVOICE_AR.INVOICE_NO like '%T')

GROUP BY
JOB.JURISDICTION,
PAYER.PAY_GROUPNAME,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.PATIENTID,
JOB.INVOICE_DATE,
JOB.JOBOUTCOMEID,
JOB.SERVICEOUTCOME,
INVOICE_AR.INVOICE_NO,
INVOICE_AR.INVOICE_DATE,
INVOICE_AR.AMOUNT_DUE,
INVOICE_AR.CLAIMNUMBER,
PATIENT.LASTNAME,
PATIENT.FIRSTNAME,
PATIENT.EMPLOYERNAME,
JOB_OUTCOME.DESCRIPTION,
SERVICE_TYPE.DESCRIPTION,
PAT_SERVICES_HISTORY.TRANSPORT_TYPE

UNION ALL

SELECT
'2007 Quarter 1 ' as 'qtr',
COUNT(JOB.JOBID) AS 'transcount',
COUNT(DISTINCT JOB.PATIENTID) AS 'patientcount',
SUM(JOB.TRANSPORTATION_TCOST) AS 'tcost',
SUM(JOB.TRANSPORTATION_DISC_COST) AS 'dtcost',
AVG(JOB.TRANSPORTATION_DISC) AS 'avgTDisc',
SUM(JOB.TRANSPORTATION_TCOST) + SUM(JOB.TRANSPORTATION_DISC_COST) AS 'TGrossAmtBilled',
SUM(JOB.TRANSPORTATION_TCOST) / COUNT(DISTINCT JOB.PATIENTID) AS 'PatAvgT',
SUM(JOB.TRANSPORTATION_DISC) AS 'avgPercentDiscT',
SUM(JOB.TRANSPORTATION_TCOST) / COUNT(JOB.JOBID) AS 'RefAvgT',
JOB.JURISDICTION,
PAYER.PAY_GROUPNAME,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.PATIENTID,
JOB.INVOICE_DATE,
JOB.JOBOUTCOMEID,
JOB.SERVICEOUTCOME,
INVOICE_AR.INVOICE_NO,
INVOICE_AR.INVOICE_DATE AS Expr1,
INVOICE_AR.AMOUNT_DUE,
INVOICE_AR.CLAIMNUMBER,
PATIENT.LASTNAME,
PATIENT.FIRSTNAME,
PATIENT.EMPLOYERNAME,
JOB_OUTCOME.DESCRIPTION,
SERVICE_TYPE.DESCRIPTION,
PAT_SERVICES_HISTORY.TRANSPORT_TYPE,

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed Successfully') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startDate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedSuccessfullyItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with complaint') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithComplaintItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with No Show') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithNoShowItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with No Charge') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithNoChargeItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with Situation') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithSituationItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Not Completed') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'NotCompletedItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Cancelled Prior to service') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CancelledPriorToServiceItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Cancelled During Service') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CancelledDuringServiceItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed Successfully') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'AwaitingforcompletionItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Pending for review') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like'%T ')) AS 'PendingforreviewItems'

FROM JOB
INNER JOIN INVOICE_AR
ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER
ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES
ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT
ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME
ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE
ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY
ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID

WHERE
(INVOICE_AR.AMOUNT_DUE > 0)
AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate))
AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3))
AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%')
AND
(INVOICE_AR.INVOICE_NO like '%T')

GROUP BY
JOB.JURISDICTION,
PAYER.PAY_GROUPNAME,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.PATIENTID,
JOB.INVOICE_DATE,
JOB.JOBOUTCOMEID,
JOB.SERVICEOUTCOME,
INVOICE_AR.INVOICE_NO,
INVOICE_AR.INVOICE_DATE,
INVOICE_AR.AMOUNT_DUE,
INVOICE_AR.CLAIMNUMBER,
PATIENT.LASTNAME,
PATIENT.FIRSTNAME,
PATIENT.EMPLOYERNAME,
JOB_OUTCOME.DESCRIPTION,
SERVICE_TYPE.DESCRIPTION,
PAT_SERVICES_HISTORY.TRANSPORT_TYPE

UNION ALL

SELECT
'2007 Quarter 2 ' as 'qtr',
COUNT(JOB.JOBID) AS 'transcount',
COUNT(DISTINCT JOB.PATIENTID) AS 'patientcount',
SUM(JOB.TRANSPORTATION_TCOST) AS 'tcost',
SUM(JOB.TRANSPORTATION_DISC_COST) AS 'dtcost',
AVG(JOB.TRANSPORTATION_DISC) AS 'avgTDisc',
SUM(JOB.TRANSPORTATION_TCOST) + SUM(JOB.TRANSPORTATION_DISC_COST) AS 'TGrossAmtBilled',
SUM(JOB.TRANSPORTATION_TCOST) / COUNT(DISTINCT JOB.PATIENTID) AS 'PatAvgT',
SUM(JOB.TRANSPORTATION_DISC) AS 'avgPercentDiscT',
SUM(JOB.TRANSPORTATION_TCOST) / COUNT(JOB.JOBID) AS 'RefAvgT',
JOB.JURISDICTION,
PAYER.PAY_GROUPNAME,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.PATIENTID,
JOB.INVOICE_DATE,
JOB.JOBOUTCOMEID,
JOB.SERVICEOUTCOME,
INVOICE_AR.INVOICE_NO,
INVOICE_AR.INVOICE_DATE AS Expr1,
INVOICE_AR.AMOUNT_DUE,
INVOICE_AR.CLAIMNUMBER,
PATIENT.LASTNAME,
PATIENT.FIRSTNAME,
PATIENT.EMPLOYERNAME,
JOB_OUTCOME.DESCRIPTION,
SERVICE_TYPE.DESCRIPTION,
PAT_SERVICES_HISTORY.TRANSPORT_TYPE,

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed Successfully') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startDate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (4,5,6)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedSuccessfullyItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with complaint') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (4,5,6)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithComplaintItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with No Show') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (4,5,6)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithNoShowItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with No Charge') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (4,5,6)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithNoChargeItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with Situation') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (4,5,6)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithSituationItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Not Completed') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (4,5,6)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'NotCompletedItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Cancelled Prior to service') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (4,5,6)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CancelledPriorToServiceItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Cancelled During Service') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (4,5,6)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CancelledDuringServiceItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed Successfully') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (4,5,6)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'AwaitingforcompletionItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Pending for review') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (4,5,6)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like'%T ')) AS 'PendingforreviewItems'

FROM JOB
INNER JOIN INVOICE_AR
ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER
ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES
ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT
ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME
ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE
ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY
ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID

WHERE
(INVOICE_AR.AMOUNT_DUE > 0)
AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate))
AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (4,5,6))
AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%')
AND
(INVOICE_AR.INVOICE_NO like '%T')

GROUP BY
JOB.JURISDICTION,
PAYER.PAY_GROUPNAME,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.PATIENTID,
JOB.INVOICE_DATE,
JOB.JOBOUTCOMEID,
JOB.SERVICEOUTCOME,
INVOICE_AR.INVOICE_NO,
INVOICE_AR.INVOICE_DATE,
INVOICE_AR.AMOUNT_DUE,
INVOICE_AR.CLAIMNUMBER,
PATIENT.LASTNAME,
PATIENT.FIRSTNAME,
PATIENT.EMPLOYERNAME,
JOB_OUTCOME.DESCRIPTION,
SERVICE_TYPE.DESCRIPTION,
PAT_SERVICES_HISTORY.TRANSPORT_TYPE

UNION ALL

SELECT
'2007 Quarter 3 ' as 'qtr',
COUNT(JOB.JOBID) AS 'transcount',
COUNT(DISTINCT JOB.PATIENTID) AS 'patientcount',
SUM(JOB.TRANSPORTATION_TCOST) AS 'tcost',
SUM(JOB.TRANSPORTATION_DISC_COST) AS 'dtcost',
AVG(JOB.TRANSPORTATION_DISC) AS 'avgTDisc',
SUM(JOB.TRANSPORTATION_TCOST) + SUM(JOB.TRANSPORTATION_DISC_COST) AS 'TGrossAmtBilled',
SUM(JOB.TRANSPORTATION_TCOST) / COUNT(DISTINCT JOB.PATIENTID) AS 'PatAvgT',
SUM(JOB.TRANSPORTATION_DISC) AS 'avgPercentDiscT',
SUM(JOB.TRANSPORTATION_TCOST) / COUNT(JOB.JOBID) AS 'RefAvgT',
JOB.JURISDICTION,
PAYER.PAY_GROUPNAME,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.PATIENTID,
JOB.INVOICE_DATE,
JOB.JOBOUTCOMEID,
JOB.SERVICEOUTCOME,
INVOICE_AR.INVOICE_NO,
INVOICE_AR.INVOICE_DATE AS Expr1,
INVOICE_AR.AMOUNT_DUE,
INVOICE_AR.CLAIMNUMBER,
PATIENT.LASTNAME,
PATIENT.FIRSTNAME,
PATIENT.EMPLOYERNAME,
JOB_OUTCOME.DESCRIPTION,
SERVICE_TYPE.DESCRIPTION,
PAT_SERVICES_HISTORY.TRANSPORT_TYPE,

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed Successfully') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startDate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (7,8,9)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedSuccessfullyItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with complaint') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (7,8,9)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithComplaintItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with No Show') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (7,8,9)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithNoShowItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with No Charge') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (7,8,9)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithNoChargeItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with Situation') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (7,8,9)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithSituationItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Not Completed') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (7,8,9)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'NotCompletedItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Cancelled Prior to service') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (7,8,9)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CancelledPriorToServiceItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Cancelled During Service') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (7,8,9)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CancelledDuringServiceItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed Successfully') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (7,8,9)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'AwaitingforcompletionItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Pending for review') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (7,8,9)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like'%T ')) AS 'PendingforreviewItems'

FROM JOB
INNER JOIN INVOICE_AR
ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER
ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES
ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT
ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME
ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE
ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY
ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID

WHERE
(INVOICE_AR.AMOUNT_DUE > 0)
AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate))
AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (7,8,9))
AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%')
AND
(INVOICE_AR.INVOICE_NO like '%T')

GROUP BY
JOB.JURISDICTION,
PAYER.PAY_GROUPNAME,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.PATIENTID,
JOB.INVOICE_DATE,
JOB.JOBOUTCOMEID,
JOB.SERVICEOUTCOME,
INVOICE_AR.INVOICE_NO,
INVOICE_AR.INVOICE_DATE,
INVOICE_AR.AMOUNT_DUE,
INVOICE_AR.CLAIMNUMBER,
PATIENT.LASTNAME,
PATIENT.FIRSTNAME,
PATIENT.EMPLOYERNAME,
JOB_OUTCOME.DESCRIPTION,
SERVICE_TYPE.DESCRIPTION,
PAT_SERVICES_HISTORY.TRANSPORT_TYPE

UNION ALL

SELECT
'2007 Quarter 4 ' as 'qtr',
COUNT(JOB.JOBID) AS 'transcount',
COUNT(DISTINCT JOB.PATIENTID) AS 'patientcount',
SUM(JOB.TRANSPORTATION_TCOST) AS 'tcost',
SUM(JOB.TRANSPORTATION_DISC_COST) AS 'dtcost',
AVG(JOB.TRANSPORTATION_DISC) AS 'avgTDisc',
SUM(JOB.TRANSPORTATION_TCOST) + SUM(JOB.TRANSPORTATION_DISC_COST) AS 'TGrossAmtBilled',
SUM(JOB.TRANSPORTATION_TCOST) / COUNT(DISTINCT JOB.PATIENTID) AS 'PatAvgT',
SUM(JOB.TRANSPORTATION_DISC) AS 'avgPercentDiscT',
SUM(JOB.TRANSPORTATION_TCOST) / COUNT(JOB.JOBID) AS 'RefAvgT',
JOB.JURISDICTION,
PAYER.PAY_GROUPNAME,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.PATIENTID,
JOB.INVOICE_DATE,
JOB.JOBOUTCOMEID,
JOB.SERVICEOUTCOME,
INVOICE_AR.INVOICE_NO,
INVOICE_AR.INVOICE_DATE AS Expr1,
INVOICE_AR.AMOUNT_DUE,
INVOICE_AR.CLAIMNUMBER,
PATIENT.LASTNAME,
PATIENT.FIRSTNAME,
PATIENT.EMPLOYERNAME,
JOB_OUTCOME.DESCRIPTION,
SERVICE_TYPE.DESCRIPTION,
PAT_SERVICES_HISTORY.TRANSPORT_TYPE,

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed Successfully') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startDate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedSuccessfullyItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with complaint') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithComplaintItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with No Show') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithNoShowItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with No Charge') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithNoChargeItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed with Situation') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedWithSituationItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Not Completed') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'NotCompletedItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Cancelled Prior to service') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CancelledPriorToServiceItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Cancelled During Service') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CancelledDuringServiceItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Completed Successfully') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'AwaitingforcompletionItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION = 'Pending for review') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like'%T ')) AS 'PendingforreviewItems'

FROM JOB
INNER JOIN INVOICE_AR
ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER
ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES
ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT
ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME
ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE
ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY
ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID

WHERE
(INVOICE_AR.AMOUNT_DUE > 0)
AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (@.startdate) and DATEADD(@.enddate))
AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (10,11,12))
AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%')
AND
(INVOICE_AR.INVOICE_NO like '%T')

GROUP BY
JOB.JURISDICTION,
PAYER.PAY_GROUPNAME,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.PATIENTID,
JOB.INVOICE_DATE,
JOB.JOBOUTCOMEID,
JOB.SERVICEOUTCOME,
INVOICE_AR.INVOICE_NO,
INVOICE_AR.INVOICE_DATE,
INVOICE_AR.AMOUNT_DUE,
INVOICE_AR.CLAIMNUMBER,
PATIENT.LASTNAME,
PATIENT.FIRSTNAME,
PATIENT.EMPLOYERNAME,
JOB_OUTCOME.DESCRIPTION,
SERVICE_TYPE.DESCRIPTION,
PAT_SERVICES_HISTORY.TRANSPORT_TYPE

ORDER BY 'qtr' asc

That query is needlessly big.

What you need to do is consolodate... there are SEVERAL places where you repeat 10 lines of code just to get 1 small difference. This is not only making your script large, but also amajor perfornace loss.

Example:

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION ='Completed Successfully') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@.startate) and DATEADD(year,0,@.endate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS 'CompletedSuccessfullyItems',

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (JOB_OUTCOME.DESCRIPTION ='Completed with complaint') AND (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@.startate) and DATEADD(year,0,@.endate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) AS'CompletedWithComplaintItems',

So what you need to do is shrink this into the following:

(SELECT
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION ='Completed with complaint') THEN 1 ELSE 0 END) AS'CompletedWithComplaintItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION ='Completed Successfully') THEN 1 ELSE 0 END) AS'CompletedSuccessfullyItems',
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@.startate) and DATEADD(year,0,@.endate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) _temp

You will need to treat this as a sort of "inline view" and then JOIN to it... example:

SELECT
'blah' AS Blah,
'blah 2' AS SomeOtherColumn,
_temp.* <-- that right there will pull ALL fields from below... you can add all of the fields you want easily.
FROM
(SELECT
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION ='Completed with complaint') THEN 1 ELSE 0 END) AS'CompletedWithComplaintItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION ='Completed Successfully') THEN 1 ELSE 0 END) AS'CompletedSuccessfullyItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION ='Completed with No Charge') THEN 1 ELSE 0 END) AS'CompletedWithNoChargeItems'
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@.startate) and DATEADD(year,0,@.endate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) _temp

Peace,

(by the way... this is going to take a lot of time for you to clean up... and I don't think anyone else is going to be able to offer much more support than the above... so if you start to see that you have the ability to fix your problem, please mark this post as the "answer" so that we know it's closed).

|||

Can you clarify the below

You will need to treat this as a sort of "inline view" and then JOIN to it... example:

SELECT
'blah' AS Blah,
'blah 2' AS SomeOtherColumn,
_temp.* <-- that right there will pull ALL fields from below... you can add all of the fields you want easily.
FROM
(SELECT
SUM

This is what I did below but I dont understand the above part you mentioned in the above post

SELECT
'Prior Year All ' as 'qtr',
COUNT(JOB.JOBID) AS 'transcount',
COUNT(DISTINCT JOB.PATIENTID) AS 'patientcount',
SUM(JOB.TRANSPORTATION_TCOST) AS 'tcost',
SUM(JOB.TRANSPORTATION_DISC_COST) AS 'dtcost',
AVG(JOB.TRANSPORTATION_DISC) AS 'avgTDisc',
SUM(JOB.TRANSPORTATION_TCOST) + SUM(JOB.TRANSPORTATION_DISC_COST) AS 'TGrossAmtBilled',
SUM(JOB.TRANSPORTATION_TCOST) / COUNT(DISTINCT JOB.PATIENTID) AS 'PatAvgT',
SUM(JOB.TRANSPORTATION_DISC) AS 'avgPercentDiscT',
SUM(JOB.TRANSPORTATION_TCOST) / COUNT(JOB.JOBID) AS 'RefAvgT',
JOB.JURISDICTION,
PAYER.PAY_GROUPNAME,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.PATIENTID,
JOB.INVOICE_DATE,
JOB.JOBOUTCOMEID,
JOB.SERVICEOUTCOME,
INVOICE_AR.INVOICE_NO,
INVOICE_AR.INVOICE_DATE AS Expr1,
INVOICE_AR.AMOUNT_DUE,
INVOICE_AR.CLAIMNUMBER,
PATIENT.LASTNAME,
PATIENT.FIRSTNAME,
PATIENT.EMPLOYERNAME,
JOB_OUTCOME.DESCRIPTION,
SERVICE_TYPE.DESCRIPTION,
PAT_SERVICES_HISTORY.TRANSPORT_TYPE,

(SELECT COUNT(JOB.JOBOUTCOMEID)
FROM
(SELECT
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Completed with complaint') THEN 1 ELSE 0 END) AS 'CompletedWithcomplaintItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Completed Successfully') THEN 1 ELSE 0 END) AS 'CompletedSuccessfullyItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Completed with No Charge') THEN 1 ELSE 0 END) AS 'CompletedwithNoChargeItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Completed with No Show') THEN 1 ELSE 0 END) AS 'CompletedwithNoShowItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Completed with Situation') THEN 1 ELSE 0 END) AS 'CompletedWithSituationItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Not Completed') THEN 1 ELSE 0 END) AS 'NotCompletedItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Cancelled Prior to service') THEN 1 ELSE 0 END) AS 'CancelledPriortoserviceItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Cancelled During Service') THEN 1 ELSE 0 END) AS 'CancelledDuringServiceItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Awaiting for completion') THEN 1 ELSE 0 END) AS 'AwaitingforcpmpletionItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Pending for review') THEN 1 ELSE 0 END) AS 'PendingforreviewItems'

FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@.startate) and DATEADD(year,0,@.endate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) _temp

FROM JOB
INNER JOIN INVOICE_AR
ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER
ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES
ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT
ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME
ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE
ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY
ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID

WHERE
(INVOICE_AR.AMOUNT_DUE > 0)
AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@.startate) and DATEADD(year,0,@.endate))
AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12))
AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%')
AND
(INVOICE_AR.INVOICE_NO like '%T')

GROUP BY
JOB.JURISDICTION,
PAYER.PAY_GROUPNAME,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.PATIENTID,
JOB.INVOICE_DATE,
JOB.JOBOUTCOMEID,
JOB.SERVICEOUTCOME,
INVOICE_AR.INVOICE_NO,
INVOICE_AR.INVOICE_DATE,
INVOICE_AR.AMOUNT_DUE,
INVOICE_AR.CLAIMNUMBER,
PATIENT.LASTNAME,
PATIENT.FIRSTNAME,
PATIENT.EMPLOYERNAME,
JOB_OUTCOME.DESCRIPTION,
SERVICE_TYPE.DESCRIPTION,
PAT_SERVICES_HISTORY.TRANSPORT_TYPE

|||

Run this EXACT script and you will see what I'm talking about:

SELECT
'blah' AS Blah,
'blah 2' AS SomeOtherColumn,
_temp.*
FROM
(SELECT
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION ='Completed with complaint') THEN 1 ELSE 0 END) AS'CompletedWithComplaintItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION ='Completed Successfully') THEN 1 ELSE 0 END) AS'CompletedSuccessfullyItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION ='Completed with No Charge') THEN 1 ELSE 0 END) AS'CompletedWithNoChargeItems'
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@.startate) and DATEADD(year,0,@.endate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) _temp

Then when you understand that the "_temp" will provide you with more than just 1 field (in this case it will provide 3)... you will realize that you can consolodate your 50 reduntant SQL statements above into just 1... but the 1 will return all the columns you want.

|||

First of all I want to thank you for all the great help. This has help me see how I can program more effiencently.

Second I keep getting an error telling me there is an error in the query, Incorrect syntax near the keyword 'FROM'.

Can you look at the code below and tell me what is causing this does it have to do with the(SELECT or is it something else..

SELECT
'Prior Year All ' as 'qtr',
COUNT(JOB.JOBID) AS 'transcount',
COUNT(DISTINCT JOB.PATIENTID) AS 'patientcount',
SUM(JOB.TRANSPORTATION_TCOST) AS 'tcost',
SUM(JOB.TRANSPORTATION_DISC_COST) AS 'dtcost',
AVG(JOB.TRANSPORTATION_DISC) AS 'avgTDisc',
SUM(JOB.TRANSPORTATION_TCOST) + SUM(JOB.TRANSPORTATION_DISC_COST) AS 'TGrossAmtBilled',
SUM(JOB.TRANSPORTATION_TCOST) / COUNT(DISTINCT JOB.PATIENTID) AS 'PatAvgT',
SUM(JOB.TRANSPORTATION_DISC) AS 'avgPercentDiscT',
SUM(JOB.TRANSPORTATION_TCOST) / COUNT(JOB.JOBID) AS 'RefAvgT',
JOB.JURISDICTION, PAYER.PAY_GROUPNAME, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID, JOB.PATIENTID, JOB.INVOICE_DATE,JOB.JOBOUTCOMEID,JOB.SERVICEOUTCOME,
INVOICE_AR.INVOICE_NO, INVOICE_AR.INVOICE_DATE AS Expr1, INVOICE_AR.AMOUNT_DUE,INVOICE_AR.CLAIMNUMBER,
PATIENT.LASTNAME, PATIENT.FIRSTNAME, PATIENT.EMPLOYERNAME,JOB_OUTCOME.DESCRIPTION,SERVICE_TYPE.DESCRIPTION,
PAT_SERVICES_HISTORY.TRANSPORT_TYPE, _temp.*

FROM
(SELECT
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Completed with complaint') THEN 1 ELSE 0 END) AS 'CompletedWithcomplaintItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Completed Successfully') THEN 1 ELSE 0 END) AS 'CompletedSuccessfullyItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Completed with No Charge') THEN 1 ELSE 0 END) AS 'CompletedwithNoChargeItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Completed with No Show') THEN 1 ELSE 0 END) AS 'CompletedwithNoShowItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Completed with Situation') THEN 1 ELSE 0 END) AS 'CompletedWithSituationItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Not Completed') THEN 1 ELSE 0 END) AS 'NotCompletedItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Cancelled Prior to service') THEN 1 ELSE 0 END) AS 'CancelledPriortoserviceItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Cancelled During Service') THEN 1 ELSE 0 END) AS 'CancelledDuringServiceItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Awaiting for completion') THEN 1 ELSE 0 END) AS 'AwaitingforcpmpletionItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Pending for review') THEN 1 ELSE 0 END) AS 'PendingforreviewItems'

FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE (INVOICE_AR.AMOUNT_DUE > 0) AND
(INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@.startate) and DATEADD(year,0,@.endate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND
(PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')) _temp
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID

WHERE
(INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD (year,0,@.startate) and DATEADD(year,0,@.endate)) AND
(MONTH(INVOICE_AR.INVOICE_DATE) in (1,2,3,4,5,6,7,8,9,10,11,12)) AND (PAYER.PAY_GROUPNAME like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%T')

GROUP BY
JOB.JURISDICTION,
PAYER.PAY_GROUPNAME,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.PATIENTID,
JOB.INVOICE_DATE,
JOB.JOBOUTCOMEID,
JOB.SERVICEOUTCOME,
INVOICE_AR.INVOICE_NO,
INVOICE_AR.INVOICE_DATE,
INVOICE_AR.AMOUNT_DUE,
INVOICE_AR.CLAIMNUMBER,
PATIENT.LASTNAME,
PATIENT.FIRSTNAME,
PATIENT.EMPLOYERNAME,
JOB_OUTCOME.DESCRIPTION,
SERVICE_TYPE.DESCRIPTION,
PAT_SERVICES_HISTORY.TRANSPORT_TYPE

ORDER BY 'qtr' asc

|||

You currently have...

SELECT {some stuff}

FROM ({virtual table definition}) _temp

FROM JOB

{more joins}

WHERE {stuff}

GROUP BY {stuff}

ORDER BY {stuff}

You have two "FROM" clauses, and you can't. You probably want to join the virtual table to the job table, or remove the reference to the job table and update your joins to reference _temp.

|||

You're on the right track!

Because you're query is only going to return 1 record, it's OK to "CROSS JOIN" the "_temp" view I showed you up above.

(A "cross join" multiples the number of records together... so normally it would be bad, but 1 x 1 = 1 :) so it's ok)

Here's an example of how to do that.

SELECT
table1.ColumnA,
table1.ColumnB,
table2.ColumnA,
table2.ColumnB
FROM
table1, table2

Keep going!

|||I have never done this before I am new to csome of the functions you are talking about Cross Joining?? Nullable this script you showed me earlier works great but does not allow me to get the other fields I need. So I have tried toput the script you provided into my script I dont understand how to do this type of join can you assist me? I really appreciate your help.|||

A CROSS JOIN is like an INNER JOIN, but there is no "ON" clause (meaning it pulls ALL records from table1 and ALL records from table2 together).

Try my example above with a real table... change "table1" to a table that you have (like "SysObjects" or something) and change table2 to the "_temp" code we did above and look at the results.