Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2008
    Posts
    5

    Unanswered: Is it possible?????

    Sorry, but i am a bit of a newbie to SQL, i am wanting to know whether the following is possible.

    I have tables in a database Date , Account, And Value. I am wanting to pull this information out of the Database like the following

    Column 1 Column 2 Column3 etc...
    Account | Value(if prior Feb)| Value(if prior to march) | etc...

    Can you actually do this using sql?????

    Thanks for all your help in advance

  2. #2
    Join Date
    Aug 2008
    Posts
    5
    Sorry, but i am a bit of a newbie to SQL, i am wanting to know whether the following is possible.

    I have tables in a database with the following transaction information

    Date , Account posted to, And Value



    I am wanting to pull this information out of the Database so it will look like the following:

    Column 1 :
    Account

    Column 2 :
    sum(Value(if transaction date is prior to Feb))

    Column3:
    sum(Value(if transaction date is prior to march))

    Column4:
    sum(Value(if transaction date is prior to April))
    etc.

    I know i can pull 12 different queries to get each month, but i was hoping to be able to pull it all at once.


    Can you actually do this using sql?????

    Thanks for all your help in advance

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT Account 
         , SUM(CASE WHEN "Date" < '2008-02-01'
                    THEN Value END) AS prior_to_feb
         , SUM(CASE WHEN "Date" < '2008-03-01'
                    THEN Value END) AS prior_to_mar
         , SUM(CASE WHEN "Date" < '2008-04-01'
                    THEN Value END) AS prior_to_apr
         , ...
      FROM daTale
    GROUP 
        BY Account
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Aug 2008
    Posts
    5

    Is anything wrong with this statement then?

    Hi and thankyou for your response, taking the information and applying to the SAP database, i get an error stating incorrect column expression Sum(Case When OJDT.RefDate <= '2008-01-31' Then (AJD1.Debit-AJD1.Credit) End.
    __________________________________________________ _______________

    SELECT AACT.FormatCode, AACT.AccountName,
    Sum(Case When OJDT.RefDate <= '2008-01-31' Then (AJD1.Debit-AJD1.Credit) End) As Month1,
    Sum(Case When OJDT.RefDate <= '2008-02-29' Then (AJD1.Debit-AJD1.Credit) End) As Month2,
    Sum(Case When OJDT.RefDate <= '2008-03-31' Then (AJD1.Debit-AJD1.Credit) End) As Month3,
    Sum(Case When OJDT.RefDate <= '2008-04-30' Then (AJD1.Debit-AJD1.Credit) End) As Month4,
    Sum(Case When OJDT.RefDate <= '2008-05-31' Then (AJD1.Debit-AJD1.Credit) End) As Month5,
    Sum(Case When OJDT.RefDate <= '2008-06-30' Then (AJD1.Debit-AJD1.Credit) End) As Month6,
    Sum(Case When OJDT.RefDate <= '2008-07-31' Then (AJD1.Debit-AJD1.Credit) End) As Month7,
    Sum(Case When OJDT.RefDate <= '2008-08-31' Then (AJD1.Debit-AJD1.Credit) End) As Month8,
    Sum(Case When OJDT.RefDate <= '2008-09-30' Then (AJD1.Debit-AJD1.Credit) End) As Month9,
    Sum(Case When OJDT.RefDate <= '2008-10-31' Then (AJD1.Debit-AJD1.Credit) End) As Month10,
    Sum(Case When OJDT.RefDate <= '2008-01-30' Then (AJD1.Debit-AJD1.Credit) End) As Month11,
    Sum(Case When OJDT.RefDate <= '2008-01-31' Then (AJD1.Debit-AJD1.Credit) End) As Month12,

    FROM MSPLC.dbo.AACT AACT, MSPLC.dbo.AJD1 AJD1, MSPLC.dbo.OJDT OJDT

    WHERE OJDT.TransId = AJD1.TransId AND AACT.AcctCode = AJD1.Account

    __________________________________________________ _______________
    Thanks
    Last edited by Elaussie09; 08-08-08 at 05:36.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you forgot your GROUP BY clause

    also, you have a dangling comma in front of the FROM keyword

    and your dates are wrong for november and december
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Aug 2008
    Posts
    5

    Corrected statement still errors

    Hi Sorry about that, but even when i revise the statement, i am getting the same error.

    SELECT AACT.FormatCode, AACT.AccountName,
    Sum(Case When OJDT.RefDate <= '2008-01-31' Then (AJD1.Debit-AJD1.Credit) End) As Month1,
    Sum(Case When OJDT.RefDate <= '2008-02-29' Then (AJD1.Debit-AJD1.Credit) End) As Month2,
    Sum(Case When OJDT.RefDate <= '2008-03-31' Then (AJD1.Debit-AJD1.Credit) End) As Month3,
    Sum(Case When OJDT.RefDate <= '2008-04-30' Then (AJD1.Debit-AJD1.Credit) End) As Month4,
    Sum(Case When OJDT.RefDate <= '2008-05-31' Then (AJD1.Debit-AJD1.Credit) End) As Month5,
    Sum(Case When OJDT.RefDate <= '2008-06-30' Then (AJD1.Debit-AJD1.Credit) End) As Month6,
    Sum(Case When OJDT.RefDate <= '2008-07-31' Then (AJD1.Debit-AJD1.Credit) End) As Month7,
    Sum(Case When OJDT.RefDate <= '2008-08-31' Then (AJD1.Debit-AJD1.Credit) End) As Month8,
    Sum(Case When OJDT.RefDate <= '2008-09-30' Then (AJD1.Debit-AJD1.Credit) End) As Month9,
    Sum(Case When OJDT.RefDate <= '2008-10-31' Then (AJD1.Debit-AJD1.Credit) End) As Month10,
    Sum(Case When OJDT.RefDate <= '2008-11-30' Then (AJD1.Debit-AJD1.Credit) End) As Month11,
    Sum(Case When OJDT.RefDate <= '2008-12-31' Then (AJD1.Debit-AJD1.Credit) End) As Month12

    FROM MSPLC.dbo.AACT AACT, MSPLC.dbo.AJD1 AJD1, MSPLC.dbo.OJDT OJDT

    WHERE OJDT.TransId = AJD1.TransId AND AACT.AcctCode = AJD1.Account

    Group By AACT.FormatCode, AACT.AccountName

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    maybe the SAP database does not support standard SQL

    consult your SAP manual
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Aug 2008
    Posts
    5

    Different Case syntax

    Thanks for the response, i have just realized that MS Query has a different Case syntax than SQL

    i have to try and work it out , unless you guys already know?

    thanks for all your help

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    possibly IF will work instead of CASE

    consult your MS Query documentation
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •