Query - detecting new customers joined every month

[former_member765529]

Hello everyone,

I would like to create a query which detecting new customers joined every month.

New customer counts if:

Or make an Order

Or get an invoice

Sometimes the customer order and than get invoice, and sometimes get an invoice without issuing an order.

Hence:

The report need to take in account the first order or a first invoice (if wasn’t an order before).

Sometimes the customer order in one month, but get the invoice in the month after. In this case , what’s count is only the order , because for us he is a new customer when he order and not when gets an invoice.

This is the current query we use:

--------------------------------------------------------

/*SELECT FROM [dbo].[ocrd] T0*/

declare @date1 datetime

declare @date2 datetime

/* where */

set @date1 =/* T0.createdate */'[%0]'

set @date2 =/* T0.createdate */'[%1]'

(select distinct ORDR.docnum,

N'הזמנה' AS docnum ,

ORDR.cardcode,

ORDR.cardname,

(ORDR.doctotal-ORDR.VatSum) as totalSum,

ORDR.docdate,

oslp.SlpName

,ordr.[U_XIS_SLPLine],

case when ocrd.u_xis_referrer =1 then N'אינטרנט'

when ocrd.u_xis_referrer =2 then N'לקוח'

when ocrd.u_xis_referrer =3 then N'ספק'

when ocrd.u_xis_referrer =4 then N'אתר'

when ocrd.u_xis_referrer =5 then N'לקוח קיים'

when ocrd.u_xis_referrer =6 then N'ביקור סוכן' end

as N'גורם מפנה'

-- קו חלוקה, גורם מפנה?

from oinv inner join inv1 on oinv.docentry=inv1.DocEntry

inner join ORDR on ORDR.docnum=inv1.BaseRef and inv1.basetype=17

inner join ocrd on ocrd.cardcode=oinv.cardcode

inner join oslp on oslp.SlpCode=ocrd.SlpCode

where oinv.IsICT='N'

--ocrd.createdate=ORDR.docdate nbv

AND (OCRD.createdate>=@date1 OR @date1='')

AND (OCRD.createdate<=@date2 OR @date2='')

and ((oinv.docdate=OCRD.CREATEDATE) or (oinv.docdate<DATEADD(Day,14,OCRD.CREATEDATE)))

)

union all

(

select distinct

oinv.docnum,

N'חשבונית מס' AS docnum1,

oinv.cardcode,

ocrd.cardname,

(oinv.doctotal-oinv.VatSum) as totalSum,

oinv.docdate,

oslp.SlpName

,oinv.[U_XIS_SLPLine],

case when ocrd.u_xis_referrer =1 then N'אינטרנט'

when ocrd.u_xis_referrer =2 then N'לקוח'

when ocrd.u_xis_referrer =3 then N'ספק'

when ocrd.u_xis_referrer =4 then N'אתר'

when ocrd.u_xis_referrer =5 then N'לקוח קיים'

when ocrd.u_xis_referrer =6 then N'ביקור סוכן' end

--as N'גורם מפנה'

-- קו חלוקה, גורם מפנה?

from oinv inner join inv1 on oinv.docentry=inv1.DocEntry

inner join ocrd on ocrd.cardcode=oinv.cardcode

inner join oslp on oslp.SlpCode=ocrd.SlpCode

where inv1.Basetype <> 17 and IsICT='N'

AND (OCRD.createdate>=@date1 OR @date1='')

AND (OCRD.createdate<=@date2 OR @date2='')

and ((oinv.docdate=OCRD.CREATEDATE) or (oinv.docdate<DATEADD(Day,14,OCRD.CREATEDATE)))

)

union all

(select distinct ORDR.docnum,

N'הזמנה' AS docnum ,

ORDR.cardcode,

ORDR.cardname,

(doctotal-VatSum) as totalSum,

ORDR.docdate,

oslp.SlpName

,ordr.[U_XIS_SLPLine],

case when ocrd.u_xis_referrer =1 then N'אינטרנט'

when ocrd.u_xis_referrer =2 then N'לקוח'

when ocrd.u_xis_referrer =3 then N'ספק'

when ocrd.u_xis_referrer =4 then N'אתר'

when ocrd.u_xis_referrer =5 then N'לקוח קיים'

when ocrd.u_xis_referrer =6 then N'ביקור סוכן' end

--as N'גורם מפנה'

-- קו חלוקה, גורם מפנה?

from ORDR inner join ocrd on ocrd.cardcode=ordr.cardcode

inner join oslp on oslp.SlpCode=ocrd.SlpCode

--ocrd.createdate=ORDR.docdate nbv

where ordr.DocStatus='o'

and (OCRD.createdate>=@date1 OR @date1='')

AND (OCRD.createdate<=@date2 OR @date2='')

and ((ordr.docdate=OCRD.CREATEDATE) or (ordr.docdate<DATEADD(Day,14,OCRD.CREATEDATE)))

)

--------------------------------------------------------

It works fine, except some cases we cancel an invoice or order (because of any mistake), and then issue a new one. In this cases, the query doesn’t show this customer is a new customer (because we canceled the first document, now this is not the first anymore)

I am asking for your help to find the problem in those exclusive cases, which the query doesn’t show results.

Thanks for the help

[Vitaliy-R]

It would be helpful if you add the tag with the name of SAP product you are working on in addition to "SQL" tag. That would increase a number of people who can help you with the question 🙂

[former_member765529]

I have taged "SQL"