ASP to SQL Connection Prob, Same Server, IIS

Yes, it should be easy...

I have a single box hosing IIS and SQL Server 2000. In my ASP script I've
tried using the following connection strings to attempt to connect to no
avail:

"Provider=SQLOLEDB;Server=servername\instancename,1 433;Database=TEST;network=DBMSSOCN;uid=TEST;pwd=TE ST"
"Provider=SQLOLEDB;Server=local IP
address\instancename,1433;Database=TEST;network=DB MSSOCN;uid=TEST;pwd=TEST"

"Provider=SQLOLEDB;Server=external IP
address\servername\instancename;Database=TEST;netw ork=DBMSSOCN;uid=TEST;pwd=TEST"

Getting error:

"Microsoft OLE DB Provider for SQL Server (0x80004005)
[DBNETLIB][ConnectionOpen (Connect()).]Specified SQL server not found."

SQL Server is set up for SQL and Windows authentication. Server utility is
configured for both named pipes and TCP/IP. Port is set to 1433.

I can connect to the DB using Query Analyzer both from the local server
itself AND from a remote laptop over the internet.

So, what am I missing???

Thanks in advance,

ChadChad Richardson (chad@.NIXSPAM_chadrichardson.com) writes:
> SQL Server is set up for SQL and Windows authentication. Server utility is
> configured for both named pipes and TCP/IP. Port is set to 1433.

But you have a named instance. Yes, you can of course have a named
instance on port 1433, but normally this is where you have the unnamed
instance.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||OK. But if you can have one, than do you have any other ideas on why it
would not connect?

I have this same set up on my development laptop and it connects fine,
except that my web site on the laptop is served up from a virtual directory.
On the home development box, the web site is located under the
InetPub\wwwroot folder. Not sure if this would change the "perspective" that
the connection call is coming from and hence would require a different
syntax...

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns960E3D672D9AYazorman@.127.0.0.1...
> Chad Richardson (chad@.NIXSPAM_chadrichardson.com) writes:
>> SQL Server is set up for SQL and Windows authentication. Server utility
>> is
>> configured for both named pipes and TCP/IP. Port is set to 1433.
> But you have a named instance. Yes, you can of course have a named
> instance on port 1433, but normally this is where you have the unnamed
> instance.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Chad Richardson (chad@.NIXSPAM_chadrichardson.com) writes:
> OK. But if you can have one, than do you have any other ideas on why it
> would not connect?

I asked my question mainly as incentive for you to double-check.

> I have this same set up on my development laptop and it connects fine,
> except that my web site on the laptop is served up from a virtual
> directory. On the home development box, the web site is located under
> the InetPub\wwwroot folder. Not sure if this would change the
> "perspective" that the connection call is coming from and hence would
> require a different syntax...

I don't really see why either. Then again, I'm completely ignorant about
IIS.

But there is one thing I don't like in your connection string:
network=DBMSSOCN. I believe this library is out-dated. Not that I
think it matters, but remove it.

And, hm, what about removing 1433? After all there is a service to
find the port. And then there is shared memory which does not even need a
port...

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

ASP script to display database structure?

Posted this to SQLServer.Tools, but maybe it belongs here...
Does anyone have an ASP script that I can use to dump the structure of an MS
SQL 2000 database?
The firewall here won't allow any remote connections through, and our DB
host does not have any web administration.
I just need the table names, field names, and field data types.
Thx!would information_schema.tables and information_schema.columns do?
Just join the views and select the columns you need.
SELECT *
FROM information_schema.tables t
JOIN information_schema.columns c
ON t.table_schema = c.table_schema
AND t.table_name = c.table_name
"Noozer" wrote:

> Posted this to SQLServer.Tools, but maybe it belongs here...
>
> Does anyone have an ASP script that I can use to dump the structure of an
MS
> SQL 2000 database?
> The firewall here won't allow any remote connections through, and our DB
> host does not have any web administration.
> I just need the table names, field names, and field data types.
> Thx!
>
>|||Thanks!
That did the trick!
"Nigel Rivett" <NigelRivett@.discussions.microsoft.com> wrote in message
news:46273214-5138-4D9F-A0EB-7C1AC0B70344@.microsoft.com...
> would information_schema.tables and information_schema.columns do?
> Just join the views and select the columns you need.
> SELECT *
> FROM information_schema.tables t
> JOIN information_schema.columns c
> ON t.table_schema = c.table_schema
> AND t.table_name = c.table_name

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.