Skip to Content

HELP WITH SQL QUERY PLEASE

Can one of you very kind people help me please

I need to amend a query to cover the following

from ordr

if CardCode = C000111 and

from rdr1

ItemCode = CHSE040 then hide or do not display item in history list

thats what im trying to achieve, but im confused by the current query, I can include this code if you wish

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

6 Answers

  • May 10 at 01:55 PM

    Hi,

    Add in your query under "Where" conditions as below,

    ORDR.Cardcode not in ('C000111') and RDR1.Itemcode NOT IN ('CHSE040')

    Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded

    • replace ORDR with D0 and RDR1 with D1 from the above query. you will get your result.

      From your above query I know alias name for ORDR is D0, check what alias name you use for RDR1 table. Most probably it should be D1.use correct one.

      Thanks

  • May 10 at 04:35 PM

    From your initial description, I think you are saying that the record should not be displayed only if both conditions are true for the result.
    Consider this simplified test case. Do you want none of the rows returned, or rows 1 and 2 returned?:

    create table t1 (id int, Cardcode char(7), Itemcode char(7) )
    go
    insert t1 values (1,'C000111', 'item')
    insert t1 values (2, 'card', 'CHSE040')
    insert t1 values (3, 'C000111', 'CHSE040')
    go

    select * from t1
    where
    Cardcode not in ('C000111') and Itemcode NOT IN ('CHSE040')

    id Cardcode Itemcode
    ----------- -------------- --------------

    (0 rows affected)

    select * from t1
    where
    not
    (Cardcode in ('C000111') and Itemcode IN ('CHSE040'))
    go

    ----------- -------------- --------------
    1 C000111 item
    2 card CHSE040

    (2 rows affected)

    So I suspect what you actually want is this:

    WHERE
    (
    D0.Cardname = $[$54.0.0]
    OR D0.CardCode = $[$4.0.0])
    AND NOT (
    ORDR.Cardcode IN ('C000111')
    AND RDR1.Itemcode IN ('CHSE040')
    )
    )

    Cheers,
    -bret

    Add comment
    10|10000 characters needed characters exceeded

    • I tried the bottom one, but get a error when its run. This is the original query, in this state it works.

      As you suggested above, the point was to remove the rows from the sales history if 2 conditions were met, a specific cardcode and a specific itemcode.

      Thanks for all your assistance

      Chris

      SELECT 'N' as [Selected], ItemCode, Item, Quantity, CAST(CAST([Qty This Month] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price This Month] AS decimal(12, 2))
      AS varchar(12)) + ')' AS [This Month], CAST(CAST([Qty Month -1] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -1] AS decimal(12, 2)) AS varchar(12))
      + ')' AS [Month -1], CAST(CAST([Qty Month -2] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -2] AS decimal(12, 2)) AS varchar(12)) + ')' AS [Month -2],
      CAST(CAST([Qty Month -3] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -3] AS decimal(12, 2)) AS varchar(12)) + ')' AS [Month -3],
      CAST(CAST([Qty Month -4] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -4] AS decimal(12, 2)) AS varchar(12)) + ')' AS [Month -4],
      CAST(CAST([Qty Month -5] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -5] AS decimal(12, 2)) AS varchar(12)) + ')' AS [Month -5],
      CAST(CAST([Qty Month -6] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -6] AS decimal(12, 2)) AS varchar(12)) + ')' AS [Month -6],
      CAST(CAST([Qty Month -7] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -7] AS decimal(12, 2)) AS varchar(12)) + ')' AS [Month -7],
      CAST(CAST([Qty Month -8] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -8] AS decimal(12, 2)) AS varchar(12)) + ')' AS [Month -8],
      CAST(CAST([Qty Month -9] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -9] AS decimal(12, 2)) AS varchar(12)) + ')' AS [Month -9],
      CAST(CAST([Qty Month -10] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -10] AS decimal(12, 2)) AS varchar(12)) + ')' AS [Month -10],
      CAST(CAST([Qty Month -11] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -11] AS decimal(12, 2)) AS varchar(12)) + ')' AS [Month -11],
      CAST(CAST([Qty Month -12] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -12] AS decimal(12, 2)) AS varchar(12)) + ')' AS [Month -12]
      FROM (SELECT DISTINCT TOP (100) PERCENT D3.ItmsGrpNam AS [Item Group], D1.ItemCode, /*D1.Dscription*/ D2.ItemName AS Item, 1 AS Quantity, CAST(ISNULL
      ((SELECT TotalQty
      FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1
      WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, 0, GETDATE()),
      112))), 0) AS varchar(10)) AS [Qty This Month], CAST(ISNULL
      ((SELECT Price
      FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1
      WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, 0, GETDATE()),
      112))), 0) AS varchar(12)) AS [Price This Month], ISNULL
      ((SELECT TotalQty
      FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1
      WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 1, GETDATE()),
      112))), 0) AS [Qty Month -1], ISNULL
      ((SELECT Price
      FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1
      WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 1, GETDATE()),
      112))), 0) AS [Price Month -1], ISNULL
      ((SELECT TotalQty
      FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1
      WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 2, GETDATE()),
      112))), 0) AS [Qty Month -2], ISNULL
      ((SELECT Price
      FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1
      WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 2, GETDATE()),
      112))), 0) AS [Price Month -2], ISNULL
      ((SELECT TotalQty
      FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1
      WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 3, GETDATE()),
      112))), 0) AS [Qty Month -3], ISNULL
      ((SELECT Price
      FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1
      WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 3, GETDATE()),
      112))), 0) AS [Price Month -3], ISNULL
      ((SELECT TotalQty
      FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1
      WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 4, GETDATE()),
      112))), 0) AS [Qty Month -4], ISNULL
      ((SELECT Price
      FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1
      WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 4, GETDATE()),
      112))), 0) AS [Price Month -4], ISNULL
      ((SELECT TotalQty
      FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1
      WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 5, GETDATE()),
      112))), 0) AS [Qty Month -5], ISNULL
      ((SELECT Price
      FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1
      WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 5, GETDATE()),
      112))), 0) AS [Price Month -5], ISNULL
      ((SELECT TotalQty
      FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1
      WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 6, GETDATE()),
      112))), 0) AS [Qty Month -6], ISNULL
      ((SELECT Price
      FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1
      WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 6, GETDATE()),
      112))), 0) AS [Price Month -6], ISNULL
      ((SELECT TotalQty
      FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1
      WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 7, GETDATE()),
      112))), 0) AS [Qty Month -7], ISNULL
      ((SELECT Price
      FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1
      WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 7, GETDATE()),
      112))), 0) AS [Price Month -7], ISNULL
      ((SELECT TotalQty
      FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1
      WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 8, GETDATE()),
      112))), 0) AS [Qty Month -8], ISNULL
      ((SELECT Price
      FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1
      WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 8, GETDATE()),
      112))), 0) AS [Price Month -8], ISNULL
      ((SELECT TotalQty
      FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1
      WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 9, GETDATE()),
      112))), 0) AS [Qty Month -9], ISNULL
      ((SELECT Price
      FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1
      WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 9, GETDATE()),
      112))), 0) AS [Price Month -9], ISNULL
      ((SELECT TotalQty
      FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1
      WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 10, GETDATE()),
      112))), 0) AS [Qty Month -10], ISNULL
      ((SELECT Price
      FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1
      WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 10, GETDATE()),
      112))), 0) AS [Price Month -10], ISNULL
      ((SELECT TotalQty
      FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1
      WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 11, GETDATE()),
      112))), 0) AS [Qty Month -11], ISNULL
      ((SELECT Price
      FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1
      WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 11, GETDATE()),
      112))), 0) AS [Price Month -11], ISNULL
      ((SELECT TotalQty
      FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1
      WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 12, GETDATE()),
      112))), 0) AS [Qty Month -12], ISNULL
      ((SELECT Price
      FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1
      WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 12, GETDATE()),
      112))), 0) AS [Price Month -12]
      FROM [SBO_VFF_LIVE].dbo.ORDR D0 INNER JOIN
      [SBO_VFF_LIVE].dbo.RDR1 D1 ON D0.DocEntry = D1.DocEntry INNER JOIN
      [SBO_VFF_LIVE].dbo.OITM D2 ON D1.ItemCode = D2.ItemCode INNER JOIN
      [SBO_VFF_LIVE].dbo.OITB D3 ON D2.ItmsGrpCod = D3.ItmsGrpCod

      WHERE (D0.Cardname = $[$54.0.0] OR D0.CardCode = $[$4.0.0])

      ORDER BY D1.ItemCode) AS InvHistory

  • May 14 at 01:49 PM

    cant get it working bret, errors on me

    Add comment
    10|10000 characters needed characters exceeded

  • May 15 at 01:27 PM

    Hi,

    Try this,

    SELECT 'N' as [Selected], ItemCode, Item, Quantity, CAST(CAST([Qty This Month] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price This Month] AS decimal(12, 2)) AS varchar(12)) + ')' AS [This Month], CAST(CAST([Qty Month -1] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -1] AS decimal(12, 2)) AS varchar(12)) + ')' AS [Month -1], CAST(CAST([Qty Month -2] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -2] AS decimal(12, 2)) AS varchar(12)) + ')' AS [Month -2], CAST(CAST([Qty Month -3] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -3] AS decimal(12, 2)) AS varchar(12)) + ')' AS [Month -3], CAST(CAST([Qty Month -4] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -4] AS decimal(12, 2)) AS varchar(12)) + ')' AS [Month -4], CAST(CAST([Qty Month -5] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -5] AS decimal(12, 2)) AS varchar(12)) + ')' AS [Month -5], CAST(CAST([Qty Month -6] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -6] AS decimal(12, 2)) AS varchar(12)) + ')' AS [Month -6], CAST(CAST([Qty Month -7] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -7] AS decimal(12, 2)) AS varchar(12)) + ')' AS [Month -7], CAST(CAST([Qty Month -8] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -8] AS decimal(12, 2)) AS varchar(12)) + ')' AS [Month -8], CAST(CAST([Qty Month -9] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -9] AS decimal(12, 2)) AS varchar(12)) + ')' AS [Month -9], CAST(CAST([Qty Month -10] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -10] AS decimal(12, 2)) AS varchar(12)) + ')' AS [Month -10], CAST(CAST([Qty Month -11] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -11] AS decimal(12, 2)) AS varchar(12)) + ')' AS [Month -11], CAST(CAST([Qty Month -12] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -12] AS decimal(12, 2)) AS varchar(12)) + ')' AS [Month -12] FROM (SELECT DISTINCT TOP (100) PERCENT D3.ItmsGrpNam AS [Item Group], D1.ItemCode, /*D1.Dscription*/ D2.ItemName AS Item, 1 AS Quantity, CAST(ISNULL ((SELECT TotalQty FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, 0, GETDATE()), 112))), 0) AS varchar(10)) AS [Qty This Month], CAST(ISNULL ((SELECT Price FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, 0, GETDATE()), 112))), 0) AS varchar(12)) AS [Price This Month], ISNULL ((SELECT TotalQty FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 1, GETDATE()), 112))), 0) AS [Qty Month -1], ISNULL ((SELECT Price FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 1, GETDATE()), 112))), 0) AS [Price Month -1], ISNULL ((SELECT TotalQty FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 2, GETDATE()), 112))), 0) AS [Qty Month -2], ISNULL ((SELECT Price FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 2, GETDATE()), 112))), 0) AS [Price Month -2], ISNULL ((SELECT TotalQty FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 3, GETDATE()), 112))), 0) AS [Qty Month -3], ISNULL ((SELECT Price FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 3, GETDATE()), 112))), 0) AS [Price Month -3], ISNULL ((SELECT TotalQty FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 4, GETDATE()), 112))), 0) AS [Qty Month -4], ISNULL ((SELECT Price FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 4, GETDATE()), 112))), 0) AS [Price Month -4], ISNULL ((SELECT TotalQty FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 5, GETDATE()), 112))), 0) AS [Qty Month -5], ISNULL ((SELECT Price FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 5, GETDATE()), 112))), 0) AS [Price Month -5], ISNULL ((SELECT TotalQty FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 6, GETDATE()), 112))), 0) AS [Qty Month -6], ISNULL ((SELECT Price FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 6, GETDATE()), 112))), 0) AS [Price Month -6], ISNULL ((SELECT TotalQty FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 7, GETDATE()), 112))), 0) AS [Qty Month -7], ISNULL ((SELECT Price FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 7, GETDATE()), 112))), 0) AS [Price Month -7], ISNULL ((SELECT TotalQty FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 8, GETDATE()), 112))), 0) AS [Qty Month -8], ISNULL ((SELECT Price FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 8, GETDATE()), 112))), 0) AS [Price Month -8], ISNULL ((SELECT TotalQty FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 9, GETDATE()), 112))), 0) AS [Qty Month -9], ISNULL ((SELECT Price FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 9, GETDATE()), 112))), 0) AS [Price Month -9], ISNULL ((SELECT TotalQty FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 10, GETDATE()), 112))), 0) AS [Qty Month -10], ISNULL ((SELECT Price FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 10, GETDATE()), 112))), 0) AS [Price Month -10], ISNULL ((SELECT TotalQty FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 11, GETDATE()), 112))), 0) AS [Qty Month -11], ISNULL ((SELECT Price FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 11, GETDATE()), 112))), 0) AS [Price Month -11], ISNULL ((SELECT TotalQty FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 12, GETDATE()), 112))), 0) AS [Qty Month -12], ISNULL ((SELECT Price FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 12, GETDATE()), 112))), 0) AS [Price Month -12]

    FROM [SBO_VFF_LIVE].dbo.ORDR D0 INNER JOIN [SBO_VFF_LIVE].dbo.RDR1 D1 ON D0.DocEntry = D1.DocEntry INNER JOIN [SBO_VFF_LIVE].dbo.OITM D2 ON D1.ItemCode = D2.ItemCode INNER JOIN [SBO_VFF_LIVE].dbo.OITB D3 ON D2.ItmsGrpCod = D3.ItmsGrpCod WHERE (D0.Cardname = $[$54.0.0] OR D0.CardCode = $[$4.0.0]) and D0.Cardcode NOT IN ( 'C000111')and D1.ItemCode NOT IN ('CHSE040')

    ORDER BY D1.ItemCode) AS InvHistory

    Add comment
    10|10000 characters needed characters exceeded

    • WHERE (D0.Cardname = $[$54.0.0] OR D0.CardCode = $[$4.0.0]) and NOT ( D0.Cardcode = 'C000078' and D1.ItemCode = 'CHSE040' ) and NOT ( D0.Cardcode = 'C000650' and D1.ItemCode = 'bac002' )

      this seems to be working

  • May 14 at 02:03 PM

    Please try attached query with added conditions.

    SELECT 'N' as [Selected], ItemCode, Item, Quantity, CAST(CAST([Qty This Month] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price This Month] AS decimal(12, 2)) AS varchar(12)) + ')' AS [This Month], CAST(CAST([Qty Month -1] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -1] AS decimal(12, 2)) AS varchar(12)) + ')' AS [Month -1], CAST(CAST([Qty Month -2] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -2] AS decimal(12, 2)) AS varchar(12)) + ')' AS [Month -2], CAST(CAST([Qty Month -3] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -3] AS decimal(12, 2)) AS varchar(12)) + ')' AS [Month -3], CAST(CAST([Qty Month -4] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -4] AS decimal(12, 2)) AS varchar(12)) + ')' AS [Month -4], CAST(CAST([Qty Month -5] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -5] AS decimal(12, 2)) AS varchar(12)) + ')' AS [Month -5], CAST(CAST([Qty Month -6] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -6] AS decimal(12, 2)) AS varchar(12)) + ')' AS [Month -6], CAST(CAST([Qty Month -7] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -7] AS decimal(12, 2)) AS varchar(12)) + ')' AS [Month -7], CAST(CAST([Qty Month -8] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -8] AS decimal(12, 2)) AS varchar(12)) + ')' AS [Month -8], CAST(CAST([Qty Month -9] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -9] AS decimal(12, 2)) AS varchar(12)) + ')' AS [Month -9], CAST(CAST([Qty Month -10] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -10] AS decimal(12, 2)) AS varchar(12)) + ')' AS [Month -10], CAST(CAST([Qty Month -11] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -11] AS decimal(12, 2)) AS varchar(12)) + ')' AS [Month -11], CAST(CAST([Qty Month -12] AS decimal(12, 0)) AS varchar(8)) + ' (£' + CAST(CAST([Price Month -12] AS decimal(12, 2)) AS varchar(12)) + ')' AS [Month -12] FROM (SELECT DISTINCT TOP (100) PERCENT D3.ItmsGrpNam AS [Item Group], D1.ItemCode, /*D1.Dscription*/ D2.ItemName AS Item, 1 AS Quantity, CAST(ISNULL ((SELECT TotalQty FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, 0, GETDATE()), 112))), 0) AS varchar(10)) AS [Qty This Month], CAST(ISNULL ((SELECT Price FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, 0, GETDATE()), 112))), 0) AS varchar(12)) AS [Price This Month], ISNULL ((SELECT TotalQty FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 1, GETDATE()), 112))), 0) AS [Qty Month -1], ISNULL ((SELECT Price FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 1, GETDATE()), 112))), 0) AS [Price Month -1], ISNULL ((SELECT TotalQty FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 2, GETDATE()), 112))), 0) AS [Qty Month -2], ISNULL ((SELECT Price FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 2, GETDATE()), 112))), 0) AS [Price Month -2], ISNULL ((SELECT TotalQty FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 3, GETDATE()), 112))), 0) AS [Qty Month -3], ISNULL ((SELECT Price FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 3, GETDATE()), 112))), 0) AS [Price Month -3], ISNULL ((SELECT TotalQty FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 4, GETDATE()), 112))), 0) AS [Qty Month -4], ISNULL ((SELECT Price FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 4, GETDATE()), 112))), 0) AS [Price Month -4], ISNULL ((SELECT TotalQty FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 5, GETDATE()), 112))), 0) AS [Qty Month -5], ISNULL ((SELECT Price FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 5, GETDATE()), 112))), 0) AS [Price Month -5], ISNULL ((SELECT TotalQty FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 6, GETDATE()), 112))), 0) AS [Qty Month -6], ISNULL ((SELECT Price FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 6, GETDATE()), 112))), 0) AS [Price Month -6], ISNULL ((SELECT TotalQty FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 7, GETDATE()), 112))), 0) AS [Qty Month -7], ISNULL ((SELECT Price FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 7, GETDATE()), 112))), 0) AS [Price Month -7], ISNULL ((SELECT TotalQty FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 8, GETDATE()), 112))), 0) AS [Qty Month -8], ISNULL ((SELECT Price FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 8, GETDATE()), 112))), 0) AS [Price Month -8], ISNULL ((SELECT TotalQty FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 9, GETDATE()), 112))), 0) AS [Qty Month -9], ISNULL ((SELECT Price FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 9, GETDATE()), 112))), 0) AS [Price Month -9], ISNULL ((SELECT TotalQty FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 10, GETDATE()), 112))), 0) AS [Qty Month -10], ISNULL ((SELECT Price FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 10, GETDATE()), 112))), 0) AS [Price Month -10], ISNULL ((SELECT TotalQty FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 11, GETDATE()), 112))), 0) AS [Qty Month -11], ISNULL ((SELECT Price FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 11, GETDATE()), 112))), 0) AS [Price Month -11], ISNULL ((SELECT TotalQty FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 12, GETDATE()), 112))), 0) AS [Qty Month -12], ISNULL ((SELECT Price FROM [SBO_VFF_LIVE].dbo.CT_Sales_Order_Lines_By_BP_V AS T1 WHERE (CardCode = D0.CardCode) AND (ItemCode = D1.ItemCode) AND (DocDate = CONVERT(varchar(6), DATEADD(m, - 12, GETDATE()), 112))), 0) AS [Price Month -12]

    FROM [SBO_VFF_LIVE].dbo.ORDR D0 INNER JOIN [SBO_VFF_LIVE].dbo.RDR1 D1 ON D0.DocEntry = D1.DocEntry INNER JOIN [SBO_VFF_LIVE].dbo.OITM D2 ON D1.ItemCode = D2.ItemCode INNER JOIN [SBO_VFF_LIVE].dbo.OITB D3 ON D2.ItmsGrpCod = D3.ItmsGrpCod WHERE (D0.Cardname = $[$54.0.0] OR D0.CardCode = $[$4.0.0]) and D0.Cardcode = 'C000111' and D1.ItemCode = 'CHSE040'

    ORDER BY D1.ItemCode) AS InvHistory

    Add comment
    10|10000 characters needed characters exceeded

    • There is no logical difference between NOT = and NOT IN (<single value>)

      However, there is a logical difference between

      NOT (a and b) - eliminates results with both of the specified values

      NOT a AND NOT b -- eliminates results where either one or both values exist.

      So "ok" depends on the desired result.

      Cheers,
      -bret

  • May 15 at 06:50 AM

    capture.png

    This image is the query results without the extra line added, if you see that chse040 has been previously added to a sales order so its been returned as sales history in the query results, I would now like to remove that itemcode from being displayed in this customers history

    Add comment
    10|10000 characters needed characters exceeded