Skip to Content
0
Former Member
Jan 25, 2009 at 09:02 PM

Sales Analysis query

41 Views

I would like to create a query that reliably and exactly returns the same results as the Sales Analysis tool with regard to net balance. I don't care about the other columns and I don't care about filtering by customer, by sales employee, etc. Once I have that I can create other forms of the query using joins and where clauses. I have tried three approaches, none of which do the job correctly, albeit pretty close. I would appreciate some feedback (any feedback is better than no feedback) and help to achieve this goal:

Approach #1:

declare @start datetime

declare @end datetime

set @start = '01/01/2008'

set @end = '12/31/2008'

select sum(t2.subtotal) from (select t0.doctotal as subtotal

from oinv t0

where t0.docdate between @start and @end

union all

select -1*t0.doctotal as subtotal

from orin t0

where t0.docdate between @start and @end) t2

Approach #2:

declare @start datetime

declare @end datetime

set @start = u201801/02/2008u2019

set @end = u201812/31/2008u2019

Select sum(t5.subtotal) from (Select SUM as subtotal

From INV1 T0

Inner JOIN OINV T1 ON T1.DocEntry = T0.DocEntry

Where T1.DocDate BETWEEN @start AND @end

Union All

Select -SUM as subtotal

From RIN1 T0

Inner JOIN ORIN T1 ON T1.DocEntry = T0.DocEntry

Where T1.DocDate BETWEEN @start AND @end

union all

select sum(t0.totalexpns) as subtotal

from oinv t0

where t0.docdate between @start and @end union all

select sum(t0.totalexpns) as subtotal

from orin t0

where t0.docdate between @start and @end) t5

Note: the outcome of this is identical to Approach #1.

Approach #3:

SELECT SUM(t0.debit - t0.credit) as u2018Net Balanceu2019

from JDT1 T0

Inner Join OJDT T1 ON T1.TransId = T0.TransID and

(T1.TransType = u201813u2019 or T1.TransType = u201814u2019)

Inner Join OCRD T2 ON T2.CardCode = T0.ShortName

LEFT JOIN OINV T3 ON T3.TransId = T1.TransId

LEFT JOIN ORIN T4 ON T4.TransID = T1.TransId

Where T1.refdate Between u2018[%0]u2019 and u2018[%1]u2019 and T2.CardType = u2018Cu2019