doing some math in query

Question

doing some math in query

middletree

Hall of Fame

Points: 3316
More actions
August 20, 2009 at 6:27 am

#132796

I'm asked to report some fields from a particular table, most of which are straightforward. The last two, however, are "2009 list price" and "2008 list price". In this table, we have fields which names are somewhat explanatory: Todate, FromDate. In a row, the price would be $8.00, and the ToDate would be 12/31/2009, and the FromDate would be 1/1/2009.
In another row, the same product would be listed, and the ToDate would be 12/31/2008, and the FromDate would be 1/1/2008.
I know this is not an ideal setup, and I wish I knew who set it up this way so I could clobber them. However, for now, I am struggling with how to do the SELECT statement in such as way as to capture results which will end up like this:
2009 Price: $8.00
2008 Price: $7.80
Help appreciated.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Not being mind readers or having access to your systems, can't really help you from here without more help from you. Please provide the DDL for the table(s) (Reduce it to the necessary columns to show us the problem if you need to), sample data for the table(s) is a readily consummable format, the expected results from the query, and the code you have written so far to solve your problem.

For help with this, please read and follow the instructions in the first article I have referenced in my signature block. Doing that, you will find that you will find people more willing to jump in and help you plus you'll get tested code in return.

Jeff Moden SSC Guru Points: 1004353 More actions · Answer 2

middletree (8/20/2009)
I'm asked to report some fields from a particular table, most of which are straightforward. The last two, however, are "2009 list price" and "2008 list price". In this table, we have fields which names are somewhat explanatory: Todate, FromDate. In a row, the price would be $8.00, and the ToDate would be 12/31/2009, and the FromDate would be 1/1/2009.
In another row, the same product would be listed, and the ToDate would be 12/31/2008, and the FromDate would be 1/1/2008.
I know this is not an ideal setup, and I wish I knew who set it up this way so I could clobber them. However, for now, I am struggling with how to do the SELECT statement in such as way as to capture results which will end up like this:
Actually, it is the right setup and it's know as an SCD2 or "Slowly Changing Dimension Type 2" and it's very, very useful because you never have to add a column to a table to accomodate extra time frames such as years. I recommend you lookup the term on WikiPedia where they have some great explanations and examples of SCD's,
2009 Price: $8.00
2008 Price: $7.80
Help appreciated.

SELECT ProductID,

ProductName,

MAX(CASE WHEN FromDate ='20080101' THEN ListPrice END) AS [2008 list price],

MAX(CASE WHEN FromDate ='20090101' THEN ListPrice END) AS [2009 list price]

FROM dbo.yourtablehere

GROUP BY ProductID, ProductName

ORDER BY ProductName

Now, if that doesn't do it for you, then Lynn is absolutely correct... we need more info to help you correctly. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)