Display Customers that Purchased the Same Month they are Created in GP

— Display Customers that Purchased the Same Month they are Created in GP

— Variable Below …
— Change the XXXX in @Year = to the desired year
— Change the XX in @Month = to the desired month

DECLARE @Year nvarchar(4)
DECLARE @Month nvarchar(2)

Set @Year = XXXX
Set @Month = XX

SELECT

C.CUSTNMBR
,C.CUSTNAME
,C.CUSTCLAS
,C.CNTCPRSN
,C.SHIPMTHD
,C.ADDRESS1
,C.ADDRESS2
,C.ADDRESS3
,C.COUNTRY
,C.CITY
,C.STATE
,C.ZIP
,C.PHONE1
,C.PHONE2
,C.PHONE3
,C.FAX
,C.PRCLEVEL
,C.COMMENT1
,C.COMMENT2
,C.USERDEF1
,C.CREATDDT
,TTLSLYTD Total_Sales_YTD
,sop_header.sopnumbe
,sop_header.docdate
,IsNull(SOP_Header.NumberOrInvoices,0) NumberOrInvoices
,IsNull(SOP_Header.Sales,0) Sales
,IsNull(SOP_Header.Returns,0) Returns

FROM RM00101 C
JOIN RM00103 on C.CUSTNMBR = RM00103.CUSTNMBR

Left Join
(
SELECT CUSTNMBR, sopnumbe, docdate, COUNT(*) NumberOrInvoices, SUM(CASE SOPTYPE WHEN 3 THEN DOCAMNT ELSE 0 END) Sales, SUM(CASE SOPTYPE WHEN 4 THEN DOCAMNT ELSE 0 END) Returns FROM SOP30200
WHERE Year(DOCDATE) = @Year AND Month(DOCDATE) = @Month and SOPTYPE in (3,4) and VOIDSTTS = 0
GROUP BY CUSTNMBR, sopnumbe, docdate
)SOP_Header

ON C.CUSTNMBR = SOP_Header.CUSTNMBR
WHERE Year(C.CREATDDT) = @Year AND Month(C.CREATDDT) = @Month
AND Sales > 0