Expression Help Needed ASAP

I have an expression =fields!Description.Value. This express has 3 different choices in the sql database they are Completed, Not Completed, Cancelled. I need to tak the above expression and make 3 new colums in my report where one colum totals only the Completed the 2 colum only totals the Not Completed and the third colum only totals the Cancelled. I need help with to add to the main expression to give these results

=fields!Description.Value this is what I have

Lookin gfor something like =CountDistinct(Sum(Fields!Description.Value) like '%Completed') but this will not work

Can some one help me.

I had a similar situation a while back. My best advice and the way I solved my problem was to change your sql statement. Change it to something like:

SELECT item1, item2, ... , (SELECT COUNT(id_field) FROM database_table WHERE Description = 'Completed') AS CompletedItems, (SELECT COUNT(id_field) FROM database_table WHERE Description = 'Not Completed') AS NotCompletedItems, (SELECT COUNT(id_field) FROM database_table WHERE Description = 'Cancelled') AS CancelledItems

FROM database_table

If you have a where clause in your sql, you will need to append that into the where clause for each (SELECT ... ) above. You now have three new fields (CompletedItems, NotCompletedItems, CancelledItems) in your Layout View that contain the correct values that you need. Warning, if you use one of the new fields in a table that has a lot of rows, the values will continue to repeat until the end of the table.

Hope this helps.

Mark

|||

Here is my sql statem can you show me I think I understand. The field I am using is the JOB_OUTCOME.DESCRIPTION

SELECT
'Prior Quarter 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
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

|||

sure...

SELECT
'Prior Quarter 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') 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 CompletedItems,

(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') 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 CancelledItems
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

my additions are in bold

Without knowing anything at all about your database, I think that code should return the totals for each level you wanted. You may need to change the FROM or WHERE clauses in the statement groups that I added.

Mark

|||

Thank a lot I really appreciate the help Mark If I wanted to add a couple more fields like to the Completed item

Completed Successfully

Completed with complaint

Completed with No Show

Would I only need to modifiy the completed in bold to 'Completed Successfully' & 'Completed with complaint" & 'Completed with noshow

how would you add these into the completed area of the script This should be the last question I need. I highlighesd the area in bold.

(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') 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 CompletedItems,

|||

(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,

Yes, that's correct. You can add more fields as necessary. Each new field requires its own (SELECT ... ) ASAliasName. For example, if I wanted to add Completed Successfully, I would copy and paste one of those (SELECT ... ) ASAliasName statements, change the bolded part from JOB_OUTCOME.DESCRIPTION = 'Completed' to JOB_OUTCOME.DESCRIPTION = 'Completed Successfully' and then change the bolded alias name from AS CompletedItems to AS CompletedSuccessfullyItems. It should look like the SQL above. Remember to add the comma after each alias name until you get to the last field that you want to add or you will receive an error.

Glad I could help you out.

Mark

|||

This is what I did with the code you provided. I have a issue though when I add this it give me a syntax error near ' .'. incorrect syntax near the work 'AS'

The script I posted at the begining works correctly. I am not sure why it throwing this error I added ) AS ' ***** ', the quotes to see if this was causing the error got the same error any Idea

SELECT
'Prior Quarter 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

ORDER BY 'qtr' asc

|||

In the Pending for review section delete the comma on the last line. It should look like:
) AS PendingforreviewItems

I guess I wasn't clear about the commas in my last post. With the comma there right before the FROM clause, it's expecting to see another field that it needs to return.

Mark

|||

Mark,

Removed the comma after the pending for reviewitem and still get this same error. I also sent you a email....

|||

Let me go back and look at it again.

Unfortunately, I can't access my email from this computer, but once I am at my home computer I will get to it.

|||Mark check you inbox I sent you private message|||

I found the error. The comma is missing in both the SELECT and GROUP BY clauses for JOB_OUTCOME.DESCRIPTION

Apparently, I messed that up when coming up with my answer because at first I cut and paste instead of copy and paste. When I went back and added JOB_OUTCOME.DESCRIPTION I must have forgotten the commas. Oops! Sorry about that.

Mark

|||The Select Clause? (SELECT COUNT(JOB.JOBOUTCOMEID), is this what you mean? I corrected the commas on the group by I see this but what do you mean the select? When I put the commas after the Select ZThe error changes to error near the FROM if I remove the commas after the select I get error near AS still|||

cpowers:

This is what I did with the code you provided. I have a issue though when I add this it give me a syntax error near ' .'. incorrect syntax near the work 'AS'

The script I posted at the begining works correctly. I am not sure why it throwing this error I added ) AS ' ***** ', the quotes to see if this was causing the error got the same error any Idea

SELECT
'Prior Quarter 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

ORDER BY 'qtr' asc

No, add commas in the bolded parts. That's what I was talking about.

|||I quoted the previous post and I don't see any way to edit posts, so if you copy and paste from my last post, go back and take the comma out after AS PendingforreviewItems