Monday, February 20, 2012

Need help with tsql script

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

How do want to diplay the "Year before also"?You just run it for the previous year by changing the input dates,You could alias the table so you could select twice and join the second selection change (INVOICE_AR.INVOICE_DATE BETWEEN @.startdate and @.enddate)to (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD(year, -1, @.startdate) and DATEADD(year, -1, @.enddate)and you can select two periods at once!|||How do want to diplay the "Year before also"?You just run it for the previous year by changing the input dates,You could alias the table so you could select twice and join the second selection change (INVOICE_AR.INVOICE_DATE BETWEEN @.startdate and @.enddate)to (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD(year, -1, @.startdate) and DATEADD(year, -1, @.enddate)and you can select two periods at once!|||How do want to diplay the "Year before also"?You just run it for the previous year by changing the input dates,You could alias the table so you could select twice and join the second selection change (INVOICE_AR.INVOICE_DATE BETWEEN @.startdate and @.enddate)to (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD(year, -1, @.startdate) and DATEADD(year, -1, @.enddate)and you can select two periods at once!|||

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