Sunday, February 19, 2012

I need help top N report

I have created a report this is the Sql statement I need to be able to do a Top N 20 by payer.Pay_Company for total Invoice_ar amount can someone help me with this?

SELECT COUNT(JOB.JOBID) AS 'transcount', COUNT(DISTINCT JOB.PATIENTID) AS 'patient count', 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_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE,
INVOICE_AR.INVOICE_DATE AS Expr1, INVOICE_AR.AMOUNT_DUE
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
WHERE (INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN @.startdate AND @.enddate)
GROUP BY JOB.JURISDICTION, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE,
INVOICE_AR.INVOICE_DATE, INVOICE_AR.AMOUNT_DUE
ORDER BY 'tcost' DESC

So whats the question?|||The question is I need to know how to take that t sql script and add what ever I need to be able to give me the top 20 payers by largest dollars within the defined startdate and enddate|||
SELECT TOP 20COUNT(JOB.JOBID)AS'transcount',COUNT(DISTINCT JOB.PATIENTID)AS'patient count',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_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE, INVOICE_AR.INVOICE_DATEAS Expr1, INVOICE_AR.AMOUNT_DUEFROM JOBINNERJOIN INVOICE_ARON JOB.JOBID = INVOICE_AR.JOBIDLEFTOUTER JOIN PAYERON PAYER.PAYERID = JOB.PAYERIDLEFTOUTER JOIN STATESON JOB.JURISDICTION = STATES.INITIALSWHERE (INVOICE_AR.AMOUNT_DUE > 0)AND (INVOICE_AR.INVOICE_DATEBETWEEN @.startdateAND @.enddate)GROUP BY JOB.JURISDICTION, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE, INVOICE_AR.INVOICE_DATE, INVOICE_AR.AMOUNT_DUEORDER BY'tcost'DESC

No comments:

Post a Comment