Skip to Content
0

please help :( SQL QUERY

Aug 28, 2017 at 08:03 AM

72

avatar image
adam cohen 
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 :)

SAP Business One
SAP Business One SDK
Answers Answers and Comments
10 |10000 characters needed characters left characters exceeded
▼ Viewable by all users
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Sort by:
Best Answer
avatar image
Edy Simon Answered Aug 28, 2017 at 09:46 AM
0

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

Show 2 Share
10 |10000 characters needed characters left characters exceeded
▼ Viewable by all users
adam cohen
Aug 28, 2017 at 12:57 PM

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)

0
Edy Simon
Aug 29, 2017 at 12:59 AM

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

0
Comment