Skip to Content
author's profile photo Former Member
Former Member

Invoices by Month HELP....

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....

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on Apr 20, 2011 at 01:53 PM

    Any help PLEASE.....

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      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.'

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.