SUMMARY QUERY

[Former Member]

I am running the following query to show me all documents tied to a Master Project so that I can see the P&L implications.

SELECT T1.DocNum AS 'Inv #', T1.DocDate, T1.CardCode AS 'Customer #', T1.CardName AS 'Customer', T1.U_ISGI_pono AS 'MP #', T1.U_ISGI_prjd,T1.U_ISGI_type, T0.ItemCode, T0.Quantity, T0.LineTotal, T0.PriceBefDi

FROM DBO.OINV T1

LEFT JOIN DBO.INV1 T0 ON T1.DocEntry = T0.DocEntry and (T0.ItemCode NOT IN ('a9999','c9999','i1026', 'i1027', 'i1028', 'i1029', 'i1032', 'i1122', 'i1143', 'i1501', 'i1620', 'i1900', 'i9997', 'Z_Freight') OR (T0.ItemCode is NULL))

WHERE (T1.U_ISGI_pono Like '%[%2]%') and T1.DocEntry=T0.DocEntry and (T0.LineTotal is Not NULL)

Order By T0.ItemCode

The query works perfectly but want I want to ask is there a way to condense all of the transactional information so that I can prepare a Query to only give me the total information on one line for multiple Master Projects over a set period of time? If anyone has insight on how to do this I would be appreciative.

[Former Member]

Change the code like this to see:

set @df= '[%0\]'

set @dt= '[%1\]'

[Former Member]

Gordon, I tried that as well and got this error message:

1). [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '0'. 2). [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '1'. 3). [Microsoft][SQL Native Client][SQL Server]Statement 'Service Contracts' (OCTR) (s) could not be prepared

[Former Member]

Thanks Gordon.

[Former Member]

May you give a last try:

declare @df datetime

declare @dt datetime

set @df= [%0\]

set @dt= [%1\]

SELECT T0.MP AS 'MP#', SUM(T0.PandL) AS 'P&L'

FROM

(SELECT U_ISGI_pono AS 'MP', Sum(DocTotal) AS 'PandL' FROM OINV t1 WHERE T1.DocDate Between @df and @dt GROUP BY U_ISGI_pono

UNION SELECT U_ISGI_pono AS 'MP', -SUM(DocTotal) AS 'PandL' FROM ORIN t1 WHERE T1.DocDate Between @df and @dt GROUP BY U_ISGI_pono

UNION SELECT U_ISGI_pono AS 'MP', -SUM(DocTotal) AS 'PandL' FROM OPCH t1 WHERE T1.DocDate Between @df and @dt GROUP BY U_ISGI_pono

UNION SELECT U_ISGI_pono AS 'MP', SUM(DocTotal) AS 'PandL' FROM ORPC t1 WHERE T1.DocDate Between @df and @dt GROUP BY U_ISGI_pono) AS T0

GROUP BY T0.MP

If still fail, what is the error message?

[mikesimpson]

Frank,

I can make Gordon's query run too but for some reason when use declared variables on our system I have to put a comment line in after the declaration. On gordon's code if it does not work 'as is' try this.

/*SELECT project , DocTotal FROM [dbo].[OINV] t1 WHERE T1.DocDate > [%0] and T1.DocDate < [%1] */

try inserting it like this

declare @df datetime
declare @dt datetime

/*SELECT project , DocTotal FROM [dbo].[OINV] t1 WHERE T1.DocDate > [%0] and T1.DocDate < [%1] */

set @df= [%0]
set @dt= [%1]

[Former Member]

Gordon, no such luck either. Here is the error message:

Conversion Failed When Converting Date Time From Character String. 'Service Contracts' (OCTR)

[Former Member]

Mike tried that and got this error message:

1). [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '0'. 2). [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '1'. 3). [Microsoft][SQL Native Client][SQL Server]Statement 'Service Contracts' (OCTR) (s) could not be prepared

[mikesimpson]

This is odd because I can see the result that you want on my screen. There must be something in the database setup that is different. I am using 2007A PL45.

Can you try running the following code in a SQL Server Management Studio?

You will get error messages the first time you run it because the temp table does not exist.

use yourdatabasenamehere
go

declare @d1 datetime
declare @d2 datetime
 
drop table #tbl1

set @d1= '2009-01-01 00:00:00.0'
set @d2= '2009-05-01 00:00:00.0'
 
CREATE TABLE #tbl1 (U_ISGI_pono nvarchar(20), Type nvarchar(20), DocTotal Numeric(20))
 
INSERT INTO #tbl1 (U_ISGI_pono, Type, DocTotal)
SELECT project, 'Purchase' , DocTotal FROM [dbo].[OINV] t1 WHERE T1.DocDate > @d1 and T1.DocDate < @d2
 
 INSERT INTO #tbl1 (U_ISGI_pono, Type, DocTotal)
SELECT project, 'Purchase' , DocTotal FROM [dbo].[ORIN] t1 WHERE T1.DocDate > @d1 and T1.DocDate < @d2
 
INSERT INTO #tbl1 (U_ISGI_pono, Type, DocTotal)
SELECT project, 'Update' , DocTotal FROM [dbo].[OPCH] t1 WHERE T1.DocDate > @d1 and T1.DocDate < @d2
 
INSERT INTO #tbl1 (U_ISGI_pono, Type, DocTotal)
SELECT project, 'Update' , DocTotal FROM [dbo].[ORPC] t1 WHERE T1.DocDate > @d1 and T1.DocDate < @d2
  
SELECT U_ISGI_pono, Type, Sum(DocTotal) as 'Doc Total'
FROM #tbl1
Group By U_ISGI_pono, Type

[Former Member]

Mike, I had missed making one of the "project" substitutions and got the query to run. Unfortunately, it is not netting the Master Project # fiedl (U_ISGI_pono) into one line as I am still getting multiple rows. I also lost the negativity that I need to show the expenses as. Thoughts?

[Former Member]

Mike, I think that I got it. I changed the UPDATE on the two rows to PURCHASE and it appears that they are finally netting out into one row! Only thing that I am noticing is that the numbers are rounding off and showing no cents. Any idea on why that would happen?

[mikesimpson]

Splendid, we are getting somwhere. To get your cents we need to change the data type on the fileld to look like this

CREATE TABLE #tbl1 (U_ISGI_pono nvarchar(20), Type nvarchar(20), DocTotal numeric(19,2))

the change is subtle = added ,2 to numeric(19

here goes....

[Former Member]

Mike, I saw that and it works well. I kept the number 20 and did not change it to 19 and so far it looks to be okay.

One last question concerning my Master Project #'s (U_ISGI_pono). We have several MP#'s that are of the same MP# per se but we use a Alpha code at the end of the numeric MP#. (eg. MP# 18054; MP# 18054COM; MP18054TWC). Right now, each of these MP#'s are coming up separately. Is there a way to get everything combined under 18054 or does the Alpha ending make this unachievable?

[mikesimpson]

yep that is straigt forward enough. you can use the left(fieldname,5) function.

However, be warned this will only work if your data is consistantly structured nnnnnttt where the 5 n's are the characters that you want. if you want 7 then change the 5 to a 7 just like in MS Excel.

This need to be applied to the 4 select statements.

SELECT left(U_ISGI_pono,5), 'Purchase' , DocTotal FROM [dbo].[OINV] t1 WHERE T1.DocDate > @d1 and   T1.DocDate < @d2

[mikesimpson]

Frank, there are a couple of things to note about this solution.

Firstly, The concept is you create a temp table (#tbl1), you fill it up with data (the 4 INSERT INTO SELECT statements) you can report from the temp table (the SELECT * FROM #tbl1)

Once you close the query the temp table disappears.

Secondly, if you want to add an extra column in you can but you have to declare the field and the data type of the field when you create the temp table #tbl1.

i.e. going back to my earlier example I had docdate in there with a datatype of datetime

To get the datatype of the field you are looking to insert into the temp table you will need to look at the structure of the field in SQL by expanding the database>tables>table name and you will see the datatype of the field adjacent to the field name. Sometimes you will get lucky by switching on the System Information.

Cheers,

Mike

[Former Member]

Thanks Mike.

[Former Member]

Try this one then:

SELECT T0.MP#, SUM(T0.P&L) AS 'P & L'

FROM

(SELECT U_ISGI_pono AS 'MP#', Sum(DocTotal) AS 'P&L' FROM OINV t1 WHERE T1.DocDate >'%0' and T1.DocDate <' GROUP BY U_ISGI_pono

UNION SELECT U_ISGI_pono AS 'MP#', -SUM(DocTotal) AS 'P&L' FROM ORIN t1 WHERE T1.DocDate >'%0' and T1.DocDate <' GROUP BY U_ISGI_pono

UNION SELECT U_ISGI_pono AS 'MP#', -SUM(DocTotal) AS 'P&L' FROM OPCH t1 WHERE T1.DocDate >'%0' and T1.DocDate <' GROUP BY U_ISGI_pono

UNION SELECT U_ISGI_pono AS 'MP#', -SUM(DocTotal) AS 'P&L' FROM ORPC t1 WHERE T1.DocDate >'%0' and T1.DocDate <' GROUP BY U_ISGI_pono) AS T0

GROUP BY T0.MP#

[Former Member]

Gordon, I had to modify your structure slightly since you picked up my column headings as field names. Below is the revised script. I get to the date selection range but then get error message.

SELECT T0.U_ISGI_pono AS 'MP#', T0.DocTotal AS 'P&L

FROM

(SELECT U_ISGI_pono AS 'MP#', Sum(DocTotal) AS 'P&L FROM OINV t1, SUM(-DocTotal) AS 'P&L FROM ORIN t1, SUM(-DocTotal) AS 'P&L FROM OPCH t1, SUM(-DocTotal) AS 'P&L FROM ORPC t1 WHERE T1.DocDate >'[%0]' and T1.DocDate <'[%1]' GROUP BY U_ISGI_pono)) AS T0

GROUP BY T0.U_ISGI_pono

[Former Member]

Here is an example for you:

SELECT T0.ItemCode, SUM(T0.Quantity) AS QTY, SUM(T0.LineTotal) AS Total, T0.PriceBefDi

FROM DBO.OINV T1

LEFT JOIN DBO.INV1 T0 ON T1.DocEntry = T0.DocEntry and T0.ItemCode NOT IN ('a9999','c9999','i1026', 'i1027', 'i1028', 'i1029', 'i1032', 'i1122', 'i1143', 'i1501', 'i1620', 'i1900', 'i9997', 'Z_Freight')

WHERE T1.DocDate Between '[%0\]' AND '[%1\]' AND T0.Linetotal != 0

GROUP BY T0.ItemCode, T0.PriceBefDi

Order By T0.ItemCode

[Former Member]

Gordon, I goofed when I copied over the wrong SQL based on my P&L request initially. Below is the SQL that I need to modify to give me summary info for my individual Master Projects.

SELECT DocNum, 'A/R_Invoice' As Document_Type, DocDate, DocTotal, DocStatus, CardCode, CardName, U_ISGI_pono, U_ISGI_prjd FROM OINV t1 WHERE t1.U_ISGI_pono like'%[%0]%'

UNION (SELECT DocNum, 'AR_Credit Memo' As Document_Type, DocDate, DocTotal * -1, DocStatus, CardCode, CardName, U_ISGI_pono, U_ISGI_prjd FROM ORIN WHERE U_ISGI_pono like'%[%0]%')

UNION (SELECT DocNum, 'A/P_Invoice' As Document_Type, DocDate, DocTotal * -1, DocStatus, CardCode, CardName, U_ISGI_pono, U_ISGI_prjd FROM OPCH WHERE U_ISGI_pono like'%[%0]%')

UNION (SELECT DocNum, 'A/P_Credit Memo' AsDocument_Type, DocDate, DocTotal, DocStatus, CardCode, CardName, U_ISGI_pono, U_ISGI_prjd FROM ORPC WHERE U_ISGI_pono like'%[%0]%')

ORDER by DocStatus

[Former Member]

Try this code, you should know what you can or cant include any fields for your summary:

SELECT CardCode, CardName, 'A/R_Invoice' As Document_Type,SUM(DocTotal) AS Total
FROM OINV t1 WHERE t1.DocDate Between '[%0]' AND '[%1]'
GROUP BY CardCode, CardName
UNION
SELECT CardCode, CardName, 'A/R_CreditMemo' As Document_Type,-SUM(DocTotal) AS Total
FROM ORIN t1 WHERE t1.DocDate Between '[%0]' AND '[%1]'
GROUP BY CardCode, CardName

[Former Member]

Gordon, below is the SQL that works if not grouped in that I get the individual invoices, payments and credit memos to appear. My goal is to get this to group by my Master Project # (U_ISGI_pono) where I input a date range and multiple rows of individual Master Projects #'s are detailed summing all of the invoices, payments and credit memos into a single number. I have made changes to this but had no success with other iterations. Thoughts?

SELECT CardCode, CardName, 'A/R_Invoice' As Document_Type, DocDate, DocTotal, DocStatus, U_ISGI_pono, U_ISGI_prjd FROM OINV t1 WHERE T1.DocDate >'[%0]' and T1.DocDate <'[%1]' GROUP BY U_ISGI_pono

UNION (SELECT CardCode, CardName, 'AR_Credit Memo' As Document_Type, DocDate, DocTotal * -1, DocStatus, U_ISGI_pono, U_ISGI_prjd FROM ORIN WHERE DocDate >'[%0]' and DocDate <'[%1]')

UNION (SELECT CardCode, CardName, 'A/P_Invoice' As Document_Type, DocDate, DocTotal * -1, DocStatus, U_ISGI_pono, U_ISGI_prjd FROM OPCH WHERE DocDate >'[%0]' and DocDate <'[%1]')

UNION (SELECT CardCode, CardName, 'A/P_Credit Memo' AsDocument_Type, DocDate, DocTotal, DocStatus, U_ISGI_pono, U_ISGI_prjd FROM ORPC WHERE DocDate >'[%0]' and DocDate <'[%1]')

GROUP BY U_ISGI_pono

[Former Member]

Repeated: you don't have options if you need both detail and summary. Probably try XL Reporter for your report. That would be better for grouping to meet you need. Because buy Excel, you have option to hide detail records easily.

[Former Member]

Not looking for detail but just want to group by U_ISGI_pono field. This covers items from A/P Invoices; Credit Memos; and A/R Invoices. Can this be done in a query or am I wasting time pursuing?

[Former Member]

When you use group clause, you have to include all fields which are no SUM or COUNT. That make you goal unachievable.

[Former Member]

So if I want to group by a UDF alphanumeric field I cannot achieve this with the Group function?

[Former Member]

It may work if only you include this field in the query but no others like Customer or Document type.

[former_member204969]

You can start with this code example and can evaluate your own query:

declare @d1 datetime
declare @d2 datetime
/* select * from oinv i where  */
set @d1=/* i.docdate >= */'[%0]'
/* select * from oinv i where  */
set @d2=/* i.docdate <= */'[%1]'

Select typ, DocNum, CardCode,
 case grouping(CardCode) when 0 then max(CardName) else '' end,
 sum(DocTotal)
From
(SELECT 'Inv' as typ, T0.[DocNum], T0.[CardCode], T0.[CardName], T0.[DocTotal], T0.[DocDate]
 FROM OINV T0
Union
SELECT 'RIN' as typ, T0.[DocNum], T0.[CardCode], T0.[CardName], T0.[DocTotal], T0.[DocDate]
 FROM ORIN T0 )a
 Where docdate between  @d1 and @d2
 GROUP BY typ,CardCode, DocNum With rollup
 ORDER BY typ,CardCode, DocNum

(The max(cardname) is an example how you can insert a value which is not a sum or count.)

[Former Member]

I tried this and am able to get it by my Master Project # (U_ISGI_pono field) but because I am asking to pull data from three different tables, I am getting the data by MP# but on three different lines and not combined into one line which is what I want it to show. Also, it is only allowing me to have two columns, the MP# and Total columns. I would like to have a couple of more fields show but have had no success in trying to get it to execute properly.

[Former Member]

Istvan, I tried your SQL script but could not get it to work. Thank you.

[Former Member]

You need to try more to understand what is the limitation of the grouping. If you do not give up the detail data, it will never be done.

[mikesimpson]

Hi Frank,

I think I may know how you can achive what you are after. The concept behind this code that you create a temp table and insert your query results into it. that way you only have 1 table to report from and sorting\grouping is much more manageable. unfortunatly i do not have your UDFs so i can replicate your code. however, hopefully you will get the idea. try it anyway and see how you get on.

I have filtered on project code since I do not have your UDFs

I would use the variables as Istvan has suggested for your params.

CREATE TABLE #tbl1 (DocNum int, Doc_Type Char(1), DocDate datetime, DocTotal nvarchar(20), DocStatus Char(1), CardCode nvarchar(15), CardName nvarchar(100), Project nvarchar(8))

INSERT INTO #tbl1 (DocNum, Doc_Type , DocDate, DocTotal, DocStatus, CardCode, CardName , Project)
SELECT DocNum, DocType, DocDate, DocTotal, DocStatus, CardCode, CardName, project FROM OINV WHERE project <> ' '

INSERT INTO #tbl1 (DocNum, Doc_Type , DocDate, DocTotal, DocStatus, CardCode, CardName , Project)
SELECT DocNum, DocType, DocDate, DocTotal, DocStatus, CardCode, CardName, project FROM ORIN WHERE project <> ' '

INSERT INTO #tbl1 (DocNum, Doc_Type , DocDate, DocTotal, DocStatus, CardCode, CardName , Project)
SELECT DocNum, DocType, DocDate, DocTotal, DocStatus, CardCode, CardName, project FROM OPCH WHERE project <> ' '

INSERT INTO #tbl1 (DocNum, Doc_Type , DocDate, DocTotal, DocStatus, CardCode, CardName , Project)
SELECT DocNum, DocType, DocDate, DocTotal, DocStatus, CardCode, CardName, project FROM ORPC WHERE project <> ' '

SELECT * FROM #tbl1

Thanks,

Mike

[Former Member]

I have cut down on the fields that I need to just include my Master Project # and Total field. Below is the SQL:

SELECT U_ISGI_pono AS 'MP#', Sum(DocTotal) AS 'P&L' FROM OINV t1 WHERE T1.DocDate >'[%0]' and T1.DocDate <'[%1]' GROUP BY U_ISGI_pono

UNION SELECT U_ISGI_pono AS 'MP#', -SUM(DocTotal) AS 'P&L' FROM ORIN t1 WHERE T1.DocDate >'[%0]' and T1.DocDate <'[%1]' GROUP BY U_ISGI_pono

UNION SELECT U_ISGI_pono AS 'MP#', -SUM(DocTotal) AS 'P&L' FROM OPCH t1 WHERE T1.DocDate >'[%0]' and T1.DocDate <'[%1]' GROUP BY U_ISGI_pono

UNION SELECT U_ISGI_pono AS 'MP#', -SUM(DocTotal) AS 'P&L' FROM ORPC t1 WHERE T1.DocDate >'[%0]' and T1.DocDate <'[%1]' GROUP BY U_ISGI_pono

It is generating the following results:

1. MP# P&L

1 100 -2,532.50

2 100 12,000.00

3 123 -3,295.00

4 123 7,500.00

My goal is to get the query output to show the results as follows:

1. MP# P&L Description

1 100 9,467.50 VOD Purchase

2 123CO 4,205.00 PPV Update

[mikesimpson]

Hi Frank,

This is untested because of my lack of suitable UDFs and if Gordon's Works it is possibly more simple, however...

I have made an assumption that the datatype of your UDF is nvarchar and is no longer than 20 characters.

if the date gives and error wrap the variable with a ' ie

'[%0]'

Give it ago...

declare @d1 datetime
declare @d2 datetime

set @d1= [%0]
set @d2= [%1]


CREATE TABLE #tbl1 (U_ISGI_pono nvarchar(20), Type nvarchar(20), DocTotal Numeric(20))
 
INSERT INTO #tbl1 (U_ISGI_pono, Type, DocTotal)
SELECT U_ISGI_pono, 'Purchase' , DocTotal FROM OINV t1 WHERE T1.DocDate > @d1 and T1.DocDate < @d2

 
INSERT INTO #tbl1 (U_ISGI_pono, Type, DocTotal)
SELECT U_ISGI_pono, 'Purchase' , DocTotal FROM ORIN t1 WHERE T1.DocDate > @d1 and T1.DocDate < @d2

INSERT INTO #tbl1 (U_ISGI_pono, Type, DocTotal)
SELECT U_ISGI_pono, 'Update' , DocTotal FROM OPCH t1 WHERE T1.DocDate > @d1 and T1.DocDate < @d2


INSERT INTO #tbl1 (U_ISGI_pono, Type, DocTotal)
SELECT U_ISGI_pono, 'Update' , DocTotal FROM ORPC t1 WHERE T1.DocDate > @d1 and T1.DocDate < @d2




SELECT U_ISGI_pono, Type, Sum(DocTotal) as 'Doc Total'
FROM #tbl1
Group By U_ISGI_pono, Type

Thanks,

Mike

[Former Member]

Mike, no success as I keep getting syntax errors.

[Former Member]

Try this one:

declare @df datetime

declare @dt datetime

set @df= [%0\]

set @dt= [%1\]

SELECT T0.MP#, SUM(T0.P&L) AS 'P & L'

FROM

(SELECT U_ISGI_pono AS 'MP#', Sum(DocTotal) AS 'P&L' FROM OINV t1 WHERE T1.DocDate >@df and T1.DocDate <@dt GROUP BY U_ISGI_pono

UNION SELECT U_ISGI_pono AS 'MP#', -SUM(DocTotal) AS 'P&L' FROM ORIN t1 WHERE T1.DocDate >@df and T1.DocDate <@dt GROUP BY U_ISGI_pono

UNION SELECT U_ISGI_pono AS 'MP#', -SUM(DocTotal) AS 'P&L' FROM OPCH t1 WHERE T1.DocDate >@df and T1.DocDate <@dt GROUP BY U_ISGI_pono

UNION SELECT U_ISGI_pono AS 'MP#', -SUM(DocTotal) AS 'P&L' FROM ORPC t1 WHERE T1.DocDate >@df and T1.DocDate <@dt GROUP BY U_ISGI_pono) AS T0

GROUP BY T0.MP#

[Former Member]

Gordon/Mike thanks for all of your help. Still nothing. Ready to give up on this.

[mikesimpson]

Nope, just taking another look now, the chances are you will get 2 solutions at once!

[mikesimpson]

Frank, try this the SAP query generator has some interesting hurdles to get round.

see if the following query runs without error in first instance, i have just been able to run it. there result will be garbage but it will prove the concept.

If that works replace the word 'project' (4 instances after each SELECT) with your UDF U_ISGI_pono and try it again.

declare @d1 datetime
declare @d2 datetime

/*SELECT project, 'Purchase' , DocTotal FROM [dbo].[OINV] t1 WHERE T1.DocDate > [%0] and T1.DocDate < [%1] */
 
set @d1= '[%0]'
set @d2= '[%1]'

CREATE TABLE #tbl1 (U_ISGI_pono nvarchar(20), Type nvarchar(20), DocTotal Numeric(20))
 
INSERT INTO #tbl1 (U_ISGI_pono, Type, DocTotal)
SELECT project, 'Purchase' , DocTotal FROM [dbo].[OINV] t1 WHERE T1.DocDate > @d1 and T1.DocDate < @d2
 
 INSERT INTO #tbl1 (U_ISGI_pono, Type, DocTotal)
SELECT project, 'Purchase' , DocTotal FROM [dbo].[ORIN] t1 WHERE T1.DocDate > @d1 and T1.DocDate < @d2
 
INSERT INTO #tbl1 (U_ISGI_pono, Type, DocTotal)
SELECT project, 'Update' , DocTotal FROM [dbo].[OPCH] t1 WHERE T1.DocDate > @d1 and T1.DocDate < @d2
 
INSERT INTO #tbl1 (U_ISGI_pono, Type, DocTotal)
SELECT project, 'Update' , DocTotal FROM [dbo].[ORPC] t1 WHERE T1.DocDate > @d1 and T1.DocDate < @d2
  
SELECT U_ISGI_pono, Type, Sum(DocTotal) as 'Doc Total'
FROM #tbl1
Group By U_ISGI_pono, Type

[Former Member]

The below SQL generates the info I need but not combined on a single row as I detail below the SQL.

SELECT U_ISGI_pono AS 'MP#', Sum(DocTotal) AS 'P&L' FROM OINV t1 WHERE T1.DocDate >'[%0]' and T1.DocDate <'[%1]' GROUP BY U_ISGI_pono

UNION

SELECT U_ISGI_pono AS 'MP#', -SUM(DocTotal) AS 'P&L' FROM ORIN t1 WHERE T1.DocDate >'[%0]' and T1.DocDate <'[%1]' GROUP BY U_ISGI_pono

UNION

SELECT U_ISGI_pono AS 'MP#', -SUM(DocTotal) AS 'P&L' FROM OPCH t1 WHERE T1.DocDate >'[%0]' and T1.DocDate <'[%1]' GROUP BY U_ISGI_pono

UNION

SELECT U_ISGI_pono AS 'MP#', -SUM(DocTotal) AS 'P&L' FROM ORPC t1 WHERE T1.DocDate >'[%0]' and T1.DocDate <'[%1]' GROUP BY U_ISGI_pono

1. U_ISGI_pono DocTotal

1 100 -2,532.50

2 100 12,000.00

3 123 -3,295.00

4 123 7,500.00

1. = Column generated in Query output

U_ISGI_pono = UDF

DocTotal = Summary field in OINV, ORIN, OPCH & ORPC tables

Description = UDF

My goal is to get the query output to show the results as follows:

1. U_ISGI_pono DocTotal Description

1 100 9,467.50 VOD Purchase

2 123CO 4,205.00 PPV Update

[Former Member]

Mike, here is the error message I got:

Conversion Failed When Converting Date Time From Character String. 'User Defined Values' CSHS

[Former Member]

If you need summary query, you have to remove all detailed info like Inv#, DocDate, and/or CardCode, ItemCode. Then use Group By clause to group them.

Thanks,

Gordon

[Former Member]

Gordon, I have not used the Group by clause before so can I ask you how that would be written?

Also, with the Select statement deleted, does the query start right after that statement is deleted?