0

I am not fluent in SQL, but I am using Microsoft Query to create a new table from an old table and add a new column (a calculated column).

My calculation queries from the same table. I essentially want to retrieve a dollar figure from the same date one year ago and provide a calculated field that gives year-over-year growth by account. Each account will have several columns of unique data except for the date.

I am thinking that some sort of query stating that columns x, y and z match, but also including the date column -364.

If you can help, I'd be more than greatful.

Thanks, Mike

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
MikeM
  • 11
  • 2
  • 3
  • 1
    It isn't the best to store a calculated column in a database...you are far better off to run the calculation at the time you want to calculate it and not store the result of the calculation (unless you are losing data and want to store time sensitive aggregates). A view might be appropriate here for you as well. – Twelfth Sep 23 '13 at 16:32
  • Are you using `SQL Server`? – PM 77-1 Sep 23 '13 at 16:37
  • @Twelfth - I'm sure storing it may be best, but this is a small database table that may get larger. I'm just trying to serve up some requirements to a vendor and the best way to give them what I want is to calculate that column. – MikeM Sep 23 '13 at 16:45
  • @PM77-1 - I'm using Microsoft Query (querying a table in an excel file). – MikeM Sep 23 '13 at 16:46
  • Ah k, got where you are coming from...this is for requirements. If you ask a 3rd party vendor to make this for you, odds are your vendor won't store this calculation but just use a view (or something to that extent) to give you the feel that it is physically there, but not actually be there (I'd actually recommend finding a different vendor if they try to store these calc'd fields). If your database table has 1 line per date and all you are doing is comparing last years row beside this years number, then the answer by user2065377 is likely close to what you are looking for. – Twelfth Sep 23 '13 at 17:08

1 Answers1

0

Assuming that you have your accountTotals in the same table, you could join that table to itself and specify different dates in your WHERE clause. The query should look something like this:

SELECT a.accountingDate, a.accountTotal,
b.accountingDate, b.accountTotal, (b.accountTotal - a.accountTotal) as ChangeInTotal
FROM sourceTbl a
JOIN sourceTbl b on a.x = b.x and a.y = b.y and a.z = b.z
WHERE a.accountingDate = <current year date>
and b.accountingDate = <prior year date>

hope this helps.

user2065377
  • 448
  • 3
  • 12