cancel
Showing results for 
Search instead for 
Did you mean: 

Invoices by Month HELP....

Former Member
0 Kudos

I am at a total loss... I am trying to create a custom query that I think is way over my head! Can any of you help me figure out how to do this?

The execs want a report to show the following columns:

Customer #

Customer Name

Invoice Number

Document Type

Invoice Date

Gross Amount

Breakdown gross amount by base amount, tax amount, retention amount

The GL Account associated to the revenue

This would be broken down for current month.

I just have no idea to where to even begin....

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Any help PLEASE.....

Former Member
0 Kudos

HI DaytonHoneycutt

Try this ,

SELECT T0.CardCode, T0.CardName, T0.DocNum,'AR Invoice' as Type ,T0.DocType, T0.DocDate ,SUM(T1.LineTotal) AS 'Total before VAT', SUM(T1.VatSum) 
  AS 'Tax amount' , SUM(T1.LineTotal) + SUM(T1.VatSum)  as 'Retention amount'
FROM dbo.OINV  T0 INNER JOIN
  dbo.INV1  T1 ON T0.DocEntry = T1.DocEntry INNER JOIN
  dbo.OVTG  T2 ON T1.VatGroup = T2.Code
WHERE T0.DocDate >= [%0] AND T0.DocDate <= [%1]
GROUP BY T0.CardCode, T0.CardName, T0.DocNum, T0.DocDate, T0.DocType
UNION
SELECT T0.CardCode, T0.CardName, T0.DocNum,'AR Credit Memo' as Type , T0.DocType, T0.DocDate,SUM(T1.LineTotal) AS 'Total before VAT', SUM(T1.VatSum) 
  AS 'Tax amount' , SUM(T1.LineTotal) + SUM(T1.VatSum)  as 'Retention amount'
FROM  dbo.ORIN  T0 INNER JOIN
  dbo.RIN1  T1 ON T0.DocEntry = T1.DocEntry INNER JOIN
  dbo.OVTG  T2 ON T1.VatGroup = T2.Code
WHERE T0.DocDate >= [%0] AND T0.DocDate <= [%1]
 GROUP BY T0.CardCode, T0.CardName, T0.DocNum, T0.DocDate, T2.Rate, T0.DocType

hope this help you !

Former Member
0 Kudos

Thank you, but whatever dates I put in it never returns any values?

Anything else I can try?

Former Member
0 Kudos

Try this first:

SELECT T0.CardCode, T0.CardName, T0.DocNum,'AR Invoice' as Type ,T0.DocType, T0.DocDate ,SUM(T1.LineTotal) AS 'Total before VAT', SUM(T1.VatSum) AS 'Tax amount', SUM(T1.LineTotal) + SUM(T1.VatSum) as 'Retention amount'

FROM dbo.OINV T0

INNER JOIN dbo.INV1 T1 ON T0.DocEntry = T1.DocEntry

LEFT JOIN dbo.OVTG T2 ON T1.VatGroup = T2.Code

WHERE DateDiff(MM,T0.DocDate,Getdate())=0

GROUP BY T0.CardCode, T0.CardName, T0.DocNum, T0.DocDate, T0.DocType

Thanks,

Gordon

Former Member
0 Kudos

Gordon,

That works great!! Now how can I set this up to pull credit memos and any other AR Invoices?

Also, they have now asked to see if it can default to current month or have a month/year selection. That way if they do not enter a specific month and year, it will default to the current month. I hope this makes some sense?

Thank again Gordon!!!!

Edited by: DaytonHoneycutt on Apr 21, 2011 3:13 PM

Edited by: DaytonHoneycutt on Apr 21, 2011 3:30 PM

Former Member
0 Kudos

Hi

try this :

SELECT T0.CardCode, T0.CardName, T0.DocNum,'AR Invoice' as Type ,T0.DocType, T0.DocDate ,SUM(T1.LineTotal) AS 'Total before VAT', SUM(T1.VatSum) AS 'Tax amount', SUM(T1.LineTotal) + SUM(T1.VatSum) as 'Retention amount'
FROM dbo.OINV T0
INNER JOIN dbo.INV1 T1 ON T0.DocEntry = T1.DocEntry
LEFT JOIN dbo.OVTG T2 ON T1.VatGroup = T2.Code
WHERE '1' = (CASE WHEN [%0] = '' and  DateDiff(MM,T0.DocDate,Getdate())=0 THEN '1' 
when right('00' + convert(nvarchar(2),month(T0.DocDate)),2) + '/' + convert(nvarchar(4),year(T0.DocDate)) = [%0]  THEN '1' else '0' end)  
GROUP BY T0.CardCode, T0.CardName, T0.DocNum, T0.DocDate, T0.DocType

when you enter month/year this format is MM/yyyy ex : 04/2011

thanks

H2

Former Member
0 Kudos

Here is my query now... Please note that I am also hitting our prject management tables to get a few pieces of the information. When I add your pice to it H2, I do not get anything, just "No data found". All of the Data is now opulling correctly and I have no complaints about that, just cant seem to get this to work where I can make it selectable by month/year.

Any thoughts?

SELECT (SELECT TOP (1) Projektnummer

FROM P4100_PM.dbo.MPProjektstamm

WHERE (Kostentraeger =

(SELECT TOP (1) Project

FROM JDT1

WHERE (TransId = l.TransId) AND (ISNULL(Project, N'') <> '')))) AS 'Project Number',

(SELECT TOP (1) Projektname

FROM P4100_PM.dbo.MPProjektstamm AS MPProjektstamm_2

WHERE (Kostentraeger =

(SELECT TOP (1) Project

FROM JDT1 AS JDT1_2

WHERE (TransId = l.TransId) AND (ISNULL(Project, N'') <> '')))) AS 'Project Name', OCRD.CardCode AS 'Customer Code',

OCRD.CardName AS 'Customer Name',

(SELECT Personenname

FROM P4100_PM.dbo.MPPersonenstamm

WHERE (Personalnummer =

(SELECT Personalnummer

FROM P4100_PM.dbo.MPProjektstammProjektleiter

WHERE (ProjektleiterTyp = 1) AND (Projektnummer =

(SELECT TOP (1) Projektnummer

FROM P4100_PM.dbo.MPProjektstamm AS MPProjektstamm_1

WHERE (Kostentraeger =

(SELECT TOP (1) Project

FROM JDT1 AS JDT1_1

WHERE (TransId = l.TransId) AND (ISNULL(Project, N'') <> '')))))))) AS 'Project Manager',

j.BaseRef AS 'Doc. No.', CASE l.transtype WHEN '13' THEN 'Invoice' WHEN '14' THEN 'AR CN' WHEN '24' THEN 'INCOMING' ELSE 'Other' END AS 'Type',

INV1.LineNum AS 'Line #', CONVERT(VARCHAR(10), l.TaxDate, 103) AS 'Doc Date', INV1.LineTotal AS [Base Amount], INV1.VatSum AS [Tax Amount],

OINV.DocTotal - OINV.PaidToDate AS [Retention Open Amount], OINV.DocTotal

FROM INV1 INNER JOIN

OINV ON INV1.DocEntry = OINV.DocEntry RIGHT OUTER JOIN

JDT1 AS l INNER JOIN

OJDT AS j ON j.TransId = l.TransId INNER JOIN

OCRD ON l.ShortName = OCRD.CardCode ON OINV.TransId = l.TransId

WHERE (OCRD.CardType = 'c') AND (l.IntrnMatch = '0') AND (DATEDIFF(MM, l.TaxDate, GETDATE()) = 0) AND

((SELECT TOP (1) Projektnummer

FROM P4100_PM.dbo.MPProjektstamm AS MPProjektstamm_3

WHERE (Kostentraeger =

(SELECT TOP (1) Project

FROM JDT1 AS JDT1_3

WHERE (TransId = l.TransId) AND (ISNULL(Project, N'') <> '')))) LIKE '1%')

ORDER BY 'Project Number', 'Project Manager', 'Customer Code', 'Customer Name', l.TaxDate, 'Doc. No.'