please help :( SQL QUERY

[Former Member]

SELECT  T1.[DocEntry], T0.[DocDate],T0.[CardCode], T0.[CardName], sum(T1.[Quantity]) as 'quantity'

FROM ODLN T0  INNER JOIN DLN1 T1 ON T0.[DocEntry] = T1.[DocEntry] 

WHERE (T0.[DocDate] >=[%0] or [%0]='') AND  (T0.[DocDate] <=[%1] or [%1]='') 
GROUP BY T1.[DocEntry], T0.[DocDate], T0.[CardCode], T0.[CardName]

order by T1.[DocEntry]

hi everyone

i need a bit of help please 😞

i wrote this query which lets me choose dates and then shows me all of the delivery documents from the ODLN table that were created at that date, with the columns i chose.

as u can see i have a column called "quantity" which also shows me the total amount of cases in that document

what im trying to achieve is another column that will show me the total amount of cases in that document but *from a specific item*

and also 4 more columns that will show me the total amount of cases in that document but only from items that are in a specific warehouse (OWHS, whsecode)

for example column4 should show the total cases in that document for items that are in wharehouse 4

column5 should show items from wharehouse 5

and so on..

THANK YOU

sorry for my english

i tried my best 🙂

[edy_simon · Accepted solution]

Hi Adam

SELECT  T1.[DocEntry], T0.[DocDate],T0.[CardCode], T0.[CardName], sum(T1.[Quantity]) as 'quantity'
	, sum(Case T1.ItemCode WHEN 'SPECIFIC_ITEM' THEN T1.Quantity ELSE 0 END) as 'SumOfQtyOfSpecificItem'
	, sum(Case T1.WhsCode WHEN 'WHS_01' THEN T1.Quantity ELSE 0 END) as 'SumOfQtyInWHS_01'
	, sum(Case T1.WhsCode WHEN 'WHS_02' THEN T1.Quantity ELSE 0 END) as 'SumOfQtyInWHS_02'
	, sum(Case T1.WhsCode WHEN 'WHS_03' THEN T1.Quantity ELSE 0 END) as 'SumOfQtyInWHS_03'
	, sum(Case T1.WhsCode WHEN 'WHS_04' THEN T1.Quantity ELSE 0 END) as 'SumOfQtyInWHS_04'
FROM ODLN T0  INNER JOIN DLN1 T1 ON T0.[DocEntry] = T1.[DocEntry] 
WHERE (T0.[DocDate] >=[%0] or [%0]='') AND  (T0.[DocDate] <=[%1] or [%1]='') 
GROUP BY T1.[DocEntry], T0.[DocDate], T0.[CardCode], T0.[CardName]
order by T1.[DocEntry]

Regards

Edy

[Former Member · Accepted solution]

hi edy

thanks for the fast reply

my problem is that the warehouse in the final document turns to "5" in all of the items in the document

thats because during the picking of the items - they all end at whs_5 .. and thats when the document is created

what i need, for example, is "column1" and it will show the total amount of cases in that document only for items that are not locked for whs_01 in the inventory details of the item (OITW table)

[edy_simon · Accepted solution]

Hi Adam,

From my sample query, you should be able to get the idea of how you should write your query.
Inside the CASE WHEN, you can include any subquery you need to implement your own logic.

Regards
Edy