YTD Year to Date Query SQL

RADeveloper · #1 (**permalink**) 12-19-11, 18:01

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?

healdem · #2 (**permalink**) 12-19-11, 18:23

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

Sinndho · #3 (**permalink**) 12-20-11, 01:54

Quote:

Originally Posted by RADeveloper

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.

RADeveloper · #4 (**permalink**) 12-20-11, 09:29

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.

Sinndho · #5 (**permalink**) 12-20-11, 09:39

You're welcome!

RADeveloper · #6 (**permalink**) 12-20-11, 11:01

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?

Sinndho · #7 (**permalink**) 12-20-11, 11:30

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

RADeveloper · #8 (**permalink**) 12-20-11, 11:46

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?

RADeveloper · #9 (**permalink**) 12-20-11, 12:21

What is the underlying logic behind:

Code:

ON (b.Year & b.Month <= a.Year & a.Month)

?

What exactly does the "&" operator do?

Sinndho · #10 (**permalink**) 12-20-11, 12:37

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.

RADeveloper · #11 (**permalink**) 12-20-11, 14:19

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!

Sinndho · #12 (**permalink**) 12-20-11, 14:22

You're welcome!

RADeveloper · #13 (**permalink**) 12-20-11, 14:33

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"?

RADeveloper · #14 (**permalink**) 12-20-11, 14:39

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?

healdem · #15 (**permalink**) 12-20-11, 14:49

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

Internet Services

Web Development

Digital Marketing

Consumer Tech