comparing dates in a table by a transaction type

yes, use datediff() e.g.

where datediff(day,[invoice date],[credit memo apply date]) > 21

datediff()
http://technet.microsoft.com/en-us/library/ms189794(v=sql.105).aspx

The problem is  the transaction type for invoice and credit memo are different
would a union statement be the go

the date applied is used by both credit memo and invoice


select
a.doc_no
a.customer_code as 'customer code',
a.trx_type,
a.date_doc,
a.date_applied,
a.date_posted,
a.user_id,
a.amt_net
from table  a
where date_applied >= '734869'
and trx_type = '1111' (invoice transaction no)
union
select  
a.doc_no,
a.customer_code as 'customer code',
a.trx_type,
a.date_doc,
a.date_applied,
a.date_posted,
a.user_id,
a.amt_net
from mytable a
where date_applied >= '734869'
and trx_type = '2222'
and amt_net = '352'
and datediff (day, date_applied, when trx_type = '2031' when  date_applied, trx_type '2032') >21

this is not quite working there is more to this, like creatin a function

I don't think a UNION will help.

What is the data type of [date_applied]? is it an int?
That would indicate it is a form of 'Julian date' I think

Do you happen to know what date 734869 is meant to be?
--------------
I'll be back soon, need to think about that query.

I'm not entirely certain of the join condition below - you may need to help with that, but would this work for you perhaps?

SELECT
        c.*
FROM credit_memos AS c
INNER JOIN invoices AS i
        ON c.doc_no = i.doc_no
        AND c.customer_code = i.customer_code
        AND i.trx_type = '1111'
WHERE i.date_applied >= '734869'
AND c.date_applied - i.date_applied >= 21 -- assuming these are integers
AND c.trx_type = '2222'

Select allOpen in new window

{+ an edit}

date applied is a julian date

the credit memos and invoices are in the one table

You may have to provide some sample data from that table that represents what you are trying to do, & ideally also provide the DDL to create the table.

SELECT distinct
        c.*
FROM  ARTABLE AS c
INNER JOIN ARTABLE AS i
        ON c.customer_code = i.customer_code
        AND i.trx_type = '1111'
WHERE i.date_applied >= '734869'
AND c.date_applied - i.date_applied >= 21  
AND c.trx_type = '2222'
and c.amt_net = '352'

no with this query I am not getting the correct data back.
invoices and credit memos are held in the same table, they use the same column date_applied
the only thing that tells them apart is transaction type and transaction control number.
transaction type is good enough

The credit memo amount I am looking for is $352, and it is being aplied after 21 days after invoice date applied.

Credit memos should automatically generate within 21 days if payment has been paid in full and applied against the invoice.

I am seeing a few where payment is made after the 21 days and credit memo is still going out.

The report is showing me a few of these but it is also showing me payments made within the 21 days ie paid in full.

ahh this query is giving me the data now

SELECT
        c.*
FROM MyTable AS c      --<< same table
INNER JOIN (
                SELECT
                        doc_no
                        , customer_code
                        , date_applied
                FROM MyTable                --<< same table
                WHERE trx_type = '1111'
                AND date_applied >= '734869'
           ) AS i
        ON c.doc_no = i.doc_no
        AND c.customer_code = i.customer_code
WHERE c.trx_type = '2222'
AND c.date_applied - i.date_applied >= 21 -- integers (Julian dates)
;

a sample of your data would greatly assist I am guessing my way through this

I'm more than willing to help - but I do need your assistance as I don't know your data model or how an invoice relates to a credit memo in that table

invoices and credit memos are held in the same table (yes, in the query suggested)
they use the same column date_applied (yes, in the query suggested)
the only thing that tells them apart is transaction type (yes, in the query suggested)
and transaction control number. (NOT in the query suggested)

transaction type is good enough (thanks, but tell me more about your data please)

e.g. what is the field name for "transaction control number"?
has this been included in any of the queries so far?

Flyfishtrout15

I'm confused.
Have you managed to get the invoice to credit-memo working?

>>ahh this query is giving me the data now

this is very strange, but I was not seeing all your most recent posts...

apologies if I've confused you - but it seems you do now have the solution

Thanks, Paul.