2016 payment report for taxes

For users or potential users.
Post Reply
Tom Zaccaria
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

2016 payment report for taxes

Post by Tom Zaccaria » Wed Jan 04, 2017 3:40 am

The ball had just hit bottom in Times Square when we got our first call of 2017. It was a request for 'everything I paid you in 2016 for taxes'.

For newbies to OD or those of you who may have forgotten. We use the following:
/*75*/
SET @StartDate='2016-01-01';
SET @EndDate='2016-12-31';
SET @PatientNumber=247;
SELECT Patnum, ProcDate, SplitAmt, ProvNum, DatePay
FROM paysplit WHERE
DatePay>=@StartDate AND
DatePay<=@EndDate AND
PatNum=@PatientNumber;

You have to search by patient number in line 3. (Open patient panel for their number.)
Does anyone have a more elegant way to do this?
Thanks,
drtmz

Third Coast
Posts: 30
Joined: Fri Nov 14, 2014 4:56 pm

Re: 2016 payment report for taxes

Post by Third Coast » Fri Jan 06, 2017 12:26 pm

Sorry, I don't have any info to help you do it more elegantly. However I do appreciate you posting this query, and am curious if there a way to include multiple patients into this report? For example a husband and a wife?

Tom Zaccaria
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Re: 2016 payment report for taxes

Post by Tom Zaccaria » Sat Jan 07, 2017 3:11 am

Since the report is by patient number you have to run it multiple times. Once for each person who made any payments.

drtmz

Tom Zaccaria
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Re: 2016 payment report for taxes

Post by Tom Zaccaria » Mon Jan 09, 2017 2:31 pm

Just stumbled upon this query from our query list.
Surprised no one else pointed it out.

/*1222 Income Tax Receipt */
/*Provides the guarentor who paid, when they paid, the amount, and the payment type. Each row is for a single family. Ordered by Guarentor name, date paid, and amount.*/
/*Query code written/modified: 12/20/2016*/
SET @FromDate='2011-10-01' , @ToDate='2016-12-31'; /*Enter your starting date and ending date here*/
SET @ExcludePaymentTypes=''; /*Enter payment types to be excluded separated by a pipe ('|' without the quotes). For example, 'Check|NSF|Patient Refund' */
SET @GuarentorPatNum=''; /*Please specify the Guarentor PatNum to run the report for and format like the following => Guarentor1|Guarentor2|Guarentor3 , To see all leave blank as '' */
/*-------------------------DO NOT MODIFY BELOW THIS LINE-------------------------------------------------------------------------------*/
SET @GuarentorPatNum=(CASE WHEN @GuarentorPatNum='' THEN '^' ELSE CONCAT('^',REPLACE(@GuarentorPatNum,'|','$|^'),'$') END);
SET @ExcludePaymentTypes=(CASE WHEN @ExcludePaymentTypes='' THEN 'excludeFoo' ELSE CONCAT('^',REPLACE(@ExcludePaymentTypes,'|','$|^'),'$') END);
SELECT pay.PayDate AS 'Payment Date', CONCAT(pat.LName,', ',pat.FName) AS 'Payee', def.ItemName AS 'Payment Type', pay.PayAmt AS 'Amount'
FROM patient pat
INNER JOIN payment pay ON pat.PatNum=pay.PatNum
INNER JOIN definition def ON pay.PayType=def.DefNum
AND def.ItemName NOT REGEXP @ExcludePaymentTypes
INNER JOIN patient g ON g.PatNum = pat.Guarantor AND pat.Guarantor REGEXP @GuarentorPatNum
AND pay.PayDate BETWEEN @FromDate AND @ToDate
ORDER BY g.LName, pay.PayDate, pay.PayAmt;

Still need to know and enter the patient number. In this case, if you use the Guarentor's number the receipt will be for all family members. Nice heading with the title '2016 tax receipt' and then the practice name on the second line.

drtmz

Third Coast
Posts: 30
Joined: Fri Nov 14, 2014 4:56 pm

Re: 2016 payment report for taxes

Post by Third Coast » Tue Jan 10, 2017 1:47 pm

That's great! Works perfectly for what I was looking for.

Post Reply