Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help Newbie needs HELP

Author  Topic 

IMZSHAH
Starting Member

12 Posts

Posted - 2014-10-13 : 09:40:21
Hi All,

I'm a novice when it comes to sql programming and hoping someone can help me with my scenario. I have produced an aged debtors report which worked fine. However I have been asked to add revaluations in the month. This is the problem, now that I had joined the "GeneralLedger" table that anything in my base currency which obviously is not re-valued is being excluded. I want transactions in base as well as foreign to be included.

My coding is below:

SELECT InvcHead.Company, InvcHead.InvoiceNum AS [Invoice Number],
InvcHead.CurrencyCode AS Currency,
CONVERT(VARCHAR, InvcHead.InvoiceDate, 101) AS [Invoice Date],
CONVERT(VARCHAR, InvcHead.DueDate, 101) AS [Due Date],
DATEDIFF(dd, InvcHead.DueDate, GETDATE()) AS [Days Over],
InvcHead.UnpostedBal AS [Invoice Bal],
Customer.Name AS Customer,
Currency_table.CurrentRate,
CASE WHEN DATEDIFF(dd, duedate, GETDATE()) <= 0 THEN UnpostedBal + GLJrnDtl.BookCreditAmount + GLJrnDtl.BookDebitAmount * -1 ELSE 0 END AS FUTURE,
CASE WHEN DATEDIFF(dd, duedate, GETDATE()) > 1 AND DATEDIFF(dd, duedate, GETDATE()) <= 30 THEN UnpostedBal + GLJrnDtl.BookCreditAmount + GLJrnDtl.BookDebitAmount * -1 ELSE 0 END AS [CURRENT],
CASE WHEN datediff(dd, duedate, GETDATE()) > 30 AND DATEDIFF(dd, duedate, GETDATE()) <= 60 THEN UnpostedBal + GLJrnDtl.BookCreditAmount + GLJrnDtl.BookDebitAmount * -1 ELSE 0 END AS [Over 30],
CASE WHEN datediff(dd, duedate, GETDATE()) > 60 AND DATEDIFF(dd, duedate, GETDATE()) <= 90 THEN UnpostedBal + GLJrnDtl.BookCreditAmount + GLJrnDtl.BookDebitAmount * -1 ELSE 0 END AS [Over 60],
CASE WHEN datediff(dd, invoicedate, GETDATE()) > 90 AND DATEDIFF(dd, invoicedate, GETDATE())<= 120 THEN InvcHead.UnpostedBal + GLJrnDtl.BookCreditAmount + GLJrnDtl.BookDebitAmount * -1 ELSE 0 END AS [Over 90],
CASE WHEN datediff(dd, duedate, GETDATE()) > 120 AND DATEDIFF(dd, duedate, GETDATE()) <= 1000 THEN InvcHead.UnpostedBal + GLJrnDtl.BookCreditAmount + GLJrnDtl.BookDebitAmount * -1 ELSE 0 END AS [Over 120],
GLJrnDtl.BookCreditAmount + GLJrnDtl.BookDebitAmount * -1 AS [Gain/(Loss) in Period]
FROM InvcHead INNER JOIN Customer ON (InvcHead.CustNum = Customer.CustNum)
INNER JOIN CurrExRate AS Currency_table ON (InvcHead.CurrencyCode = Currency_table.TargetCurrCode)
INNER JOIN GLJrnDtl ON (InvcHead.InvoiceNum = GLJrnDtl.ARInvoiceNum) AND(GLJrnDtl.Company = InvcHead.Company)
AND (InvcHead.Company = Customer.Company) AND (Currency_table.Company = InvcHead.company)
WHERE
(InvcHead.OpenInvoice = '1') AND
(InvcHead.Posted = '1') AND
Customer.CustID NOT LIKE '100034' AND
Customer.CustID NOT LIKE'9%' AND
Gljrndtl.FiscalYear ='2014' AND
Gljrndtl.FiscalPeriod ='9' AND
GLJrnDtl.Description like 'rev%' AND
(JEDate IN (@GL_date)) AND
(SourceModule IN (@Source)) AND
(effectivedate IN (@date)) AND
(JournalLine IN (@Line))AND
(Reverse IN (@Reverse))
GROUP BY InvcHead.Company,
InvcHead.InvoiceNum,
InvcHead.CurrencyCode,
InvcHead.InvoiceDate,
InvcHead.DueDate,
InvcHead.ExchangeRate,
InvcHead.UnpostedBal,
Customer.Name,
Currency_table.CurrentRate,
GLJrnDtl.BookCreditAmount,
GLJrnDtl.BookDebitAmount
ORDER BY InvcHead.Company,
Customer,
[Invoice Number]

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-13 : 10:15:26
would you please post an example of what you get today with this query and what you really want to see?
Go to Top of Page

IMZSHAH
Starting Member

12 Posts

Posted - 2014-10-13 : 10:43:13
Sure thing, how can I upload an attachment please?
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-13 : 10:54:45
just a few lines would be enough
Go to Top of Page

IMZSHAH
Starting Member

12 Posts

Posted - 2014-10-13 : 12:32:20
Sure,

When I run the query, the report will give me information of outstanding debts owed by customers grouped by aged date, whether the invoice is future dated, whether it is current, over due by 30,60 days and son on. I've done this for all our group companies and it's worked fine, I'm able to reconcile to the system without issue. The problem is the report is meant to be run at the end of the month which should take into account foreign exchange gains or losses. This is on another table, once I've joined this table anything that hasn't been revalued i.e. base currency invoices are excluded.

I want the report to show base currency invoices as well as invoices that have been revalued.

Thanks

Imran
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-13 : 12:39:20
OK, but I want to see a few rows of what you get with your query and a few rows of what you want (using the same data)
Go to Top of Page

IMZSHAH
Starting Member

12 Posts

Posted - 2014-10-14 : 04:16:09
1st report, excluding revaluations shows all currency invoices due, take the company "spbene", you can see euro and usd invoices.


Second report, addition of new table and variables to include revaluations for the month now excludes base currency invoices. Take the same company "spbene", all euro invoices have now been excluded as euro is the base currency.


Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-14 : 08:37:45
Try changing

INNER JOIN GLJrnDtl

to

LEFT JOIN

This will return NULL for BookCreditAmount when there is no matching row, but you can test for that.

BTW, reformatted your SQL for (my) readability (I used poorsql.com) and simplified it a bit. Check this out:


SELECT InvcHead.Company
,InvcHead.InvoiceNum AS [Invoice Number]
,InvcHead.CurrencyCode AS Currency
,CONVERT(VARCHAR, InvcHead.InvoiceDate, 101) AS [Invoice Date]
,CONVERT(VARCHAR, InvcHead.DueDate, 101) AS [Due Date]
,DATEDIFF(dd, InvcHead.DueDate, GETDATE()) AS [Days Over]
,InvcHead.UnpostedBal AS [Invoice Bal]
,Customer.NAME AS Customer
,Currency_table.CurrentRate
,CASE
WHEN DATEDIFF(dd, duedate, GETDATE()) <= 0
THEN OverDue.amt
ELSE 0
END AS FUTURE
,CASE
WHEN DATEDIFF(dd, duedate, GETDATE()) > 1
AND DATEDIFF(dd, duedate, GETDATE()) <= 30
THEN OverDue.amt
ELSE 0
END AS [CURRENT]
,CASE
WHEN datediff(dd, duedate, GETDATE()) > 30
AND DATEDIFF(dd, duedate, GETDATE()) <= 60
THEN OverDue.amt
ELSE 0
END AS [Over 30]
,CASE
WHEN datediff(dd, duedate, GETDATE()) > 60
AND DATEDIFF(dd, duedate, GETDATE()) <= 90
THEN OverDue.amt
ELSE 0
END AS [Over 60]
,CASE
WHEN datediff(dd, invoicedate, GETDATE()) > 90
AND DATEDIFF(dd, invoicedate, GETDATE()) <= 120
THEN OverDue.amt
ELSE 0
END AS [Over 90]
,CASE
WHEN datediff(dd, duedate, GETDATE()) > 120
AND DATEDIFF(dd, duedate, GETDATE()) <= 1000
THEN OverDue.amt
ELSE 0
END AS [Over 120]
,OverDue.gain_loss AS [Gain/(Loss) in Period]
FROM InvcHead

-- Compute overdue amount and gain/loss
CROSS APPLY (
SELECT UnpostedBal + GLJrnDtl.BookCreditAmount + GLJrnDtl.BookDebitAmount * -1
, GLJrnDtl.BookCreditAmount + GLJrnDtl.BookDebitAmount * - 1
) as OverDue(amt, gain_loss)
--

INNER JOIN Customer ON (InvcHead.CustNum = Customer.CustNum)
INNER JOIN CurrExRate AS Currency_table ON (InvcHead.CurrencyCode = Currency_table.TargetCurrCode
)
INNER JOIN GLJrnDtl ON (InvcHead.InvoiceNum = GLJrnDtl.ARInvoiceNum)
AND (GLJrnDtl.Company = InvcHead.Company)
AND (InvcHead.Company = Customer.Company)
AND (Currency_table.Company = InvcHead.company)
WHERE (InvcHead.OpenInvoice = '1')
AND (InvcHead.Posted = '1')
AND Customer.CustID NOT LIKE '100034'
AND Customer.CustID NOT LIKE '9%'
AND Gljrndtl.FiscalYear = '2014'
AND Gljrndtl.FiscalPeriod = '9'
AND GLJrnDtl.Description LIKE 'rev%'
AND (JEDate IN (@GL_date))
AND (SourceModule IN (@Source))
AND (effectivedate IN (@date))
AND (JournalLine IN (@Line))
AND (Reverse IN (@Reverse))
GROUP BY InvcHead.Company
,InvcHead.InvoiceNum
,InvcHead.CurrencyCode
,InvcHead.InvoiceDate
,InvcHead.DueDate
,InvcHead.ExchangeRate
,InvcHead.UnpostedBal
,Customer.NAME
,Currency_table.CurrentRate
,GLJrnDtl.BookCreditAmount
,GLJrnDtl.BookDebitAmount
ORDER BY InvcHead.Company
,Customer
,[Invoice Number]
Go to Top of Page

IMZSHAH
Starting Member

12 Posts

Posted - 2014-10-14 : 08:44:08
Thank you, I tried the left join to no avail, this is like hitting my head against a brick wall.
Go to Top of Page

IMZSHAH
Starting Member

12 Posts

Posted - 2014-10-14 : 08:45:16
Thank you for simplyfying the code.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-14 : 09:10:18
using a Left join instead of an Inner join on GLJrnDtl (That's the only addition to the original query, right?) will preserve non-matching rows. Check your output. The "missing" invoice from the second query should reappear if you change INNER to LEFT. If it does not reappear, then you have modified the original query in some other way Do a side-by-side eye check and highlight any differences. then, look at those carefully to determine the effect of the changes
Go to Top of Page

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-10-14 : 09:22:10
You should also place any reference to GLJrnDtl in the WHERE clause into the JOIN or else your LEFT JOIN will effectively to turned into an INNER JOIN.

http://stackoverflow.com/questions/3256304/left-join-turns-into-inner-join

eg remove the follwoing from the WHERE clause:

AND Gljrndtl.FiscalYear = '2014'
AND Gljrndtl.FiscalPeriod = '9'
AND GLJrnDtl.Description LIKE 'rev%'


and change the JOIN to:

LEFT JOIN GLJrnDtl
ON InvcHead.InvoiceNum = GLJrnDtl.ARInvoiceNum
AND GLJrnDtl.Company = InvcHead.Company
AND InvcHead.Company = Customer.Company
AND Currency_table.Company = InvcHead.company
AND Gljrndtl.FiscalYear = '2014'
AND Gljrndtl.FiscalPeriod = '9'
AND GLJrnDtl.Description LIKE 'rev%'


In future, as SQL is basically about transforming sets, you will get quicker and better answers if you post consumable test data with expected results.
Go to Top of Page

IMZSHAH
Starting Member

12 Posts

Posted - 2014-10-14 : 09:28:02
They only reappear when I do a little reverse engineering on the filters in the where clause.

I have now got the foreign currency invoices as well as base currency but now all foreign currency invoices are being duplicated.
Go to Top of Page

IMZSHAH
Starting Member

12 Posts

Posted - 2014-10-14 : 09:50:18
Thank you so much resolved.
Go to Top of Page

IMZSHAH
Starting Member

12 Posts

Posted - 2014-10-14 : 13:57:18
It seemed to good to be true but now everything in base where I have applied the following to get the calculations for revaluations is showing as a null balance, any suggestions?

CASE WHEN DATEDIFF(dd, duedate, GETDATE()) <= 0 THEN UnpostedBal + GLJrnDtl.BookCreditAmount + GLJrnDtl.BookDebitAmount * -1 ELSE 0 END AS FUTURE,
CASE WHEN DATEDIFF(dd, duedate, GETDATE()) > 1 AND DATEDIFF(dd, duedate, GETDATE()) <= 30 THEN UnpostedBal + GLJrnDtl.BookCreditAmount + GLJrnDtl.BookDebitAmount * -1 ELSE 0 END AS [CURRENT],
CASE WHEN datediff(dd, duedate, GETDATE()) > 30 AND DATEDIFF(dd, duedate, GETDATE()) <= 60 THEN UnpostedBal + GLJrnDtl.BookCreditAmount + GLJrnDtl.BookDebitAmount * -1 ELSE 0 END AS [Over 30],
CASE WHEN datediff(dd, duedate, GETDATE()) > 60 AND DATEDIFF(dd, duedate, GETDATE()) <= 90 THEN UnpostedBal + GLJrnDtl.BookCreditAmount + GLJrnDtl.BookDebitAmount * -1 ELSE 0 END AS [Over 60],
CASE WHEN datediff(dd, invoicedate, GETDATE()) > 90 AND DATEDIFF(dd, invoicedate, GETDATE()) <= 120 THEN InvcHead.UnpostedBal + GLJrnDtl.BookCreditAmount + GLJrnDtl.BookDebitAmount * -1 ELSE 0 END AS [Over 90],
CASE WHEN datediff(dd, duedate, GETDATE()) > 120 AND DATEDIFF(dd, duedate, GETDATE()) <= 1000 THEN InvcHead.UnpostedBal + GLJrnDtl.BookCreditAmount + GLJrnDtl.BookDebitAmount * -1 ELSE 0 END AS [Over 120],
Go to Top of Page

IMZSHAH
Starting Member

12 Posts

Posted - 2014-10-15 : 04:47:19
Any help guys
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-19 : 16:11:00
you will get a null from any of these case statements if any of the columns being added is null. e.g.

if any of the columns UnpostedBal, GLJrnDtl.BookCreditAmount or GLJrnDtl.BookDebitAmount is null

then

UnpostedBal + GLJrnDtl.BookCreditAmount + GLJrnDtl.BookDebitAmount

will be null. You need to either test for that condition in the WHERE Clause or use the ISNULL function to set it to 0 or whatever your default value should be.
Go to Top of Page
   

- Advertisement -