I started out as a relative novice to SQL a few months ago. Now I use SQL queries in Business One almost daily. I wanted to share with you some of my most frequently used queries and why I like them. Just copy and paste these in to your own Query Generator

If you like SQL want to learn more we’d love to see you at the next Business One User Group at SAP UK: UK and Ireland SAP User Group – Developing, Benefiting and Serving the SAP User Community

We are on SAP 9.0 PL07

Open Invoices Not Emailed:

This query shows all AP Invoices added to SAP that are Open but have not been sent. It’s easy to forget to send invoices and this report has helped us be more efficient. Please note we are using Boyum Print and Deliver module:

SELECT t0.DocEntry, t0.CardCode, t0.CardName, t0.DocDueDate as ‘Due Date’, t0.DocCur, t0.DocTotal

FROM OINV t0

WHERE t0.DocStatus = ‘O’ and (t0.Printed = ‘N’ and (SELECT count(*)

FROM [dbo].[@BOY_85_DEL_LOG]  s0

WHERE s0.[U_BOY_id]  = t0.DocEntry and  s0.[U_BOY_OBJECTID]  = ’13’ and  s0.[U_BOY_ACTION]  = 4) = 0)

Partial Stock Take

We needed a way to list items in stock AND items not in stock for stock taking so we can run an inventory report between locations. The Bin Locations Content report in SAP only shows items in stock:

select t0.ItemCode, t1.ItemName, isnull(t3.OnHandQty,0) as ‘In Stock’, t2.BinCode, t2.SL1Code, t2.SL2Code from OITW T0

inner join OITM t1 on t0.ItemCode = t1.ItemCode

inner join OBIN t2 on t2.AbsEntry = T0.DftBinAbs

left outer join OIBQ t3 on t0.ItemCode=t3.ItemCode and t0.WhsCode = t3.WhsCode

where t2.BinCode between [%0] and [%1]

order by t2.SL1Code, t2.SL2Code, t0.ItemCode

Items with No Default Bin Location

We want to report on all items in SAP not assigned a Default Bin Location. This has helped our Warehouse Manager to ensure ALL stock has a Default Bin:

SELECT T0.[ItemCode], T0.[WhsCode], T0.[DftBinAbs] FROM OITW T0 WHERE T0.[WhsCode]  = ‘main’ and  T0.[DftBinAbs]  is null

Purchase Invoice Analysis

The SAP Purchase Analysis report is in System Currency, mine is GBP. No use to me when I buy in EUR. This report shows all AR Invoices for a set period for a particular Business Partner in the currency on the Invoice (FC):

SELECT T0.[DocEntry], T0.[CardCode], T0.[DocDate], T0.[DocTotalFC]

FROM OPCH T0

WHERE T0.[DocDate] BETWEEN [%0] AND [%1]  AND T0.[CardCode] =[%2]

If you want to run the same report but on Purchase Orders, simply substitute OPCH for OPOR

Top 10 Customers (Thanks to Zal Parchem for this)

SELECT TOP 10 T0.CardCode, MAX(T0.Cardname) as Customer,  SUM(T0.doctotal) as “Amount(LC)”

FROM dbo.OINV T0

WHERE t0.docdate BETWEEN [%0] AND [%1]

GROUP BY T0.CardCode

Order by SUM(T0.doctotal) DESC

Sales History by Item Group

Yes I know this exists in Sales Analysis reports but I prefer my layout:

SELECT T0.[DocEntry], T0.[DocDate],T0.[CardName], T2.[ItmsGrpCod], T2.[ItemCode], T2.[ItemName], T1.[Quantity]

FROM ORDR T0  INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode

WHERE T2.[ItmsGrpCod] = [%0] AND T0.[DocDate] BETWEEN [%1] AND [%2]

Sales Orders with no SLP Code

Some of our Sales People forget to enter their name when adding a Sales Order. This handy report helps me identify these quickly and easily

SELECT T1.[DocNum], T1.[CardName], T1.[DocDate], T0.[SlpCode]

FROM OSLP T0  INNER JOIN ORDR T1 ON T0.SlpCode = T1.SlpCode

WHERE T0.[SlpCode] = ‘-1’

ORDER BY T0.[SlpCode]

If anyone has any suggestions on how to improve these, or you would like to share some of your own SQL Queries please feel free. It’s good to share!

To report this post you need to login first.

37 Comments

You must be Logged on to comment or reply to a post.

    1. Tim Guest Post author

      Dude, make sure you go to “View” then “System Information” it will give you the name of the tables and fields when you hover over a Business Object. Like OCRD,CardCode is BP account number.

      Then go to Query Generator, enter the names of the tables you want to query and click attributes to be brought in. SAP makes the Joins for you.

      I also refer to “Mastering SQL Queries for SAP Business One” book by Gordon Du very often

      (0) 
  1. Gareth Ryan

    Hi Tim,

    I don’t know anything about B1 but thought I’d share just a couple of suggestions from my years of meddling with SQL in various guises…

    Are you able to control the table aliases used by the Query generator?  If so, I’d probably re-write your statements such that:

    select t0.ItemCode, t1.ItemName, isnull(t3.OnHandQty,0) as ‘In Stock’, t2.BinCode, t2.SL1Code, t2.SL2Code from OITW T0

    inner join OITM t1 on t0.ItemCode = t1.ItemCode

    inner join OBIN t2 on t2.AbsEntry = T0.DftBinAbs

    left outer join OIBQ t3 on t0.ItemCode=t3.ItemCode and t0.WhsCode = t3.WhsCode

    where t2.BinCode between [%0] and [%1]

    order by t2.SL1Code, t2.SL2Code, t0.ItemCode

    becomes:

    select    OITW.ItemCode,

                    OITM.ItemName,

                    isnull(OIBQ.OnHandQty,0) as ‘In Stock’,

                    OBIN.BinCode,

                    OBIN.SL1Code,

                    OBIN.SL2Code

                    from OITW OITW

                                    inner join OITM OITM on OITW.ItemCode = OITM.ItemCode

                                    inner join OBIN OBIN on OBIN.AbsEntry = OITW.DftBinAbs

                                    left outer join OIBQ OIBQ on OITW.ItemCode=OIBQ.ItemCode

                                                    and OITW.WhsCode = OIBQ.WhsCode

                    where OBIN.BinCode between [%0] and [%1]

                    order by OBIN.SL1Code,

                                     OBIN.SL2Code,

                                     OITW.ItemCode

    You will notice I’ve changed the t0, t1, etc. aliases to be the actual table names.  I just think this makes queries more readable, especially when you have numerous joins or where conditions.  You don’t have to use the table names but something more meaningful than t0, t1, etc. is often a big help.

    Also, you may already know but the “as ‘In Stock'” addition to a selected field can be used against all fields in your select statement, not just those that are calculated as part of the SQL execution.  So you could append the statement to be something like:

    select    OITW.ItemCode as ‘Item Code’,

                    OITM.ItemName as ‘Item Description/Name’,


    etc.  Sometimes this just helps to get better column headers or if you are then moving the query results into something else, such as Lumira or Excel or whatever.


    Of course, YMMV. 🙂


    Cheers,

    Gareth.

    (0) 
    1. Tim Guest Post author

      Gareth – Thanks for the input. The end result is the same but I agree it is so much easier to read. We are a bit restricted in Business One as Query Generator does a lot of the work for us but I can see how tidying it up will make it more readable.

      (0) 
      1. Gareth Ryan

        Hi Tim,

        Yes of course, the outcome is the same regardless and often more important than how your SQL looks.  I just know from painful experience how much easier it is to figure out issues when you can read your statement without squinting!

        I wonder if there is any mileage in using an external SQL editor, such as the Oracle SQL Developer or MySQL Workbench?  So you could use the B1 query generator to get the framework and then a bit of copying and pasting you could knock up quite extensive SQL statements.

        All depends on whether this sort of interoperability is possible and whether you need this level of control I guess.

        Cheers,

        Gareth.

        (0) 
        1. Tim Guest Post author

          We’re looking at SQL during our next User Group meeting so I will mention it to the expert. (From USA) At present and at my level I’ll just stick to the basic tools!

          (0) 
    2. Tim Guest Post author

      Gareth – After trying this way in the real world I’m going to have to disagree with you. I’ve got several long queries set up that I use on Sales Orders ORDR table. If I want to run the same query but for Quotes I simply change ORDR to OQUT in the FROM section once. With your way I have to change it many times! 🙂

      I do agree yours is easier to understand though.

      (0) 
      1. Johan Hakkesteegt

        Hi Tim,

        With this table alias business you can actually have your cake and eat it too.

        Gareth used the table names as aliases, which in a sense is the same as not really using aliases. However, using the table names is indeed the most common “best practice” in query writing.

        Now about eating the cake, here is how you can make the query readable, and maintain interchangeability:

        SELECT i.ItemCode

                      ,i.ItemName

                      ,SUM(r.Quantity) AS Quantity

        FROM OITM i /*<– “i” as in items */

                   LEFT OUTER JOIN RDR1 r /*<– “r” as in document rows */

                                                 ON i.ItemCode = r.ItemCode

                  LEFT OUTER JOIN ORDR h /*<– “h” as in document header */

                                                 ON r.DocEntry = h.DocEntry

        WHERE h.DocDate = [%0]

        GROUP BY i.ItemCode

                          ,i.ItemName


        Now the aliases tell you the most important basic property of the field’s identity in a glance, and you can still replace RDR1 with DLN1, or INV1, etc. without having to completely rewrite your query.

        Additionally, by the time you are ready to start using MS SQL Management Studio to write those queries, it is a lot quicker to write “i” than “OITM”.

        Regards,

        Johan

        (0) 
    3. romil sikka

      Hii Experts,

      Please help with this query.

      My query :- I want to send automatic email to those BP whose Invoice due date has come. If they have made the payment then message will not send.

      Please help me.

      Thanks

      (0) 
    4. romil sikka

      Hii Experts,

      Please help with this query.

      My query :- I want to send automatic email to those BP whose Invoice due date has come. If they have made the payment then message will not send.

      Please help me.

      Thanks

      (0) 
      1. DARSHAN DESAI

        Hi Tim,

        Thanks for the wonderful queries listed. Even I am  learning from the Query generator and the default tables.

        In the query of top 10 Customers listed above when we execute it the last column

        Amount(LC) is taken from which document and what exactly the amount signifies in the context.

        As that understanding is important for me to know

        Thanks & Regards

        Darshan Desai

        (0) 
        1. Tim Guest Post author

          Hi Sir, Amount (LC) is the total amount taken from OINV AR Invoice. LC is your system currency.

          I am working on taking away in AR Credit Notes to get the figures more accurate.

          Any suggestions would be appreciated.

          (0) 
          1. DARSHAN DESAI

            Hii Tim,

            Thanks for the reply I got it. In default sales analysis report based on A/R Invoice report the A/R Credit memo are deducted and it shows the deducted. So there is total difference  Even I will work on it and will post any ways thank you so much for the rest of the queries.

            Regards

            Darshan Desai

            (0) 
  2. Nagarajan K

    Hi Tim,

    Why did you select t0.docentry in Purchase invoice analysis? You can use T0.docnumber instead of t0.docentry.

    Thanks & Regards,

    Nagarajan

    (0) 
    1. Tim Guest Post author

      Hi Neetu – I’m just working on the format of the queries then will post to the Wiki

      I think this WiKi needs to be publicized more. So many Business One users have useful SQL it’s really great if we can share.

      (0) 
  3. Kevin Vos

    Hi Tim,

    Thank you for sharing those queries.

    There is an important factor that we all should be aware when writing SQL queries both for reporting, alerts or approbations, but almost all of us forgot: the row lock.

    In a nutshell, when a user adds/changes a document, the rows in header and lines tables are locked.

    Even when you launch a simple report (SELECT), by the time the query reads the rows, they are locked.

    So in order to not penalising users transacting in SAP Business One, we should add “with nolock” to all the queries.

    SELECT * FROM RDR1 WITH (NOLOCK) WHERE….

    This works fine when you do not care if the data has not yet been ‘committed’ to the database, for instance a report showing the top 5 customers (certainly, even with a missing (Not Committed yet) row, the customer will still be in the top 5).

    But when using more precise reports, i.e. an approbation on customer credit limit that will stop (or not) a POS transaction, you would not use the (NOLOCK).

    This, I promise, will help on your database health and SQL server speed.

    Thanks and regards,

    Frédéric Bada

    (0) 
      1. Kevin Vos

        I didn’t either until years ago when I had a WebEx session with SAP support regarding a slow server with huge reporting queries on SQL 2008.

        We are learning every day 🙂

        Looking forwards to your next blog,

        Frédéric

        (0) 
  4. Md Akramul Islam

    Hi Tim Guest

    I am trying to make modification an excel report as from Inventory Audit Report Criteria  the table OINM & OITM I had used to find the results. I have attached a picture which I need as a output into SAP system.On the below is my desire output needed report.JPG from the Query but not getting proper one. Any one have any suggestions for this.

    Right now getting this

    report 2.PNG

    (0) 
  5. José Severs

    I have several queries. They worked fine.

    But now the year has changed several queries don’t work any more.

    I think I need to add a year selection to the query some how, but I have no idea how to do this.

    Can anyone help me, to get the queries working again?

    For example, I have this query for revenue per customer per month.

    I want to run it to show the results for the whole of 2014. An of course later on also the months in 2015.

    SELECT     TOP (100) PERCENT t1.ShortName, t2.CardName,

    SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 1 AND year(duedate) = year(getdate()) THEN debit – credit END) AS Januari,

    SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 2 AND year(duedate) = year(getdate()) THEN debit – credit END) AS Februari,

    SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 3 AND year(duedate) = year(getdate()) THEN debit – credit END) AS Maart,

    SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 4 AND year(duedate) = year(getdate()) THEN debit – credit END) AS April,

    SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 5 AND year(duedate) = year(getdate()) THEN debit – credit END) AS Mei,

    SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 6 AND year(duedate) = year(getdate()) THEN debit – credit END) AS Juni,

    SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 7 AND year(duedate) = year(getdate()) THEN debit – credit END) AS Juli,

    SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 8 AND year(duedate) = year(getdate()) THEN debit – credit END) AS Augustus,

    SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 9 AND year(duedate) = year(getdate()) THEN debit – credit END) AS September,

    SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 10 AND year(duedate) = year(getdate()) THEN debit – credit END) AS Oktober,

    SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 11 AND year(duedate) = year(getdate()) THEN debit – credit END) AS November,

    SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 12 AND year(duedate) = year(getdate()) THEN debit – credit END) AS December,

    FROM dbo.JDT1 AS t1

    LEFT OUTER JOIN dbo.OCRD AS t2 ON t2.CardCode = t1.ShortName

    LEFT OUTER JOIN dbo.OCRD AS t3 ON t3.CardCode = t2.CardName

    WHERE     (t2.CardType = ‘c’)

    GROUP BY t1.ShortName,t2.CardName

    ORDER BY t1.ShortName

    (0) 
    1. J. Dielemans

      Hello José

      Here is your Query for 2014:

      SELECT     TOP (100) PERCENT t1.ShortName, t2.CardName,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 1 AND year(duedate) = 2014 THEN debit – credit END) AS Januari,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 2 AND year(duedate) = 2014 THEN debit – credit END) AS Februari,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 3 AND year(duedate) = 2014 THEN debit – credit END) AS Maart,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 4 AND year(duedate) = 2014 THEN debit – credit END) AS April,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 5 AND year(duedate) = 2014 THEN debit – credit END) AS Mei,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 6 AND year(duedate) = 2014 THEN debit – credit END) AS Juni,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 7 AND year(duedate) = 2014 THEN debit – credit END) AS Juli,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 8 AND year(duedate) = 2014 THEN debit – credit END) AS Augustus,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 9 AND year(duedate) = 2014 THEN debit – credit END) AS September,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 10 AND year(duedate) = 2014 THEN debit – credit END) AS Oktober,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 11 AND year(duedate) = 2014 THEN debit – credit END) AS November,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 12 AND year(duedate) = 2014 THEN debit – credit END) AS December

      FROM dbo.JDT1 AS t1

      LEFT OUTER JOIN dbo.OCRD AS t2 ON t2.CardCode = t1.ShortName

      LEFT OUTER JOIN dbo.OCRD AS t3 ON t3.CardCode = t2.CardName

      WHERE     (t2.CardType = ‘c’)

      GROUP BY t1.ShortName,t2.CardName

      ORDER BY t1.ShortName

      I changed the year in 2014 and took the comma away after the word December.

      Next time open a new discussion. That suits better for questions!

      (0) 
      1. J. Dielemans

        Instead of 2014 as a fixed query you can better use this dynamic query for the same results for the past year:

        SELECT     TOP (100) PERCENT t1.ShortName, t2.CardName,

        SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 1 AND year(duedate) = (year(getdate())-1) THEN debit – credit END) AS Januari,

        SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 2 AND year(duedate) = (year(getdate())-1) THEN debit – credit END) AS Februari,

        SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 3 AND year(duedate) = (year(getdate())-1) THEN debit – credit END) AS Maart,

        SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 4 AND year(duedate) = (year(getdate())-1) THEN debit – credit END) AS April,

        SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 5 AND year(duedate) = (year(getdate())-1) THEN debit – credit END) AS Mei,

        SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 6 AND year(duedate) = (year(getdate())-1) THEN debit – credit END) AS Juni,

        SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 7 AND year(duedate) = (year(getdate())-1) THEN debit – credit END) AS Juli,

        SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 8 AND year(duedate) = (year(getdate())-1) THEN debit – credit END) AS Augustus,

        SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 9 AND year(duedate) = (year(getdate())-1) THEN debit – credit END) AS September,

        SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 10 AND year(duedate) = (year(getdate())-1) THEN debit – credit END) AS Oktober,

        SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 11 AND year(duedate) = (year(getdate())-1) THEN debit – credit END) AS November,

        SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 12 AND year(duedate) = (year(getdate())-1) THEN debit – credit END) AS December

        FROM dbo.JDT1 AS t1

        LEFT OUTER JOIN dbo.OCRD AS t2 ON t2.CardCode = t1.ShortName

        LEFT OUTER JOIN dbo.OCRD AS t3 ON t3.CardCode = t2.CardName

        WHERE     (t2.CardType = ‘c’)

        GROUP BY t1.ShortName,t2.CardName

        ORDER BY t1.ShortName

        Good Luck Jos Dielemans

        (0) 
  6. Charles Mcintyre

    Hi, Just found your 10 fav queries they are dated 2014 so not sure if you are still logging on. With regard to top 10 customers – which works perfectly – what I need is not a summed total but a list of the invoices that go to make up the total. Is this possible?

    thanks

    (0) 
  7. michael iannacone

    Hi, Can any one test my query to check if an invoice has been sent using both boyum and SAP ways of sending. We need to query to tell if an invoice has been emailed or printed.

    SELECT

    DISTINCT

    t0.DocEntry,

    t0.DocNum,

    t0.CardCode,

    t0.CardName,

    t0.DocDate,

    t0.DocDueDate as ‘Due Date’,

    T1.U_BOY_DATE,

    t0.DocCur,

    t0.DocTotal,

    T0.DocStatus,

    T0.CANCELED,

    T0.Printed,

    CASE

    WHEN T1.[U_BOY_Action] = 2 THEN ‘Printed’

    WHEN T1.[U_BOY_Action] = 4 THEN ‘Emailed’

    ELSE CONVERT(VARCHAR,T1.[U_BOY_Action]) End AS ‘Boyum_Action’,

    T3.WasSent AS ‘SAP_Sent’,

    T4.Confirmed2

    FROM OINV t0

    LEFT JOIN [dbo].[@BOY_85_DEL_LOG]  T1  ON T0.DocNum = T1.[U_BOY_id]

    LEFT JOIN dbo.OALR T2 ON CONVERT(VARCHAR,T0.DocNum) = LEFT(CONVERT(VARCHAR,T2.MsgData),9)

    LEFT JOIN OAOB T3 ON T2.Code = T3.AlertCode

    LEFT JOIN AOB1 T4 ON T3.AlertCode = T4.AlertCode 

    (0) 
  8. Danielle Ostach

    Just to share one that I created, this gives usage for items by year.

    It was restricted to usage defined aS Issues to production, shipped via delivery or shipped via direct invoice.

    It would not be hard though to modify this to fact in returns, credits or inventory adjustments.  (just change the sum to outqty-inqty instead of outqty and add the extra doctypes to the ‘where’ clause’)

    It runs fast for company that gets about 15-100 orders a day.

    select oinm.itemcode, oitb.itmsgrpnam,docyear = YEAR(docdate),

        JanQty = sum(CASE WHEN Month(docdate) = 1 THEN outqty else 0 END),

        febQty = sum(CASE WHEN Month(docdate) = 2 THEN outqty else 0 END),

        marQty = sum(CASE WHEN Month(docdate) = 3 THEN outqty else 0 END),

        AprQty = sum(CASE WHEN Month(docdate) = 4 THEN outqty else 0 END),

        MayQty = sum(CASE WHEN Month(docdate) = 5 THEN outqty else 0 END),

        JunQty = sum(CASE WHEN Month(docdate) = 6 THEN outqty else 0 END),

        JulQty = sum(CASE WHEN Month(docdate) = 7 THEN outqty else 0 END),

        AugQty = sum(CASE WHEN Month(docdate) = 8 THEN outqty else 0 END),

        SepQty = sum(CASE WHEN Month(docdate) = 9 THEN outqty else 0 END),

        OctQty = sum(CASE WHEN Month(docdate) = 10 THEN outqty else 0 END),

        NovQty = sum(CASE WHEN Month(docdate) = 11 THEN outqty else 0 END),

        DecQty = sum(CASE WHEN Month(docdate) = 12 THEN outqty else 0 END)

    FROM OINM

    inner join oitm on oinm.itemcode = oitm.itemcode

    inner join oitb on oitb.itmsgrpcod = oitm.itmsgrpcod

    WHERE (transtype =59 or transtype = 13 OR  transtype = 15)

    group by oinm.itemcode, oitb.itmsgrpnam, YEAR(docdate)

    Order by oinm.itemcode, year(docdate)

    (0) 

Leave a Reply