SQL Help
Thread Starter
Bent = #1
Joined: Dec 2001
Posts: 13,473
Likes: 25
From: Marietta, GA
SQL Help
Each Vendor has a Vendor ID & Tax ID. I was able to query it so it would display any vendor record with duplicate Tax IDs in the system.
I'm trying to get the last payment date for each vendor by using MAX(). This works, but if a vendor with a duplicate Tax ID has never been paid it won't show up because the MAX() treats that as nothing.
How do I have the code include the payment date if no payment has ever been made? It's an Oracle server... Thanks!
I'm trying to get the last payment date for each vendor by using MAX(). This works, but if a vendor with a duplicate Tax ID has never been paid it won't show up because the MAX() treats that as nothing.
How do I have the code include the payment date if no payment has ever been made? It's an Oracle server... Thanks!
Code:
SELECT V.VEN_ID, V.NAME, V.FED_ID, V.VEN_STAT_CD, APMT.PAID_DATE, APMT.FORM_ID FROM VENDOR V LEFT JOIN AP_PH_BASE APMT ON V.VEN_ID = APMT.VEN_ID WHERE V.FED_ID IN ( SELECT V2.FED_ID FROM VENDOR V2 GROUP BY V2.FED_ID HAVING COUNT(V2.FED_ID) > 1 ) AND APMT.PAID_DATE = ( SELECT MAX(APMT2.PAID_DATE) FROM AP_PH_BASE APMT2 WHERE (APMT2.VEN_ID=APMT.VEN_ID) ) GROUP BY V.FED_ID, V.VEN_ID, V.NAME, V.VEN_STAT_CD, APMT.PAID_DATE, APMT.FORM_ID
Thread Starter
Bent = #1
Joined: Dec 2001
Posts: 13,473
Likes: 25
From: Marietta, GA
For instance.. I have a list of all Vendors with duplicate Tax IDs. However, when I try to match the vendors with their last payment, and they've never been paid around 80-100 of those disappear. Since they have no payment I'd like the records to still be displayed, but have nothing for the Tax ID.
I have a hard time writing code for tables I can't see and test with but almost seems like a virtual table and another left outer might work. See if this does anything.
SELECT V.VEN_ID
, V.NAME
, V.FED_ID
, V.VEN_STAT_CD
, APMT.PAID_DATE
, APMT.FORM_ID
FROM VENDOR V
LEFT JOIN AP_PH_BASE APMT ON V.VEN_ID = APMT.VEN_ID
LEFT JOIN (SELECT VEN_ID, MAX(APMT2.PAID_DATE) as MaxPayDate FROM AP_PH_BASE GROUP BY VEN_ID) APMT2 ON APMT2.VEN_ID = APMT.VEN_ID AND APMT.PAID_DATE = APMT2.MaxPayDate
WHERE V.FED_ID IN (SELECT V2.FED_ID
FROM VENDOR V2
GROUP BY V2.FED_ID
HAVING COUNT(V2.FED_ID) > 1)
GROUP
BY V.FED_ID
, V.VEN_ID
, V.NAME
, V.VEN_STAT_CD
, APMT.PAID_DATE
, APMT.FORM_ID
SELECT V.VEN_ID
, V.NAME
, V.FED_ID
, V.VEN_STAT_CD
, APMT.PAID_DATE
, APMT.FORM_ID
FROM VENDOR V
LEFT JOIN AP_PH_BASE APMT ON V.VEN_ID = APMT.VEN_ID
LEFT JOIN (SELECT VEN_ID, MAX(APMT2.PAID_DATE) as MaxPayDate FROM AP_PH_BASE GROUP BY VEN_ID) APMT2 ON APMT2.VEN_ID = APMT.VEN_ID AND APMT.PAID_DATE = APMT2.MaxPayDate
WHERE V.FED_ID IN (SELECT V2.FED_ID
FROM VENDOR V2
GROUP BY V2.FED_ID
HAVING COUNT(V2.FED_ID) > 1)
GROUP
BY V.FED_ID
, V.VEN_ID
, V.NAME
, V.VEN_STAT_CD
, APMT.PAID_DATE
, APMT.FORM_ID
Just had another idea as well. in this part of the code,
AND APMT.PAID_DATE = (
SELECT MAX(APMT2.PAID_DATE)
FROM AP_PH_BASE APMT2
WHERE (APMT2.VEN_ID=APMT.VEN_ID)
Are you saying it fails because there is no paid_date in the AP_PH_Base table? is it a NULL? If so, you could do an isnull(). Well I say that, thats a MSSQL command but i'd imagine oracle should having something like it. Lets say the field paid_date is NULL, in MSSQL you would do...
AND ISNULL(APMT.PAID_DATE,'') = (
SELECT ISNULL(MAX(APMT2.PAID_DATE),'')
FROM AP_PH_BASE APMT2
WHERE (APMT2.VEN_ID=APMT.VEN_ID)
AND APMT.PAID_DATE = (
SELECT MAX(APMT2.PAID_DATE)
FROM AP_PH_BASE APMT2
WHERE (APMT2.VEN_ID=APMT.VEN_ID)
Are you saying it fails because there is no paid_date in the AP_PH_Base table? is it a NULL? If so, you could do an isnull(). Well I say that, thats a MSSQL command but i'd imagine oracle should having something like it. Lets say the field paid_date is NULL, in MSSQL you would do...
AND ISNULL(APMT.PAID_DATE,'') = (
SELECT ISNULL(MAX(APMT2.PAID_DATE),'')
FROM AP_PH_BASE APMT2
WHERE (APMT2.VEN_ID=APMT.VEN_ID)
Last edited by rza49311; Nov 16, 2010 at 11:31 AM.
Trending Topics
Thread Starter
Bent = #1
Joined: Dec 2001
Posts: 13,473
Likes: 25
From: Marietta, GA
I have a hard time writing code for tables I can't see and test with but almost seems like a virtual table and another left outer might work. See if this does anything.
SELECT V.VEN_ID
, V.NAME
, V.FED_ID
, V.VEN_STAT_CD
, APMT.PAID_DATE
, APMT.FORM_ID
FROM VENDOR V
LEFT JOIN AP_PH_BASE APMT ON V.VEN_ID = APMT.VEN_ID
LEFT JOIN (SELECT VEN_ID, MAX(APMT2.PAID_DATE) as MaxPayDate FROM AP_PH_BASE GROUP BY VEN_ID) APMT2 ON APMT2.VEN_ID = APMT.VEN_ID AND APMT.PAID_DATE = APMT2.MaxPayDate
WHERE V.FED_ID IN (SELECT V2.FED_ID
FROM VENDOR V2
GROUP BY V2.FED_ID
HAVING COUNT(V2.FED_ID) > 1)
GROUP
BY V.FED_ID
, V.VEN_ID
, V.NAME
, V.VEN_STAT_CD
, APMT.PAID_DATE
, APMT.FORM_ID
SELECT V.VEN_ID
, V.NAME
, V.FED_ID
, V.VEN_STAT_CD
, APMT.PAID_DATE
, APMT.FORM_ID
FROM VENDOR V
LEFT JOIN AP_PH_BASE APMT ON V.VEN_ID = APMT.VEN_ID
LEFT JOIN (SELECT VEN_ID, MAX(APMT2.PAID_DATE) as MaxPayDate FROM AP_PH_BASE GROUP BY VEN_ID) APMT2 ON APMT2.VEN_ID = APMT.VEN_ID AND APMT.PAID_DATE = APMT2.MaxPayDate
WHERE V.FED_ID IN (SELECT V2.FED_ID
FROM VENDOR V2
GROUP BY V2.FED_ID
HAVING COUNT(V2.FED_ID) > 1)
GROUP
BY V.FED_ID
, V.VEN_ID
, V.NAME
, V.VEN_STAT_CD
, APMT.PAID_DATE
, APMT.FORM_ID
LEFT JOIN (SELECT VEN_ID, MAX(APMT2.PAID_DATE) as MaxPayDate FROM AP_PH_BASE GROUP BY VEN_ID) APMT2 ON APMT2.VEN_ID = APMT.VEN_ID AND APMT.PAID_DATE = APMT2.MaxPayDate
I see an error, should be unless you changed that already
LEFT JOIN (SELECT VEN_ID, MAX(PAID_DATE) as MaxPayDate FROM AP_PH_BASE GROUP BY VEN_ID) APMT2 ON APMT2.VEN_ID = APMT.VEN_ID AND APMT.PAID_DATE = APMT2.MaxPayDate
I had the alias inside the select where there was no alias yet.
I see an error, should be unless you changed that already
LEFT JOIN (SELECT VEN_ID, MAX(PAID_DATE) as MaxPayDate FROM AP_PH_BASE GROUP BY VEN_ID) APMT2 ON APMT2.VEN_ID = APMT.VEN_ID AND APMT.PAID_DATE = APMT2.MaxPayDate
I had the alias inside the select where there was no alias yet.
I'm using all MSSQL, does Oracle support the AS command?
Might need to just remove that perhaps?
SELECT VEN_ID, MAX(PAID_DATE) as MaxPayDate FROM AP_PH_BASE GROUP BY VEN_ID
SELECT VEN_ID, MAX(PAID_DATE) MaxPayDate FROM AP_PH_BASE GROUP BY VEN_ID
Might need to just remove that perhaps?
SELECT VEN_ID, MAX(PAID_DATE) as MaxPayDate FROM AP_PH_BASE GROUP BY VEN_ID
SELECT VEN_ID, MAX(PAID_DATE) MaxPayDate FROM AP_PH_BASE GROUP BY VEN_ID
Also, the more I think about it, that second left join would probably give you both records if it does work since you are still displaying APMT.PAID_DATE in the select.
If the query does work, you would need to replace APMT.PAID_DATE in the resulting select and the group by to APMT2.MaxPayDate instead.
PS. I may be wasting your time since I don't know Oracle, just tell me to shut it if I'm hurting more than helping
If the query does work, you would need to replace APMT.PAID_DATE in the resulting select and the group by to APMT2.MaxPayDate instead.
PS. I may be wasting your time since I don't know Oracle, just tell me to shut it if I'm hurting more than helping
Thread Starter
Bent = #1
Joined: Dec 2001
Posts: 13,473
Likes: 25
From: Marietta, GA
Also, the more I think about it, that second left join would probably give you both records if it does work since you are still displaying APMT.PAID_DATE in the select.
If the query does work, you would need to replace APMT.PAID_DATE in the resulting select and the group by to APMT2.MaxPayDate instead.
PS. I may be wasting your time since I don't know Oracle, just tell me to shut it if I'm hurting more than helping
If the query does work, you would need to replace APMT.PAID_DATE in the resulting select and the group by to APMT2.MaxPayDate instead.
PS. I may be wasting your time since I don't know Oracle, just tell me to shut it if I'm hurting more than helping
Tried your solutions and nothing
Thread Starter
Bent = #1
Joined: Dec 2001
Posts: 13,473
Likes: 25
From: Marietta, GA
AHHHHH figured it out... The vendors that weren't showing up had no accounts payable entered which means they weren't in the AP_PH_BASE table...
*bangs head into desk several times*
Thanks for the help rza..
*bangs head into desk several times*
Thanks for the help rza..
Thread Starter
Bent = #1
Joined: Dec 2001
Posts: 13,473
Likes: 25
From: Marietta, GA
RZA.. if the following shows all vendors with duplicate TAX_ID that have an invoice entered for the vendor..
What would I do to get the vendors with duplicate TAX_ID that DON'T have an invoice entered for the vendor..
Code:
SELECT V.VEN_ID, V.NAME, V.FED_ID, V.VEN_STAT_CD, APMT.PAID_DATE, APMT.FORM_ID FROM VENDOR V LEFT JOIN AP_PH_BASE APMT ON V.VEN_ID = APMT.VEN_ID WHERE V.FED_ID IN ( SELECT V2.FED_ID FROM VENDOR V2 GROUP BY V2.FED_ID HAVING COUNT(V2.FED_ID) > 1 ) AND APMT.PAID_DATE = ( SELECT MAX(APMT2.PAID_DATE) FROM AP_PH_BASE APMT2 WHERE (APMT2.VEN_ID=APMT.VEN_ID) ) GROUP BY V.FED_ID, V.VEN_ID, V.NAME, V.VEN_STAT_CD, APMT.PAID_DATE, APMT.FORM_ID
Thread Starter
Bent = #1
Joined: Dec 2001
Posts: 13,473
Likes: 25
From: Marietta, GA
Nevermind got it..
Code:
SELECT V.VEN_ID, V.NAME, V.FED_ID, V.VEN_STAT_CD, APMT.PAID_DATE, APMT.FORM_ID FROM VENDOR V LEFT JOIN AP_PH_BASE APMT ON V.VEN_ID = APMT.VEN_ID WHERE V.FED_ID IN ( SELECT V2.FED_ID FROM VENDOR V2 GROUP BY V2.FED_ID HAVING COUNT(V2.FED_ID) > 1 ) AND APMT.PAID_DATE IS NULL GROUP BY V.FED_ID, V.VEN_ID, V.NAME, V.VEN_STAT_CD, APMT.PAID_DATE, APMT.FORM_ID
Thread Starter
Bent = #1
Joined: Dec 2001
Posts: 13,473
Likes: 25
From: Marietta, GA
Thread Starter
Bent = #1
Joined: Dec 2001
Posts: 13,473
Likes: 25
From: Marietta, GA
Trying to create an agings report for our receivables... I've tried everything IMO and searched forever, but can't figure it out.
I need the following columns based on the same Date Field.. Current, 31-60, 61-90, Over 90. Can anyone help with what the SQL query should look like?
I need the following columns based on the same Date Field.. Current, 31-60, 61-90, Over 90. Can anyone help with what the SQL query should look like?
Man am I glad Quickbooks does this for us...
Thread Starter
Bent = #1
Joined: Dec 2001
Posts: 13,473
Likes: 25
From: Marietta, GA
Yeah I don't even want to go into it, but our software already has a report that does this, but it's crap. Our software program is crap. I have been given access to the database so I'm free to create anything and everything I want to make the Accounting department's jobs easier.
I've gotten a couple files done, but I just can't figure out the damn Agings Receivable one! I have a work around, but I know it's not the correct thing to do.
I've gotten a couple files done, but I just can't figure out the damn Agings Receivable one! I have a work around, but I know it's not the correct thing to do.
Safety Car
Joined: Jul 2007
Posts: 4,845
Likes: 145
Without knowing the tables and columns I can only point you in the right direction.
Your columns will be something like:
So what that's doing is summing all the invoices per column but if the invoice date is not between the specific day range, it substitutes zero and doesn't affect the total.
From there it's just a matter of doing the proper GROUP BY to get the rows in the increment or group that you want.
Your columns will be something like:
Code:
sum(case when datediff(dd,invoiceDate,getdate()) between 1 and 30 then invoiceAmount else 0 end), sum(case when datediff(dd,invoiceDate,getdate()) between 31 and 60 then invoiceAmount else 0 end), sum(case when datediff(dd,invoiceDate,getdate()) between 61 and 90 then invoiceAmount else 0 end)
From there it's just a matter of doing the proper GROUP BY to get the rows in the increment or group that you want.
Thread Starter
Bent = #1
Joined: Dec 2001
Posts: 13,473
Likes: 25
From: Marietta, GA
I'll give it a try tomorrow.. I messed around with cases today, but didn't have any luck. From what I read datediff doesn't work with Oracle, but I know what does. My cases looked a little different though, so I'll give it a shot tomorrow. Thanks.
Safety Car
Joined: Jul 2007
Posts: 4,845
Likes: 145
Looks like in Oracle, you just subtract the two dates to get the number of days between them. Sorry about using getdate() for current date since that's T-SQL as well, but I'm sure you know the equivalent by now.
The concept is what you want to go for: a series of columns that sum up part of the invoice amounts based on varying date ranges.
The concept is what you want to go for: a series of columns that sum up part of the invoice amounts based on varying date ranges.
Thread Starter
Bent = #1
Joined: Dec 2001
Posts: 13,473
Likes: 25
From: Marietta, GA
Looks like in Oracle, you just subtract the two dates to get the number of days between them. Sorry about using getdate() for current date since that's T-SQL as well, but I'm sure you know the equivalent by now.
The concept is what you want to go for: a series of columns that sum up part of the invoice amounts based on varying date ranges.
The concept is what you want to go for: a series of columns that sum up part of the invoice amounts based on varying date ranges.
Thread Starter
Bent = #1
Joined: Dec 2001
Posts: 13,473
Likes: 25
From: Marietta, GA
This is what I had that worked, but wasn't 100% accurate.
Code:
SELECT AR.JOB_ID, AR.DISTR_DATE, AR.CUS_INVOICE_NUM, (SELECT SUM(AR2.AMT) FROM PTI_PROD.AR_BH_BASE AR2 WHERE (AR.JOB_ID=AR2.JOB_ID) AND (AR.DISTR_DATE=AR2.DISTR_DATE) AND (AR2.DISTR_DATE > '2010/10/31') AND (AR2.DISTR_DATE <= '2010/11/30')), (SELECT SUM(AR3.AMT) FROM PTI_PROD.AR_BH_BASE AR3 WHERE (AR.JOB_ID=AR3.JOB_ID) AND (AR.DISTR_DATE=AR3.DISTR_DATE) AND (AR3.DISTR_DATE > '2010/10/01') AND (AR3.DISTR_DATE <= '2010/10/31')), (SELECT SUM(AR4.AMT) FROM PTI_PROD.AR_BH_BASE AR4 WHERE (AR.JOB_ID=AR4.JOB_ID) AND (AR.DISTR_DATE=AR4.DISTR_DATE) AND (AR4.DISTR_DATE > '2010/09/01') AND (AR4.DISTR_DATE <= '2010/10/01')), (SELECT SUM(AR5.AMT) FROM PTI_PROD.AR_BH_BASE AR5 WHERE (AR.JOB_ID=AR5.JOB_ID) AND (AR.DISTR_DATE=AR5.DISTR_DATE) AND (AR5.DISTR_DATE <= '2010/09/01')) FROM PTI_PROD.AR_BH_BASE AR WHERE (AR.AR_TYPE_CD='J') AND (AR.AR_STAT_CD='A') AND (AR.DR_OU_ID='2035') GROUP BY AR.JOB_ID, AR.DISTR_DATE, AR.CUS_INVOICE_NUM ORDER BY AR.JOB_ID
Thread Starter
Bent = #1
Joined: Dec 2001
Posts: 13,473
Likes: 25
From: Marietta, GA
Without knowing the tables and columns I can only point you in the right direction.
Your columns will be something like:
So what that's doing is summing all the invoices per column but if the invoice date is not between the specific day range, it substitutes zero and doesn't affect the total.
From there it's just a matter of doing the proper GROUP BY to get the rows in the increment or group that you want.
Your columns will be something like:
Code:
sum(case when datediff(dd,invoiceDate,getdate()) between 1 and 30 then invoiceAmount else 0 end), sum(case when datediff(dd,invoiceDate,getdate()) between 31 and 60 then invoiceAmount else 0 end), sum(case when datediff(dd,invoiceDate,getdate()) between 61 and 90 then invoiceAmount else 0 end)
From there it's just a matter of doing the proper GROUP BY to get the rows in the increment or group that you want.
Safety Car
Joined: Jul 2007
Posts: 4,845
Likes: 145
Do you need to alias the column? Obviously my solution is not Oracle-compatible, but you should be able to do a CASE in a SUM
Maybe Oracle doesn't like BETWEEN. You could try it with > and <
Maybe Oracle doesn't like BETWEEN. You could try it with > and <
Last edited by Anachostic; Dec 8, 2010 at 08:00 PM.
Racer
Joined: Nov 2003
Posts: 376
Likes: 0
From: Pasadena, CA
Added to_date function.
Code:
SELECT AR.JOB_ID, AR.DISTR_DATE, AR.CUS_INVOICE_NUM,
(SELECT SUM(AR2.AMT) FROM PTI_PROD.AR_BH_BASE AR2 WHERE (AR.JOB_ID=AR2.JOB_ID) AND (AR.DISTR_DATE=AR2.DISTR_DATE) AND (AR2.DISTR_DATE > to_date('2010/10/31','YYYY/MM/DD')) AND (AR2.DISTR_DATE <= to_date('2010/11/30','YYYY/MM/DD'))),
(SELECT SUM(AR3.AMT) FROM PTI_PROD.AR_BH_BASE AR3 WHERE (AR.JOB_ID=AR3.JOB_ID) AND (AR.DISTR_DATE=AR3.DISTR_DATE) AND (AR3.DISTR_DATE > to_date('2010/10/01','YYYY/MM/DD')) AND (AR3.DISTR_DATE <= to_date('2010/10/31','YYYY/MM/DD'))),
(SELECT SUM(AR4.AMT) FROM PTI_PROD.AR_BH_BASE AR4 WHERE (AR.JOB_ID=AR4.JOB_ID) AND (AR.DISTR_DATE=AR4.DISTR_DATE) AND (AR4.DISTR_DATE > to_date('2010/09/01','YYYY/MM/DD')) AND (AR4.DISTR_DATE <= to_date('2010/10/01','YYYY/MM/DD'))),
(SELECT SUM(AR5.AMT) FROM PTI_PROD.AR_BH_BASE AR5 WHERE (AR.JOB_ID=AR5.JOB_ID) AND (AR.DISTR_DATE=AR5.DISTR_DATE) AND (AR5.DISTR_DATE <= to_date('2010/09/01','YYYY/MM/DD')))
FROM PTI_PROD.AR_BH_BASE AR
WHERE (AR.AR_TYPE_CD='J') AND (AR.AR_STAT_CD='A') AND (AR.DR_OU_ID='2035')
GROUP BY AR.JOB_ID, AR.DISTR_DATE, AR.CUS_INVOICE_NUM
ORDER BY AR.JOB_ID
Can you post how you applied this code to the query? I would be using this like Anachostic mentioned but you would need to make sure your FROM statement is correct. I wouldn't be using subquery's in the select for this code.
Thread
Thread Starter
Forum
Replies
Last Post