Help Newbie here, new to sql

• imran.shah

  Old Hand

  Points: 364

  More actions

  October 14, 2014 at 3:47 am

  #297336

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

  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.

  

• Neil Burton

  SSC-Insane

  Points: 23009

  More actions

  October 14, 2014 at 4:14 am

  #1752146

  Hi Imran,

  welcome to the forum.

  Try joining to the General Ledger Table with a LEFT join. That's only a guess mind you. If you go to the link on my signature it'll give you some advice about how to post a question to get a better answer.

  N

  ---

  On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
  —Charles Babbage, Passages from the Life of a Philosopher

  How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

• imran.shah

  Old Hand

  Points: 364

  More actions

  October 14, 2014 at 5:44 am

  #1752172

  Thank you, unfortunately Left join will not do anything to the data.

• Neil Burton

  SSC-Insane

  Points: 23009

  More actions

  October 14, 2014 at 5:47 am

  #1752174

  Will not do or does not do?

  Could you post some sample data please? It will make it much easier to establish what's wrong if we've got an idea what your tables look like. You might only need to post a few rows in each to show us what it looks like.

  ---

  On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
  —Charles Babbage, Passages from the Life of a Philosopher

  How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

• djj

  SSCoach

  Points: 18831

  More actions

  October 14, 2014 at 6:15 am

  #1752181

  What do you get when you use a left join form CurrExRate?

• imran.shah

  Old Hand

  Points: 364

  More actions

  October 14, 2014 at 6:21 am

  #1752184

  Sorry will not give the desired result of showing all data but excluding the base currency still

• djj

  SSCoach

  Points: 18831

  More actions

  October 14, 2014 at 6:22 am

  #1752186

  Did not think it would give the desired results. It would however be a step toward finding the problem.

• imran.shah

  Old Hand

  Points: 364

  More actions

  October 14, 2014 at 6:29 am

  #1752187

  I've tried all combinations of joins but it doesn't seem to work, I guess due to the number of filters I have in the where Clause.

• imran.shah

  Old Hand

  Points: 364

  More actions

  October 14, 2014 at 6:36 am

  #1752188

  I have 4 tables,

  Invchead - Holds most the information I need to compile a report, had to join the following tables to get extended detail.

  Customer - Had to join this table as the Invchead table did not hold the customers name,

  CurrExRate - Holds current exchange rate information that invchead did not have,

  GLJrndtl - Holds the revaluations for the month

  The where clauses filtered everything to give the desired outcome for invoices which have been revalued but as I've joined this table and applied my filters, it's now excluded anything which isn't revalued as you would expect.

• imran.shah

  Old Hand

  Points: 364

  More actions

  October 14, 2014 at 6:37 am

  #1752189

  The left join gave me example the same result as I had before, the data did not change.

• Neil Burton

  SSC-Insane

  Points: 23009

  More actions

  October 14, 2014 at 6:39 am

  #1752190

  As I've said before, please can you post some sample data. It will make it much easier to see where there might be a problem.

  ---

  On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
  —Charles Babbage, Passages from the Life of a Philosopher

  How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

• imran.shah

  Old Hand

  Points: 364

  More actions

  October 14, 2014 at 6:44 am

  #1752191

  Hopefully this makes it more readable.

  SELECT InvcHead.Company ("Invchead")

  ,InvcHead.InvoiceNum AS [Invoice Number] ("Invchead")

  ,InvcHead.CurrencyCode AS Currency ("Invchead")

  ,CONVERT(VARCHAR, InvcHead.InvoiceDate, 101) AS [Invoice Date] ("Invchead")

  ,CONVERT(VARCHAR, InvcHead.DueDate, 101) AS [Due Date] ("Invchead")

  ,DATEDIFF(dd, InvcHead.DueDate, GETDATE()) AS [Days Over] ("Invchead")

  ,InvcHead.UnpostedBal AS [Invoice Bal] ("Invchead")

  ,Customer.NAME AS Customer ("Customer")

  ,Currency_table.CurrentRate ("CurrexRate")

  ,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') ("Invchead")

  AND Customer.CustID NOT LIKE '100034' ("Customer")

  AND Customer.CustID NOT LIKE '9%' ("Customer")

  AND Gljrndtl.FiscalYear = '2014' ("GLjrndtl")

  AND Gljrndtl.FiscalPeriod = '9' ("GLjrndtl")

  AND GLJrnDtl.Description LIKE 'rev%' ("GLJrndtl")

  AND (JEDate IN (@GL_date)) Parameters

  AND (SourceModule IN (@Source)) Parameters

  AND (effectivedate IN (@date)) Parameters

  AND (JournalLine IN (@Line)) Parameters

  AND (Reverse IN (@Reverse)) Parameters

  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]

  I've listed table names at the side and parameters.

• imran.shah

  Old Hand

  Points: 364

  More actions

  October 14, 2014 at 6:49 am

  #1752195

  Sorry a little more structured.

  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]

• Neil Burton

  SSC-Insane

  Points: 23009

  More actions

  October 14, 2014 at 6:51 am

  #1752196

  It's a start 🙂 Could you post examples of the data in your tables next please? Have a look at this article

  http://www.sqlservercentral.com/articles/Best+Practices/61537/ and it'll show you how. As I've said, you don't have to post your actual data, just enough to give us an idea.

  Out of interest, have you tried taking things out of your WHERE clause one at a time to see if that makes a difference?

  ---

  On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
  —Charles Babbage, Passages from the Life of a Philosopher

  How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

• imran.shah

  Old Hand

  Points: 364

  More actions

  October 14, 2014 at 7:18 am

  #1752202

  Thank you, I'll put it into the format as per the rules and put some examples together.