SQL view for SOP sales by customer by month


Someone asked me for code to get monthly SOP sales and I could have sworn I already had that. But I searched and didn’t come up with anything, so here is a new view to show SOP sales (the total of SOP invoices less returns) by month. As usual, I am making a few assumptions (listed in the view comments), and am hard coding one year at a time (2013 in this example). You can easily change the year as needed on line 59.

create view view_Sales_by_Customer_by_Month
as

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- view_Sales_by_Customer_by_Month
-- Created June 7, 2013
--    by Victoria Yudin - Flexible Solutions, Inc.
-- For updates see http://victoriayudin.com/gp-reports/
-- Returns total sales (invoices - returns) for each customer
--    by month (for the specified year)
-- Amount used is the invoice total (including freight,
--    taxes, discounts, etc.)
-- Only posted invoices and returns are included
-- Shows functional currency
-- Voided transactions are excluded
-- Document Date is used (not GL Posting Date)
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

select
d.CUSTNMBR Customer_ID,
d.CUSTNAME Customer_Name,
sum(case when month(d.DOCDATE) = 1
  then d.SALES else 0 end) Jan_Sales,
sum(case when month(d.DOCDATE) = 2
  then d.SALES else 0 end) Feb_Sales,
sum(case when month(d.DOCDATE) = 3
  then d.SALES else 0 end) Mar_Sales,
sum(case when month(d.DOCDATE) = 4
  then d.SALES else 0 end) Apr_Sales,
sum(case when month(d.DOCDATE) = 5
  then d.SALES else 0 end) May_Sales,
sum(case when month(d.DOCDATE) = 6
  then d.SALES else 0 end) Jun_Sales,
sum(case when month(d.DOCDATE) = 7
  then d.SALES else 0 end) Jul_Sales,
sum(case when month(d.DOCDATE) = 8
  then d.SALES else 0 end) Aug_Sales,
sum(case when month(d.DOCDATE) = 9
  then d.SALES else 0 end) Sep_Sales,
sum(case when month(d.DOCDATE) = 10
  then d.SALES else 0 end) Oct_Sales,
sum(case when month(d.DOCDATE) = 11
  then d.SALES else 0 end) Nov_Sales,
sum(case when month(d.DOCDATE) = 12
  then d.SALES else 0 end) Dec_Sales,
sum(d.SALES) Yearly_Total

from
(select s.DOCDATE, s.CUSTNMBR, c.CUSTNAME,
 case s.SOPTYPE
   when 3 then s.DOCAMNT
   when 4 then s.DOCAMNT*-1
   end SALES
 from SOP30200 s
 left outer join RM00101 c
   on s.CUSTNMBR = c.CUSTNMBR
 where s.VOIDSTTS = 0
   and s.SOPTYPE in (3,4)
   and year(s.DOCDATE) = 2013 --change year as needed
 ) d

group by d.CUSTNMBR, d.CUSTNAME

-- add permissions for DYNGRP
go
grant select on view_Sales_by_Customer_by_Month to DYNGRP

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.

27 Responses to “SQL view for SOP sales by customer by month”

  1. Victoria,
    I am trying to get the Customer Sales by GL account designation, we have an Account 4000 for Commercial/Residential Sales, and 4800 for Rentals, …. I used your view_GL_Trx and then Updated the missing Originating Master IDs with Update Joins to JC30001, RM00101 and SV00300. I have some remaining non-allocated transactions in the COG area with IVADJ as the source document. I am not sure what to join these records to.

    Do you have a query that gets Revenue by Customer tied to GL Account Number?

    Like

    • Hi Warren,

      Transactions with IVADJ as the source are inventory transactions, which are not tied to a customer in GP unless you’re putting in a manual note somewhere that can be linked to.

      Unfortunately, there is no one universal query that will give you ‘revenue by customer tied to GL account number’ because how this is accomplished will greatly differ on a number of variables. There are many different ways to use GP and enter transactions into it and often what will work for one company, will not work for others. There are also many other nuances, like what makes up “revenue”, how transactions are posted, etc.

      -Victoria

      Like

  2. Victoria, I run this report for 2013. And when I sum up the totals for all sales for all customers it is quite a bit different then my sales total when I run the Sales Total from an Income Statement report within GP. Do you know what would be some possible causes of that discrepancy? I appreciate your blog so much it is a data miner’s lifeline sometimes. Keep up the great work.

    Like

    • Hi Zach,

      This view only takes onto consideration transactions posted in the SOP module. Typically, your Income Statement report would look at the General Ledger. It’s entirely possible that you have entries from sources other than SOP that go into your GL sales accounts. You can take a look at your sales accounts in the Account Transactions SmartList to get a better idea of where the transactions are coming from. Typically, if you’re looking to ‘reconcile’ the sales GL accounts, you would start from the GL and work backwards to get the details of what makes those up.

      Hope that helps,
      -Victoria

      Like

  3. Hi Victoria,
    I was thinking of trying to adapt this view to show by sales by customer by year but also showing the average time between orders across the life of the account. Do you have any pointers on how to calculate this average?

    Thank you,
    Kirk Livermont

    Like

  4. Hi Victoria, can we do similar sum type calculations in SmartList Viewer without creating a SQL view? I.e. can we create calculated fields for each month and subtotal them all up as a summary SmartList? I tried the WHEN statement but I couldn’t get it to work, I kept getting a syntax error. Can I use this same formula in SmartList viewer?

    Like

  5. Hi Victoria, I am using the above customer sales by month, however I am still unable to join a GL table that has ACTNUMST to this query. I have tried several options, however I can’t find a way to join ACTNUMST I’m looking at all GL tables that have this but haven’t found a common denometer to add to this query. Can you help?
    Thanks
    Diane

    Like

    • Diane,

      This code shows summaries of sales by customer by month fro the SOP module. Within each transaction there are at least 2, but often many more General Ledger accounts used. For each customer, there are about a dozen GL accounts that can be set up. What GL account are you trying to link to?

      -Victoria

      Like

      • Victoria,
        I am trying to link to the customer GL numbers, however I understand what you are saying, there are a few to each client in our situation . Is there any way to write something that will sum based on customer GL account and the invoices that are associated. Like the current code however sum on certain account numbers?
        I look into smart lists such as Financial/Account Summary/Customer Revenue balance, and i am able to see by period and account number the sales for each client, however those “sales” also will include accruals, which I do not want.
        Thanks
        Diane

        Like

        • Hi Diane,

          It sounds like you want a custom report that is very specific to your situation. I think it would be impossible for me to help with something like this in blog comments. I can help with this as a consulting project (for a fee). If you are interested, let me know.

          -Victoria

          Like

  6. Hi Victoria,
    I was wondering if I could join a GL table to this code, to add in the customers GL account numbers? Not sure if these tables are able to join.
    Thanks
    Diane

    Like

    • Hi Diane,

      Can you explain a little more about the logic you’re looking for? Are you looking for an account on the Customer Account Maintenance window? Or on the transactions themselves? If so, which account(s)?

      -Victoria

      Like

      • Hi Victoria
        I am looking for the cutomer GL account on the transactions themself. Then have it sum on the customer GL account and not just the customer name.
        Is that possible?
        Thanks
        Diane

        Like

        • Diane,

          I am not sure what you mean by the ‘customer GL account’?

          -Victoria

          Like

          • Hi Victoria,
            I think its Segement 1 in the tables. Does that help?
            Diane

            Like

            • Hi Diane,

              Sorry, not really. :-( In any case, I think what you’re asking for is most likely doable, but would require a more precise specification. It would also not likely to be something I would publish on my blog, as it would be specific to your company. If you’re interested in having this created for you (or getting help with creating it yourself), we can do this as a consulting project. Please let me know if that’s something you would like to discuss further.

              -Victoria

              Like

              • Hi Victoria,
                I am writing this query below where I am joining SOP table, RM table and GL table. Its erroring out on GL table join. Are these tables all able to join each other?
                select

                d.CUSTNMBR as Customer_ID,d.CUSTNAME Customer_Name, coalesce(G.ACTNUMST,”) Account_Number,

                sum(case when month(d.DOCDATE) = 1 then d.SALES else 0 end) Jan_Sales,
                sum(case when month(d.DOCDATE) = 2 then d.SALES else 0 end) Feb_Sales,
                sum(case when month(d.DOCDATE) = 3 then d.SALES else 0 end) Mar_Sales,
                sum(case when month(d.DOCDATE) = 4 then d.SALES else 0 end) Apr_Sales,
                sum(case when month(d.DOCDATE) = 5 then d.SALES else 0 end) May_Sales,
                sum(case when month(d.DOCDATE) = 6 then d.SALES else 0 end) Jun_Sales,
                sum(case when month(d.DOCDATE) = 7 then d.SALES else 0 end) Jul_Sales,
                sum(case when month(d.DOCDATE) = 8 then d.SALES else 0 end) Aug_Sales,
                sum(case when month(d.DOCDATE) = 9 then d.SALES else 0 end) Sep_Sales,
                sum(case when month(d.DOCDATE) = 10 then d.SALES else 0 end) Oct_Sales,
                sum(case when month(d.DOCDATE) = 11 then d.SALES else 0 end) Nov_Sales,
                sum(case when month(d.DOCDATE) = 12 then d.SALES else 0 end) Dec_Sales,
                sum(d.SALES) Yearly_Total
                from(select s.DOCDATE, s.CUSTNMBR, c.CUSTNAME, s.DOCAMNT as SALES, coalesce(G.ACTNUMST,”) Account_Number
                from SOP30200 s left outer join RM00101 c
                on s.CUSTNMBR = c.CUSTNMBR
                LEFT OUTER JOIN
                RM30301 DH
                on dh.CUSTNMBR = c.CUSTNMBR
                and DH.DEX_ROW_ID = c.DEX_ROW_ID
                LEFT OUTER JOIN
                GL00105 G
                ON DH.DSTINDX = G.ACTINDX
                and DH.DEX_ROW_ID = G.DEX_ROW_ID
                where s.VOIDSTTS = 0 and s.SOPTYPE = 3
                and year(s.DOCDATE) = 2013 –change year as needed
                and (s.SOPNUMBE not like ‘0i%’
                OR s.SOPNUMBE not like ‘%a’)

                ) d
                group by d.CUSTNMBR, d.CUSTNAME, coalesce(G.ACTNUMST,”)

                Like

                • Hi Diane,

                  I am little confused by what you’re doing here. Why are you linking on DEX_ROW_ID? That field should never be used for linking.

                  And why are you linking to the Receivables distribution history table (RM30301) from SOP and why on just the Customer Number? Why not use the SOP distributions table (SOP10102) instead and link to it by transaction number and type?

                  -Victoria

                  Like

Trackbacks/Pingbacks

  1. SQL view for SOP sales by customer by month | Victoria Yudin | DynamicAccounting.net - June 10, 2013

    […] Victoria provides a SQL view for SOP sales by customer by month. […]

    Like

  2. SQL view for SOP sales by customer by month – 6/7, Victoria Yudin | Partner Compete - June 7, 2013

    […] Continue reading on Source Blog […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,474 other followers

%d bloggers like this: