Average trail query

27 Views

Dear Experts,

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

What i need is

Customer Name Number of Quotes Done Value of Quotes Number of Sales Orders Created Value of Sales Orders % Quotes/Sales Orders 12 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.