November 16, 2011 at 7:41 pm
I've found many posts on this topic, but haven't quite found a solution. I'd rather handle this in the application layer, but I'm selecting my data with a pivot statement and can't seem to get a running total that way either. PIVOT (SUM CASE WHEN Month <= 'Month Filter Value') doesn't seem to work. So my next idea was to just pre-calculate the totals, and that's easier said than done apparently.
I'm using the "tally table" method, because I already have a calendar table with pre-calculated date diffs. But when I right join to that table, I get the same dataset as if I'd left-joined to the dim_date table, along with a consecutive sequence of months joined to a bunch of NULLS. I know there's something wrong with the join statement, perhaps I need to group them somehow...but how do I restructure this?
SELECT (a bunch of fields)
FROM fact_pymt
LEFT JOIN fact_chg
ON ChgNo = PymtChgNo
LEFT JOIN dim_genparam
ON dim_genparam.Office = fact_pymt.Office
LEFT JOIN dim_provider
ON ProvPK = fact_chg.ChgProviderID
LEFT JOIN dim_pos
ON POSPK = fact_chg.ChgPOSCodeID
LEFT JOIN dim_revctrcode
ON RevCtrCodePK = fact_chg.ChgRevCtrID
LEFT JOIN dim_insurance
ON InsPK = fact_pymt.PymtInsCodeID
RIGHT JOIN dim_date
ON date_id = PymtPostDateID
WHERE
dim_date.YOY_Month_12_Diff > 0 AND dim_date.Year_Diff BETWEEN 0 AND 2
November 17, 2011 at 10:52 am
Is it safe to assume that this:
RIGHT JOIN dim_date
ON date_id = PymtPostDateID
is hooked off fact_pymt? That it's not is one of the few reasons I can think of that your calendar table isn't filling in missing values.
In cases like this, you need to use the calendar table as the core table, and hook everything else off of that.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply