Help Newbie needs HELP - SQL Server Forums

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?

IMZSHAH
Starting Member

12 Posts

Posted - 2014-10-13 : 10:43:13

Sure thing, how can I upload an attachment please?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-13 : 10:54:45

just a few lines would be enough

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

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)

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.

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]

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.

IMZSHAH
Starting Member

12 Posts

Posted - 2014-10-14 : 08:45:16

Thank you for simplyfying the code.

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

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.

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.

IMZSHAH
Starting Member

12 Posts

Posted - 2014-10-14 : 09:50:18

Thank you so much resolved.

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],

IMZSHAH
Starting Member

12 Posts

Posted - 2014-10-15 : 04:47:19

Any help guys

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources