cancel
Showing results for 
Search instead for 
Did you mean: 

Item didn't sold to certain BP

former_member798634
Participant
0 Kudos

Good morning,

Who can help me with a query that shows me a list of BP who didn't buy a certain product? And if possible to choose a range of codes 'from code - to code'?

Thanks,

regards

Accepted Solutions (0)

Answers (1)

Answers (1)

Johan_H
Active Contributor

Hi Mark,

Please give this one a try:

/* select * from OITM i */
DECLARE @FROM AS NVARCHAR(50)
DECLARE @TO AS NVARCHAR(50)
SET @FROM = /* i.ItemCode */ '[%0]'
SET @TO = /* i.ItemCode */ '[%1]'
SELECT c.CardCode, c.CardName, c.CreateDate, g.GroupName
FROM OCRD c
     INNER JOIN OCRG g ON c.GroupCode = g.GroupCode
WHERE c.CardCode NOT IN (select h.CardCode 
                         from ORDR h
                              inner join RDR1 r on h.DocEntry = r.DocEntry
                         where r.ItemCode >= @FROM and r.ItemCode <= @TO)
ORDER BY 2

Regards,

Johan

former_member798634
Participant
0 Kudos

Hi Johan,

Looks good. Could you add the following?

- Inactive BP, don't show in list

- Add itemname in the list

Hope you can help me.

regards

Johan_H
Active Contributor

Hi Mark,

Considerably more complicated. Please try this.:

/* select * from OITM i */
DECLARE @FROM AS NVARCHAR(50)
DECLARE @TO AS NVARCHAR(50)
DECLARE @TBL AS TABLE(ArtikelNr NVARCHAR(50), Beschrijving NVARCHAR(100), InMagazijn INT, KlantNr NVARCHAR(50), Naam NVARCHAR(100), KlantIngevoerd DATETIME, KlantGroep NVARCHAR(100))

SET @FROM = /* i.ItemCode */ '[%0]'
SET @TO = /* i.ItemCode */ '[%1]'

DECLARE @ItemCode NVARCHAR(50)
DECLARE @ItemName NVARCHAR(100)
DECLARE @InMagazijn INT

DECLARE crsr CURSOR
FOR SELECT i.ItemCode, i.ItemName, i.OnHand
    FROM OITM i
	WHERE i.ItemCode >= @FROM and i.ItemCode <= @TO
	  AND ISNULL(i.frozenFor, 'N') = 'N'
OPEN crsr
FETCH NEXT FROM crsr
INTO @ItemCode, @ItemName, @InMagazijn
WHILE @@FETCH_STATUS = 0
 BEGIN
    /*** DO STUFF HERE ***/
	INSERT INTO @TBL
	SELECT @ItemCode, @ItemName, @InMagazijn, c.CardCode, c.CardName, c.CreateDate, g.GroupName
	FROM OCRD c
		 INNER JOIN OCRG g ON c.GroupCode = g.GroupCode
	WHERE c.CardCode NOT IN (select h.CardCode 
							 from ORDR h
								  inner join RDR1 r on h.DocEntry = r.DocEntry
							 where r.ItemCode = @ItemCode)
	  AND ISNULL(c.frozenFor, 'N') = 'N'

  FETCH NEXT FROM crsr
  INTO @ItemCode, @ItemName, @InMagazijn
 END
CLOSE crsr
DEALLOCATE crsr

SELECT * FROM @TBL

Regards,

Johan

former_member798634
Participant
0 Kudos

Hi Johan,

Thank you very much. It's working.

The only thing is that I also see my suppliers in the list and not only my customers. If you see a change, could you take a look in that part for me?

Regards

Johan_H
Active Contributor

Hi Mark,

Under this line:

AND ISNULL(c.frozenFor, 'N') = 'N'

add this line:

AND ISNULL(c.CardType, '') = 'C'

Regards,

Johan