I have a t sql script that works but i need to modify it to show the prvious years info can someone show me how to do this below is the code I have and it show this between defined dates I need it to show the yearbefore also
SELECT 'Quarter All' as 'qtr',
COUNT(JOB.JOBID) as 'transcount',
COUNT(DISTINCT JOB.PATIENTID) as 'patient count',
SUM(JOB.LANGUAGE_TCOST) as 'lcost',
SUM(JOB.LANGUAGE_DISC_COST) as 'dlcost',
AVG(JOB.LANGUAGE_DISC) as 'avgLDisc',
SUM(JOB.LANGUAGE_TCOST) + SUM(JOB.LANGUAGE_DISC_COST) as 'LGrossAmtBilled',
SUM(JOB.LANGUAGE_TCOST) / COUNT(DISTINCT JOB.PATIENTID) as 'PatAvgL',
SUM(JOB.LANGUAGE_TCOST) / COUNT(JOB.JOBID) as 'RefAvgL',
SUM(JOB.LANGUAGE_DISC) as 'avgPercentDiscL',
JOB.JURISDICTION,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.INVOICE_DATE,
JOB.JOBOUTCOMEID,
JOB.SERVICEOUTCOME,
JOB.LANGUAGE_ID,
INVOICE_AR.INVOICE_NO,
INVOICE_AR.INVOICE_DATE AS EXPR1,
INVOICE_AR.AMOUNT_DUE,
INVOICE_AR.CLAIMNUMBER,
LANGUAGES.DESCRIPTION
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 LANGUAGES
ON JOB.LANGUAGE_ID = LANGUAGES.DESCRIPTION
WHERE
(INVOICE_AR.AMOUNT_DUE > 0)
AND
(INVOICE_AR.INVOICE_DATE BETWEEN @.startdate and @.enddate)
AND
(MONTH(INVOICE_AR.INVOICE_DATE) IN (1,2,3,4,5,6,7,8,9,10,11,12))
AND
(PAYER.PAY_COMPANY like '%' + @.Company + '%')
Group By
JOB.JURISDICTION,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.INVOICE_DATE,
JOB.JOBOUTCOMEID,
JOB.SERVICEOUTCOME,
JOB.LANGUAGE_ID,
INVOICE_AR.INVOICE_NO,
INVOICE_AR.INVOICE_DATE,
INVOICE_AR.AMOUNT_DUE,
INVOICE_AR.CLAIMNUMBER,
LANGUAGES.DESCRIPTION
Order By 'QTR' asc
Try
SELECT 'Quarter All' as 'qtr',
COUNT(JOB.JOBID) as 'transcount',
COUNT(DISTINCT JOB.PATIENTID) as 'patient count',
SUM(JOB.LANGUAGE_TCOST) as 'lcost',
SUM(JOB.LANGUAGE_DISC_COST) as 'dlcost',
AVG(JOB.LANGUAGE_DISC) as 'avgLDisc',
SUM(JOB.LANGUAGE_TCOST) + SUM(JOB.LANGUAGE_DISC_COST) as 'LGrossAmtBilled',
SUM(JOB.LANGUAGE_TCOST) / COUNT(DISTINCT JOB.PATIENTID) as 'PatAvgL',
SUM(JOB.LANGUAGE_TCOST) / COUNT(JOB.JOBID) as 'RefAvgL',
SUM(JOB.LANGUAGE_DISC) as 'avgPercentDiscL',
JOB.JURISDICTION,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.INVOICE_DATE,
JOB.JOBOUTCOMEID,
JOB.SERVICEOUTCOME,
JOB.LANGUAGE_ID,
I1.INVOICE_NO,
I1.INVOICE_DATE AS EXPR1,
I1.AMOUNT_DUE,
I1.CLAIMNUMBER,
I2.INVOICE_NO,
I2.INVOICE_DATE AS EXPR1,
I2.AMOUNT_DUE,
I2.CLAIMNUMBER,
LANGUAGES.DESCRIPTION
FROM JOB
INNER JOIN INVOICE_AR I1 ON JOB.JOBID = INVOICE_AR.JOBID
INNER JOIN INVOICE_AR I2 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 LANGUAGES ON JOB.LANGUAGE_ID = LANGUAGES.DESCRIPTION
WHERE I1.AMOUNT_DUE > 0
AND (I1.INVOICE_DATE BETWEEN @.startdate and @.enddate)
AND (DATEADD(year, 1, I2.INVOICE_DATE) BETWEEN @.startdate and @.enddate)
AND (MONTH(I1.INVOICE_DATE) IN (1,2,3,4,5,6,7,8,9,10,11,12))
AND (PAYER.PAY_COMPANY like '%' + @.Company + '%')
Group By JOB.JURISDICTION, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE, JOB.JOBOUTCOMEID, JOB.SERVICEOUTCOME,
JOB.LANGUAGE_ID, I1.INVOICE_NO, I1.INVOICE_DATE, I1.AMOUNT_DUE,I1.CLAIMNUMBER, LANGUAGES.DESCRIPTION
Order By 'QTR' asc
No comments:
Post a Comment