comparing dates in a table by a transaction type

I have to compare invoice date and credit memo apply date, what I am looking for is where credit memos have been raised after 21 days from the invoice date apply.

should I be using a compare dates syntax..
Amanda WalshawBusiness Solutions AnalsystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
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
0
Amanda WalshawBusiness Solutions AnalsystAuthor Commented:
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
0
PortletPaulfreelancerCommented:
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.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

PortletPaulfreelancerCommented:
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'

Open in new window

{+ an edit}
0
Amanda WalshawBusiness Solutions AnalsystAuthor Commented:
date applied is a julian date
0
Amanda WalshawBusiness Solutions AnalsystAuthor Commented:
the credit memos and invoices are in the one table
0
PortletPaulfreelancerCommented:
>>date applied is a julian date
small but important piece of info, datediff() applies to date/datetime/datetime2/time only

Does this work for you?:
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)
;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
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.
0
Amanda WalshawBusiness Solutions AnalsystAuthor Commented:
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.
0
Amanda WalshawBusiness Solutions AnalsystAuthor Commented:
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)
;
0
PortletPaulfreelancerCommented:
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?
0
PortletPaulfreelancerCommented:
Flyfishtrout15

I'm confused.
Have you managed to get the invoice to credit-memo working?
0
PortletPaulfreelancerCommented:
>>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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.