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.

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

  1. 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 this

  2. 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 this

  3. 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 this

    • 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 this

      • 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 this

        • 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 this

  4. 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 this

    • 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 this

      • 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 this

        • Diane,

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

          -Victoria

          Like this

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

            Like this

            • 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 this

              • 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 this

                • 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 this

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 this

  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 this

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,361 other followers

%d bloggers like this: