Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Dec 2011
    Posts
    9

    Smile Unanswered: YTD Year to Date Query SQL

    Hello,

    I'm trying to write a query to sum up widgets year to date, but my problem is that the fiscal year months start from October. So, I want to sum up widgets YTD starting October of one year till September of the next year.

    For example,

    I have table1 with Month, Year and Widgets as columns, and I have to write a SQL to YTD.
    I have the following up till now:

    Code:
    SELECT Table1.Month, Table1.Year, Table1.Widgets
    
     (SELECT Sum(a.Widgets) 
                               FROM Table1 AS a 
                               WHERE  (a.Month = 10 Or a.Month = 11 Or a.Month = 12)  AND a.Year = a.Month) 
                               AND (IIf(a.Month = 10 Or a.Month = 11 Or a.Month = 12, a.Year = Table1.Year, a.Year <= Table1.Year ))) AS YTD
    FROM Table1;
    It doesn't work properly, there is some flaw.

    I have tried so hard, but can't get it to work. Any ideas?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Im suspicious of
    Code:
    SELECT Table1.Month, Table1.Year, Table1.Widgets
    
     (SELECT Sum(a.Widgets) 
                               FROM Table1 AS a 
                               WHERE  (a.Month = 10 Or a.Month = 11 Or a.Month = 12)  AND a.Year = a.Month) 
                               AND (IIf(a.Month = 10 Or a.Month = 11 Or a.Month = 12, a.Year = Table1.Year, a.Year <= Table1.Year ))) AS YTD
    FROM Table1;

    I suspect you may be better off writing a function which returns the relevant start date

    eg
    public function GetFYStart(MyDate as date) as date
    'supply a date that you want to find the start of that financial year
    'financial year starts 01 October in each and every year
    'if the supplied date is on or after the 1st of October then return October the fiurst for the same year as the supplied date
    'otherwise return the 1st october for the provious date
    if month(MyDate)>=10 then
    GetFYStart = cdate("10/01/" & year(MyDate))
    else
    GetFYStart = cdate("10/01/" & year(MyDate)-1)
    end function

    place the function in a common code module
    then call it in the SQL
    .....where adatecolumn between getfystart(adate) and adate
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by RADeveloper View Post
    I have table1 with Month, Year and Widgets as columns, and I have to write a SQL to YTD.
    I have the following up till now:

    Code:
    SELECT Table1.Month, Table1.Year, Table1.Widgets
    
     (SELECT Sum(a.Widgets) 
                               FROM Table1 AS a 
                               WHERE  (a.Month = 10 Or a.Month = 11 Or a.Month = 12)  AND a.Year = a.Month) 
                               AND (IIf(a.Month = 10 Or a.Month = 11 Or a.Month = 12, a.Year = Table1.Year, a.Year <= Table1.Year ))) AS YTD
    FROM Table1;
    It doesn't work properly, there is some flaw.

    I have tried so hard, but can't get it to work. Any ideas?
    I'm not sure to understand the logic at work here but, as far as your query is concerned I cas see several issues.

    a) You can simplify the expression:
    Code:
    a.Month = 10 Or a.Month = 11 Or a.Month = 12
    can become:
    Code:
    a.Month > 9
    or (just in case the value in 'a.Month' could be greater than 12):
    Code:
    a.Month IN ( 10, 11, 12 )
    b) This is very suspicious and I can't see any logic behind it:
    Code:
    a.Year = a.Month
    A year (e.g. 2011) equal to a month (e.g. 11)?

    c) The meaning of this:
    Code:
    IIf(a.Month = 10 Or a.Month = 11 Or a.Month = 12, a.Year = Table1.Year, a.Year <= Table1.Year )
    would be:
    Code:
    If a.Month = 10 Or a.Month = 11 Or a.Month = 12 Then
        If a.Year = Table1.Year Then 
            <Expression> = True
        Else
            <Expression> = False
        End If
    Else
        If a.Year <= Table1.Year Then
            <Expression> = True
        Else
            <Expression> = False
        End If
    End If
    which makes little sense.

    d) There is no link (operator or comma) between:
    Code:
    SELECT Table1.Month, Table1.Year, Table1.Widgets
    and:
    Code:
    (SELECT Sum(a.Widgets)
    e) You're mixing several levels of subqueries (parentheses!), but due to (d) hereabove, it's hard to be specific. If a comma is missing and the beginning of the query was intended to be:
    Code:
    SELECT Table1.Month, Table1.Year, Table1.Widgets, (SELECT Sum(a.Widgets)...
    then you cannot use the alias 'a.Widgets' in a context where it's not defined. Moreover, a GROUP BY clause would be missing in this case.


    1. Try to explain clearly what you want to achieve (e.g. how do you define 'widgets year to date').

    2. "It doesn't work properly", "there is some flaw", "can't get it to work" are meaningless and do not help to understand the problem:

    a) If you receive one or several error messages, please post them and explain where and in which circumstances they occur.

    b) If a function or a query does not yield what you were expected, please supply some sample data and explain what you're expecting as a result.
    Have a nice day!

  4. #4
    Join Date
    Dec 2011
    Posts
    9

    Smile I will digest this and post back soon

    Thanks for the reply. I understand the syntax errors, and thanks for explaining the IIf statement. I'll digest everything and post back.
    You see, I'm fairly new to SQL, and your guidance helped a lot. I'll rewrite my code, redefine the logic, and reply soon with specifics.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  6. #6
    Join Date
    Dec 2011
    Posts
    9

    Smile

    Okay, so here we go.

    Suppose I have a table as follows:

    Month Year Widgets
    10 2010 3
    11 2010 3
    12 2010 3
    01 2011 3
    02 2011 3
    03 2011 3

    Now, I'm trying to code a YTD query that would give me the following result:

    Month Year Widgets YTD
    10 2010 3 3
    11 2010 3 6
    12 2010 3 9
    01 2011 3 12
    02 2011 3 15
    03 2011 3 18


    I hope this explains the logic.

    I'm not used to writing many functions, so I looked up several tutorials.

    I then rewrote the previous function as follows:

    Code:
    SELECT Table1.Month, Table1.Year, Table1.Widgets
    
     (SELECT Sum(a.Widgets) 
                               FROM Table1 AS a 
                               WHERE (Table1.Month>9 AND (a.Month BETWEEN 9 AND Table1.Month))
                               OR (A.Month<10 AND a.Year=Table1.Year)
                               OR (a.Year>Table1.Year)
    )AS YTD
    FROM Table1;
    But it's not giving me the right result.

    I'm wondering: Do a.Widgets and Table1.Widgets refer to the same thing?

    How should I write a function that loops through the table to give me the YTD values?

    Can you please give me an example of how the function should look like?

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Like this ?
    Code:
    SELECT a.Month,
           a.year,
           a.Widgets,
           SUM(b.Widgets) AS YTD
    FROM Table1 a
    INNER JOIN Table1 b
    ON (b.Year & b.Month <= a.Year & a.Month)
    GROUP BY a.Month,a.Widgets, a.year
    ORDER BY a.year,a.Month
    Have a nice day!

  8. #8
    Join Date
    Dec 2011
    Posts
    9

    Smile Works with a tiny error

    Wow! This looks beautiful! But it has one tiny error. The result is the following:

    Month Year Widgets YTD
    10 2010 3 3
    11 2010 3 6
    12 2010 3 9
    01 2011 3 12
    02 2011 3 24
    03 2011 3 27
    04 2011 3 30

    The error is the one in Bold. It somehow doubles the previous value. Is there a fix?

    I'll search online for the definitions of all of the commands you used, and try to understand what's going on.

    Also, you are amazing with SQL! Thank you so much for your help. I want to learn advanced SQL, is there any resource that you would recommend?

  9. #9
    Join Date
    Dec 2011
    Posts
    9

    Smile Logic

    What is the underlying logic behind:
    Code:
    ON (b.Year & b.Month <= a.Year & a.Month)
    ?

    What exactly does the "&" operator do?

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This is strange, I get the correct results (see attachment).

    The technique is called a running sum query. It performs a self-join (an inner join of a table on itself) but with a condition that in one table eliminates the rows that were already processed in the other (same) table. If you prefer, you join to the next sequential row that you need to determine in the ON relation of the join operation.

    In this query, you use Table1 twice: as 'a' and as 'b' :
    Code:
    FROM Table1 AS a INNER JOIN Table1 AS b
    The trick here is that to set a sequencial chronological order, you need to concatenate the column 'Year' with the column 'Month' in that order ('Year & Month'), for both table aliases ('a' and 'b'):
    Code:
    ON b.Year & b.Month<= a.Year & a.Month
    As we have an AGGREGATE function (SUM), we must use a GROUP BY clause, event if it does nothing in this case: not two rows in the table 'Table1' have identical values for the columns 'Month', 'Widgets' and 'Year'.

    We then re-order the result set in the same sort order we used in inner join relation ('ON b.Year & b.Month<=a.Year & a.Month'):
    Code:
    ORDER BY a.year, a.Month
    There are other more efficient techniques for that, unfortunately they are not usable in Access.
    Attached Thumbnails Attached Thumbnails qry_YTD.jpg  
    Have a nice day!

  11. #11
    Join Date
    Dec 2011
    Posts
    9

    Smile It works

    I want to hug you right now! It works! I had duplicate (month) entries for the months 10,11,12 in my table, so it didn't work before. It fixed it, so now it's working!

    Thanks for the explanation, I really really appreciate it!

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  13. #13
    Join Date
    Dec 2011
    Posts
    9

    Smile Quick follow up

    Can we do something similar to more than one column. For example, YTD for widgets1, YTD for widgets2, etc. or "YTD widgets1 divided by YTD widgets2"?

  14. #14
    Join Date
    Dec 2011
    Posts
    9

    Quick follow up

    I got the first case. This is the code I used:
    Code:
    SELECT a.Month,
           a.year,
           a.Widgets,
           SUM(b.Widgets1),
           SUM(b.Widgets2)
     AS YTD
    FROM Table1 a
    INNER JOIN Table1 b
    ON (b.Year & b.Month <= a.Year & a.Month)
    GROUP BY a.Month,a.Widgets, a.year
    ORDER BY a.year,a.Month
    Is there a way to get the Division of the two? Like YTDWidgets1/YTDWidgets2?

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    bit rusty on my SQL these days, but this may be worth a try
    Code:
    SELECT a.Month,
           a.year,
           a.Widgets,
           SUM(b.Widgets1),
           SUM(b.Widgets2),
           SUM(b.Widgets1) / SUM(b.Widgets2) as ProportionWidgets1,
     AS YTD
    FROM Table1 a
    INNER JOIN Table1 b
    ON (b.Year & b.Month <= a.Year & a.Month)
    GROUP BY a.Month,a.Widgets, a.year
    ORDER BY a.year,a.Month
    like most computer languages if you can clearly think it in your mind its usually a simple task to then translate that idea into reality. SQL is a very powerfull language in its own right and can take a long long time to truly master

    however I think you would be better off doing the division in the user interface, the form or report where you are suing this information
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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