cancel
Showing results for 
Search instead for 
Did you mean: 

Average trail query

Former Member
0 Kudos

Dear Experts,

I have lost my earlier threads so i am starting with new discussion.

What i need is

Customer NameNumber of Quotes DoneValue of QuotesNumber of Sales Orders CreatedValue of Sales Orders% Quotes/Sales Orders12 Month Trailing Average

I have started with below query but i am not getting where i am getting wrong.

Select CardName,DocNum,Total,SOCardName,SONum,SOTotal

from (

Select distinct  convert (varchar,Max( T0.[CardName]))CardName,Count(T0.[DocNum]) DocNum, Sum((T0.[DocTotal] - T0.[VatSum])) Total,convert (varchar,Max( T4.[CardName]))SOCardName,Count(T4.[DocNum]) SONum, Sum((T4.[DocTotal] - T4.[VatSum])) SOTotal

From OQUT t0 INNER JOIN [dbo].[QUT1]  T1 ON T0.DocEntry = T1.DocEntry

left join [dbo].[RDR1]  T3 on t0.docEntry=T3.Baseentry left JOIN ORDR T4

ON T3.DocEntry = T4.DocEntry

where T0.[DocDate] >=[%0] AND  T0.[DocDate] <=[%1] and T1.[WhsCode] <> '90'

Group By t0.[docnum],T0.[DocDate],T1.[Quantity],T0.[CardName],T0.[DocTotal], T0.[VatSum]

union all

Select Distinct convert (varchar,Max(T0.[CardName])) + ' ' +  'Total' CardName,Count(T0.[DocNum]) DocNum,Sum(T0.[DocTotal] - T0.[VatSum]) Total,convert (varchar,Max( T4.[CardName]))SOCardName,Count(T4.[DocNum]) SONum, Sum((T4.[DocTotal] - T4.[VatSum])) SOTotal

From OQUT t0 INNER JOIN [dbo].[QUT1]  T1 ON T0.DocEntry = T1.DocEntry

left join [dbo].[RDR1]  T3 on t0.docEntry=T3.Baseentry left JOIN ORDR T4

ON T3.DocEntry = T4.DocEntry

where T0.[DocDate] >=[%0] AND  T0.[DocDate] <=[%1] and T1.[WhsCode] <> '90'

Group By T0.[CardName],T4.[CardName])a

ORDER BY  CardName

Need help!

Thanks-

Mona.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Mona,

Group function has limited result can be derived. You need two queries for your goal. Check this simple query for quotation:

SELECT Max(T0.Cardname) 'Customer Name', Count(T0.DocEntry) 'Number of Quotes Done', Sum(T0.DocTotal - T0.VatSum) 'Value of Quotes'

From OQUT t0

where T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1]

GROUP BY T0.CardCode

Warehouse is on the line level. Are all lines with the same warehouse for one document?

Thanks, Gordon

Former Member
0 Kudos

Hi gordon,

It works fine in case of quotation only but when i am linking SO table to it .I am not getting correct result.

Thanks-

Mona.

Former Member
0 Kudos

Hi gordon,

It works fine in case of quotation only but when i am linking SO table to it .I am not getting correct result.

Thanks-

Mona.

Former Member
0 Kudos

Hi Gordon,

In short i cant get the average trail report as required.

I am closing message thanks-

Thanks-

Mona.

Former Member
0 Kudos

Hi Gordon,

In short i cant get the average trail report as required.

I am closing message thanks-

Thanks-

Mona.

Former Member
0 Kudos

That is right. You need two reports for this purpose. Pleas close your thread.

Former Member
0 Kudos

Hi,

Thanks for your reply.

From where i can give points and close the thread?

Thanks-

Mona.

Former Member
0 Kudos

Hi,

Thanks for your reply.

From where i can give points and close the thread?

Thanks-

Mona.

Former Member
0 Kudos

Hi,

Thanks for your reply.

From where i can give points and close the thread?

Thanks-

Mona.

Former Member
0 Kudos

The new system changed to have only 2 options: one is Helpful = 5; the other is Correct = 10.

Answers (0)