on 04-19-2011 3:12 PM
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....
Any help PLEASE.....
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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 !
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
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
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
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.'
User | Count |
---|---|
91 | |
8 | |
7 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.