Skip to Content
author's profile photo Former Member
Former Member

Addition to Query

Hi Guys,

Gordon very kindly helped another user with the query below last year, I would like to be able to add CRD1, Country to the query but am unsure of the join required in this structure, if possible I would also like to be able to make the year as a select-able parameter, does anyone have any clues or pointers to put me in the right direction.

Help as always gratefully appreciated, have had a bash myself but epically failed !

SELECT P.[CardCode],P.[CardName],

(SELECT SUM(Debit) - sum(Credit) FROM JDT1 WHERE ShortName= P.CardCode AND Year(DueDate) = 2011 AND TransType IN ('13','14')) '2011 Sales',

[1] as [Jan],

[2] as [Feb],

[3] as [Mar],

[4] as [Apr],

[5] as [May],

[6] as [Jun],

[7] as [Jul],

[8] as [Aug],

[9] as [Sep],

[10] as [Oct],

[11] as [Nov],

[12] as [Dec]

FROM (SELECT T0.CARDCODE, T0.CARDNAME, (T1.Debit - T1.Credit) AS [BAL],

MONTH(T1.Duedate) as [month]

FROM dbo.OCRD T0

LEFT JOIN dbo.JDT1 T1 ON T1.ShortName = T0.CardCode AND Year(T1.Duedate) = 2011 AND T1.TransType in ('13','14')) S

PIVOT (SUM(S.[BAL]) FOR [month] IN

([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

Kind regards

Sean Martin

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on Aug 22, 2011 at 03:06 PM

    Hi Sean,

    CRD1 will have multiple lines for one BP. Are you sure you want to link this table? Can you restrict to one line only for one BP?

    Thanks,

    Gordon

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi All,

      Further to my question above, I have thought a little more about it, is there any way of getting a total sales per country per month, for example.,

      GB would have a total for Jan, Feb, Mar, April etc

      IE would have a total for Jan, Feb, Mar, April, etc

      Etc etc

      SELECT DISTINCT P.[Country],

      (SELECT SUM(Debit) - sum(Credit) FROM JDT1 WHERE ShortName= P.CardCode AND Year(DueDate) = 2011 AND TransType IN ('13','14')) '2011 Sales',

      [1] as [Jan],

      [2] as [Feb],

      [3] as [Mar],

      [4] as [Apr],

      [5] as [May],

      [6] as [Jun],

      [7] as [Jul],

      [8] as [Aug],

      [9] as [Sep],

      [10] as [Oct],

      [11] as [Nov],

      [12] as [Dec]

      FROM (SELECT T0.CARDCODE, T0.CARDNAME, T0.Country, (T1.Debit - T1.Credit) AS [BAL],

      MONTH(T1.RefDate) as [month]

      FROM dbo.OCRD T0

      LEFT JOIN dbo.JDT1 T1 ON T1.ShortName = T0.CardCode AND Year(T1.RefDate) = 2011 AND T1.TransType in ('13','14')) S

      PIVOT (SUM(S.[BAL]) FOR [month] IN

      ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

      This would be a huge help and cut down my time, I have tried this morning to get this to work but failed. Any help appreciated. Or if someone has a different sql that does the same.

      Kind regards

      Sean

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.