Sales Frequency Report Query

[Former Member]

Hello,

I am trying to generate a query that shows the # of times a BP has ordered, the date of their first order, the amount, the date of their most recent order, the amount, and the total amount of all orders placed.

The Sales Analysis Report that is provided with Business One is close, but does not provide the dates of first order and most recent order. If I could get the SQL query of the report, I could probably tweak it to my needs, but it doesn't provide you with the SQL query used to generate the report.

Any ideas?

Thanks,

Dan

[a_grootens · Accepted solution]

Hi Daniel,

Pfft. Took me some time, but I think this might be usefull for you.

Declare @TMP_ORDR TABLE (CardCode NVARCHAR(30), DocEntry int, MinDate date, MaxDate date, MinDocEntry int, MaxDocEntry int)

INSERT INTO @TMP_ORDR

(CardCode, DocEntry, MinDate, MaxDate)

SELECT CardCode, Count(DocEntry), min(DocDate), Max(DocDate) FROM ORDR with(NOLOCK) WHERE CANCELED='N' GROUP BY CardCode

UPDATE A

SET A.MinDocEntry=(SELECT min(B.DocEntry) FROM ORDR B with(NOLOCK) WHERE B.DocDate=A.MinDate and B.CardCode=A.CardCode and B.CANCELED='N')

, A.MaxDocEntry=(SELECT max(B.DocEntry) FROM ORDR B with(NOLOCK) WHERE B.DocDate=A.MaxDate and B.CardCode=A.CardCode and B.CANCELED='N')

FROM @TMP_ORDR A

SELECT A.DocEntry as [Nr orders], A.CardCode, D.CardName, A.MinDate as [First Order Date], B.DocNum as [First Ordernr.], B.DocTotal as [First OrderAmnt], A.MaxDate as [Last order date], C.DocNum as [Last Ordernr.], C.DocTotal as [Last order amnt]

FROM @TMP_ORDR A

INNER JOIN (SELECT DocEntry, DocNum, DocTotal FROM ORDR with(NOLOCK)) B ON A.MinDocEntry=B.DocEntry

INNER JOIN (SELECT DocEntry, DocNum, DocTotal FROM ORDR with(NOLOCK)) C ON A.MaxDocEntry=C.DocEntry

INNER JOIN OCRD D with(NOLOCK) ON A.CardCode=D.CardCode

ORDER BY A.CardCode

Kind regards,

Andy Grootens

Asecom

[Former Member · Accepted solution]

Thank you very much. This is very close to what I need. I'm trying to add promo code/ad code field to it from ORDR table, Project column, but am having trouble. Can you help with this so I can see the Ad Code used on the first order and most recent order?

Thank you,

Dan

[a_grootens · Accepted solution]

Hi Dan,

Are the promo code and adcode user defined fields? If yes on header or rowlevel?

Regards,
Andy

[Former Member · Accepted solution]

No, it's actually called Project Code and its in ORDR, Project.

-Dan

[a_grootens · Accepted solution]

Hi Dan,

Please give this a try. It's not tested, because I don't have any projects in my testadministration:

Declare @TMP_ORDR TABLE (CardCode NVARCHAR(30), FirstProject nvarchar(20), LastProject nvarchar(20), DocEntry int, MinDate date, MaxDate date, MinDocEntry int, MaxDocEntry int)

INSERT INTO @TMP_ORDR

(CardCode, DocEntry, FirstProject, LastProject, MinDate, MaxDate)

SELECT CardCode, Count(DocEntry), min(Project), max(project), min(DocDate), Max(DocDate) FROM ORDR with(NOLOCK) WHERE CANCELED='N'

GROUP BY CardCode

UPDATE A

SET A.MinDocEntry=(SELECT min(B.DocEntry) FROM ORDR B with(NOLOCK) WHERE B.DocDate=A.MinDate and B.CardCode=A.CardCode and B.CANCELED='N')

, A.MaxDocEntry=(SELECT max(B.DocEntry) FROM ORDR B with(NOLOCK) WHERE B.DocDate=A.MaxDate and B.CardCode=A.CardCode and B.CANCELED='N')

, FirstProject = (SELECT min(B.Project) FROM ORDR B with(NOLOCK) WHERE B.Project = A.FirstProject And B.CardCode = A.CardCode and B.CANCELED = 'N')

, LastProject = (SELECT max(B.Project) FROM ORDR B with(NOLOCK) WHERE B.Project = A.LastProject And B.CardCode = A.CardCode and B.CANCELED = 'N')

FROM @TMP_ORDR A

SELECT A.DocEntry as [Nr orders], A.CardCode, D.CardName, A.MinDate as [First Order Date], B.DocNum as [First Ordernr.], E.PrjName, B.DocTotal as [First OrderAmnt], A.MaxDate as [Last order date], C.DocNum as [Last Ordernr.], F.PrjName, C.DocTotal as [Last order amnt]

FROM @TMP_ORDR A

INNER JOIN (SELECT DocEntry, DocNum, DocTotal FROM ORDR with(NOLOCK)) B ON A.MinDocEntry=B.DocEntry

INNER JOIN (SELECT DocEntry, DocNum, DocTotal FROM ORDR with(NOLOCK)) C ON A.MaxDocEntry=C.DocEntry

INNER JOIN OCRD D with(NOLOCK) ON A.CardCode=D.CardCode

LEFT JOIN OPRJ E on A.FirstProject = E.PrjCode

LEFT JOIN OPRJ F on A.LastProject = F.PrjCode

ORDER BY A.CardCode

Kind regards,

Andy Grootens

Asecom

[Former Member · Accepted solution]

Andy,

Thank you very much. This is very close to what we need. My company just asked if we could add a column for total sales amount for each bp, now that we have first order amount, most recent order amount, and they're looking for a total order amount. I will try to do this one myself with the query you've created thus far. Hopefully I won't need to ask you for more help.

Again, thank you very much.

Dan

[a_grootens · Accepted solution]

Hi Dan,

That is not so dificult. Please try:

Declare @TMP_ORDR TABLE (CardCode NVARCHAR(30), FirstProject nvarchar(20), LastProject nvarchar(20), DocEntry int, MinDate date, MaxDate date, MinDocEntry int, MaxDocEntry int, TotOrderAmnt numeric(16,9))

INSERT INTO @TMP_ORDR

(CardCode, DocEntry, FirstProject, LastProject, MinDate, MaxDate, TotOrderAmnt)

SELECT CardCode, Count(DocEntry), min(Project), max(project), min(DocDate), Max(DocDate), SUM(DocTotal) FROM ORDR with(NOLOCK) WHERE CANCELED='N'

GROUP BY CardCode

UPDATE A

SET A.MinDocEntry=(SELECT min(B.DocEntry) FROM ORDR B with(NOLOCK) WHERE B.DocDate=A.MinDate and B.CardCode=A.CardCode and B.CANCELED='N')

, A.MaxDocEntry=(SELECT max(B.DocEntry) FROM ORDR B with(NOLOCK) WHERE B.DocDate=A.MaxDate and B.CardCode=A.CardCode and B.CANCELED='N')

, FirstProject = (SELECT min(B.Project) FROM ORDR B with(NOLOCK) WHERE B.Project = A.FirstProject And B.CardCode = A.CardCode and B.CANCELED = 'N')

, LastProject = (SELECT max(B.Project) FROM ORDR B with(NOLOCK) WHERE B.Project = A.LastProject And B.CardCode = A.CardCode and B.CANCELED = 'N')

FROM @TMP_ORDR A

SELECT A.DocEntry as [Nr orders], A.CardCode, D.CardName, A.MinDate as [First Order Date], B.DocNum as [First Ordernr.], E.PrjName, B.DocTotal as [First OrderAmnt], A.MaxDate as [Last order date], C.DocNum as [Last Ordernr.], F.PrjName, C.DocTotal as [Last order amnt], A.TotOrderAmnt

FROM @TMP_ORDR A

INNER JOIN (SELECT DocEntry, DocNum, DocTotal FROM ORDR with(NOLOCK)) B ON A.MinDocEntry=B.DocEntry

INNER JOIN (SELECT DocEntry, DocNum, DocTotal FROM ORDR with(NOLOCK)) C ON A.MaxDocEntry=C.DocEntry

INNER JOIN OCRD D with(NOLOCK) ON A.CardCode=D.CardCode

LEFT JOIN OPRJ E on A.FirstProject = E.PrjCode

LEFT JOIN OPRJ F on A.LastProject = F.PrjCode

ORDER BY A.CardCode

If this is your answer, please mark this tread as correct or helpfull.

Kind regards,

Andy

[kothandaraman_nagarajan]

Hi,

Please check this thread Query to list BP information and First Order Date | SCN

Thanks