Skip to Content
0

HELP WITH SQL QUERY PLEASE

May 10 at 01:48 PM

102

avatar image

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

SQL
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

6 Answers

Nagarajan K May 10 at 01:55 PM
1

Hi,

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

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

Regards,

Nagarajan

Show 5 Share
10 |10000 characters needed characters left characters exceeded

many thanks my friend if this works

0

Please close this thread by marking correct answer

0

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

is this was it should look like along side the current where, i have added the "and"

0

Share complete query here to check and advise

0

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

0
Bret Halford
May 10 at 04:35 PM
0

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

Show 1 Share
10 |10000 characters needed characters left 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

0
CHRIS FOWLER May 14 at 01:49 PM
0

cant get it working bret, errors on me

Share
10 |10000 characters needed characters left characters exceeded
Nagarajan K May 15 at 01:27 PM
0

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

Show 4 Share
10 |10000 characters needed characters left characters exceeded

and D0.Cardcode NOT IN ( 'C00078')and D1.ItemCode NOT IN ('CHSE040')

AND I CAN KEEP ADDING THIS LINE CHANGING THE CARDCODE AND ITEMCODE FOR ADDITIONAL CUSOMTERS?

0

Yes, you can add required items and cardcode.

1

WHERE (D0.Cardname = $[$54.0.0] OR D0.CardCode = $[$4.0.0]) and D0.Cardcode NOT IN ('C00078') and D1.ItemCode NOT IN ('CHSE040') and D0.Cardcode NOT IN ('C000650') and D1.ItemCode NOT IN ('BAC002')

When I try to add the second customer the first works "c00078" but the second "c000650" now has no history at all.

Am I doing something wrong, I have around 10-15 of these lines to add in total

Thanks in advance

0

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

0
Nagarajan K May 14 at 02:03 PM
0

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

Show 6 Share
10 |10000 characters needed characters left characters exceeded

ok will try this thanks

0

No, result ran without errors, but only chse040 was displayed, I need to remove chse040 from the results for that cardcode only

0

Add a NOT operator and parenthesis around the added (bold) code above so that it reads

and NOT ( D0.Cardcode = 'C000111' and D1.ItemCode = 'CHSE040' )

1

and D0.Cardcode NOT IN ( 'C00078')and D1.ItemCode NOT IN ('CHSE040')

someone suggested the above and it seems to be working, slightly different to you line but seems ok. what do you think?

0
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

0

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 is working nicely, thanks

0
CHRIS FOWLER May 15 at 06:50 AM
0

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


capture.png (65.9 kB)
Share
10 |10000 characters needed characters left characters exceeded