Skip to Content
avatar image
adam cohen

please help :( SQL QUERY

Aug 28, 2017 at 08:03 AM | 74 Views | Last edit Aug 28, 2017 at 08:03 AM 2 rev. 
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
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Edy Simon
    Aug 28, 2017 at 09:46 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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