Skip to Content
avatar image
Former Member

Sales By State Query

Hello All -

We would like to develop a query that shows us monthly sales by each US state and OUS locations. For example, on the left most column, we would have each location. Then subsequent columns to the right of that would have sales for each location in Jan, Feb, March, etc.

Can anyone help with this?

Thanks,

Mike

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Jun 23, 2010 at 07:57 PM

    That is because you have one additional comma before FROM.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Gordon,

      When i use this query NO amt came in my result ?

      query reteriving all the state but no amount there....

      JRAJPUT

  • avatar image
    Former Member
    Jun 22, 2010 at 08:04 PM

    Hi Mike,

    Try this one:

    SELECT T0.State1 AS 'Bill-to State', (SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK) INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode

    WHERE MONTH(T1.DOCDATE) = 1 AND T2.State1 = T0.State1

    AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JAN Amt'

    FROM dbo.OCRD T0 LEFT JOIN dbo.OINV T1 ON T1.CardCode = T0.CardCode

    GROUP BY T0.State1

    ORDER BY T0.State1

    Again, it is only take invoice amount without credit memo. Copy the 1st month carefully to get all other 11 months.

    Thanks,

    Gordon

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      I tried first three months below:

      SELECT T0.State1 AS 'Bill-to State', (SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK) INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode

      WHERE MONTH(T1.DOCDATE) = 1 AND T2.State1 = T0.State1

      AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JAN Amt', (SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK) INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode

      WHERE MONTH(T1.DOCDATE) = 2 AND T2.State1 = T0.State1

      AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'FEB Amt', (SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK) INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode

      WHERE MONTH(T1.DOCDATE) = 3 AND T2.State1 = T0.State1

      AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'MAR Amt',

      FROM dbo.OCRD T0 LEFT JOIN dbo.OINV T1 ON T1.CardCode = T0.CardCode

      GROUP BY T0.State1

      ORDER BY T0.State1

      But did not get any data -- not sure what I am doing wrong?!

      Mike