Showing posts with label string. Show all posts
Showing posts with label string. Show all posts

Thursday, March 22, 2012

asp page variable to sql to openquery

Im having trouble putting all of this together, but here is the
scenario
ASP page with a Query String Variable,
Example: http://mywebsite/mywebpage.aspx?variable=x
now i need to query using openquery across a linked server using this
variable.
Like:
SELECT COLUMN1, COLUMN2
FROM OPENQUERY(LINKEDSERVER, '
SELECT COLUMN1, COLUMN2
FROM LINKEDSERVERTABLE
WHERE COLUMN3=@.VARIABLE')
I know this won't work, but this is the concept, if i run the query
against the linked server then my results take about 20 seconds to
return, but if i could pass the variable to an openquery then my
results would be returned in 1-2 seconds. which is acceptable. Thanks
for any help in advance!!You need to build the SQL statement as a string and
then pass that string to an EXEC. You can find more
information and an example in the following article:
HOW TO: Pass a Variable to a Linked Server Query
http://support.microsoft.com/?id=314520
-Sue
On 3 Aug 2006 16:07:15 -0700, joshd@.norrisinc.com wrote:

>Im having trouble putting all of this together, but here is the
>scenario
>ASP page with a Query String Variable,
>Example: http://mywebsite/mywebpage.aspx?variable=x
>now i need to query using openquery across a linked server using this
>variable.
>Like:
> SELECT COLUMN1, COLUMN2
> FROM OPENQUERY(LINKEDSERVER, '
> SELECT COLUMN1, COLUMN2
> FROM LINKEDSERVERTABLE
> WHERE COLUMN3=@.VARIABLE')
>I know this won't work, but this is the concept, if i run the query
>against the linked server then my results take about 20 seconds to
>return, but if i could pass the variable to an openquery then my
>results would be returned in 1-2 seconds. which is acceptable. Thanks
>for any help in advance!!
Posted by Catrice at 7:32 PM 0 comments
Labels: , , , , , , , , , , , , , , ,

ASP Connection String To SQL Server

Hi
This is new to me.
I am trying to connect an asp page to sql server.
Any help would be gratefully appreciated
I get the error message:
Technical Information (for support personnel)
a.. Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required: ''
/idataconnection.asp, line 10
b.. Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.1.4322)
c.. Page:
GET /idataconnection.asp
The code I have in the page is:
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 1</title>
</head>
<body>
<%
oConn.Open "DSN=pubs;" & _
"Uid=testuser;" & _
"Pwd=123"
%>
</body>
</html>You have to first set oConn to a new instance of the ADODB connection
object.
"qqqq" <dddh@.ddd.clc> wrote in message
news:BW0Xb.4715$vo1.1496@.newsfep4-winn.server.ntli.net...
> Hi
> This is new to me.
> I am trying to connect an asp page to sql server.
> Any help would be gratefully appreciated
> I get the error message:
> Technical Information (for support personnel)
> a.. Error Type:
> Microsoft VBScript runtime (0x800A01A8)
> Object required: ''
> /idataconnection.asp, line 10
>
> b.. Browser Type:
> Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.1.4322)
> c.. Page:
> GET /idataconnection.asp
>
> The code I have in the page is:
> <html>
> <head>
> <meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
> <title>New Page 1</title>
> </head>
> <body>
> <%
> oConn.Open "DSN=pubs;" & _
> "Uid=testuser;" & _
> "Pwd=123"
> %>
> </body>
> </html>
>|||Hello,
try this
<%
set conn1=server.CreateObject("adodb.connection")
conn1.cursorlocation=3
conn1.Open "Provider=sqloledb;" & _
"Data Source=DB;" & _
"Initial Catalog=ayaz;" & _
"User Id=ayaz;" & _
"Password=ayaz"
%>
Thanks,
Warm Regards,
Ayaz Ahmed
Software Engineer & Web Developer
Creative Chaos (Pvt.) Ltd.
"Managing Your Digital Risk"
http://www.csquareonline.com
Karachi, Pakistan
Mobile +92 300 2280950
Office +92 21 455 2414
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!
Posted by Catrice at 7:23 PM 0 comments
Labels: , , , , , , , , , , , , , , ,

ASP Authentication with SQL Server 2005 - not express, permanent change

Hi all,

I've read up on the fact that if you use SQL server 2005 instead of SQL express you need to change the connection string in web.config.

Is there anyway to make this default connection type, or do I have to do it for every website I create. i.e. is there a global file that this connection can be entered into. Then whenever I start a new website I don't have to put the string in.

Just me being lazy really. But it's nice to know if it's possible.

Many thanks

DrB2k2

Hi,

I did it also on my dev machine. Just change the setting of the connectionstring for the LocalSqlServer in the machine.config. Make sure to make a backup, just in case, of this file first. You can find the machine.config file in this folder:C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG

Grz, Kris.

|||

Lovely I'll give it a go,thanks

DrB2k2

sql
Posted by Catrice at 7:20 PM 0 comments
Labels: , , , , , , , , , , , , , , ,

Tuesday, March 20, 2012

ASP - Connection to second instance

I have a second instance set up for a sql server. I'm going
crazy trying to determine the syntax for the connection string
for my ASP program.
Please help
Mindy Jeanne LeslieUsually it would be something like:
...data source=SERVERNAME\INSTANCENAME; user id=.........
Regards.
"Mindy Leslie via SQLMonster.com" wrote:
> I have a second instance set up for a sql server. I'm going
> crazy trying to determine the syntax for the connection string
> for my ASP program.
> Please help
> Mindy Jeanne Leslie
>
Posted by Catrice at 9:39 PM 0 comments
Labels: , , , , , , , , , , , , ,

Monday, March 19, 2012

ask for ASP connection string to ODBC

do anyone know the connection string for ASP connect to ODBC?
i have created an ODBC for MSSQL named "DMZ-SQL" already.
Thank you very much!!I have an example for DSN-less connection via ADO, so u don't have to always create your Data Source Name in the ODBC properties everytime u move your development.

Set DBConn = Server.CreateObject("ADODB.Connection")
DBConn.Open "Driver={SQL Server};SERVER=127.0.0.1;UID=sa;PWD1234=;DATABASE= Mydatabase"|||Thank you very much!
Posted by Catrice at 2:51 AM 0 comments
Labels: , , , , , , , , , , , , , , ,

Sunday, March 11, 2012

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.

Posted by Catrice at 7:11 PM 0 comments
Labels: , , , , , , , , , , , ,
Subscribe to: Posts (Atom)