September 23, 2009 at 3:01 pm
I have a very un-normalized table and have been asked to add two columns, Trx_Volume_YTD and Trx_Volume_LYTD (last year to date) and populate them.
Product Type Indication Strength Year_Int Month_Int Trx_Volume
A Other GGF 100 2008 6 492
A Other GGF 100 2008 7 331
A Other GGF 50 2008 6 443
A In-House GGF 100 2008 6 401
A In-House BPD 100 2008 7 343
B Other GGF 50 2008 6 299
In other words, for each Year-Month combination I have at least some if not all combinations of Product, Type, Indication and Strength. Each one needs a Volume_YTD and a Volume_LYTD.
Oddly, this table also uses 0-11 for months. January = 0, December = 11.
I have been playing around with something like this, but I'm only able to get the last month's number, not the year-to-date total...
SELECT
A.Product,
A.Type,
A.Indication,
A.Strength,
A.[Year_Int],
A.[Month_Int],
A.Trx_Volume,
COALESCE(B.Trx_Volume,0) as VOLUME_YTD
FROM FINANCIALS_RETAIL_MARKET_month as A
LEFT JOIN FINANCIALS_RETAIL_MARKET_month as B
ON A.Product = B.Product
AND A.ype = B.Type
AND A.Indication = B.Indication
AND A.Strength = B.Strength
AND CASE WHEN A.Month_Int = 0 THEN A.Year_Int - 1 ELSE A.Year_Int END = B.Year_Int
AND CASE WHEN A.Month_Int = 0 THEN 11 ELSE A.Month_Int-1 END = B.Month_Int
Results for the same Product/Type/Indication/Strength combination... (left those columns off to make data fit)
Year_Int Month_Int Trx_Volume my query
-------------------------------------------------------
2008 6 492 0
2008 7 496 492
2008 8 510 496
2008 9 421 510
2008 10 484 421
2008 11 455 484
2009 0 497 0
2009 1 500 497
2009 2 528 500
2009 3 492 528
2009 4 458 492
2009 5 449 458
The result set should look like this (hand-entered the numbers)
Year_Int Month_Int Trx_Volume YTD_correct LYTD_correct
------------------------------------------------------------------------
2008 6 492 492 0 (no data)
2008 7 496 988 988
2008 8 510 1498 1498
2008 9 421 1919 1919
2008 10 484 2403 2403
2008 11 455 2858 2858
2009 0 497 497 3355
2009 1 500 997 3855
2009 2 528 1525 4383
2009 3 492 2017 4875
2009 4 458 2475 5333
2009 5 449 2924 5782
Does anyone have any suggestions? I tried searching, but found nothing that fits exactly. I am at a loss as to how to proceed.
Thanks in advance, Max
September 23, 2009 at 3:26 pm
You might want to look into to sample code attached to Jeff Modens Running Totals article . Please make sure you read the discussion related to the article.
If you use the "quirky update" (which I'd recommend), you still should do some deep testing for getting the expected result.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply