August 8, 2011 at 10:19 am
I have a table with account, period, and balance and I'd like to age these balances out so that I have a table that gives me account, period, 30 Days, 60 Days, 90 Days.... < 180 days based on the period column. So if I have a balance of 100$ in account 1234 that is from June, it'll list that balance in 60 days since it's 2 months ago.
I tried using a case statement, but it lumps it all into one column which doesn't help. Thought of using an IF statement, but that doesn't seem to be doing it.
CASE WHEN FactPeriodBalHist.FK_PeriodDateKey > dateadd(month, - 1,FactPeriodBalHist.FK_PeriodDateKey), 112) THEN balance ELSE 0 END AS balance
I can't figure out how to alias things inside the case to give me separate columns.
if you can suggest a pivot table, I'm open to anything at this point.
August 8, 2011 at 10:23 am
You're 99% there.
you need SUM(CASE WHEN ...)
GROUP BY Not pivoted column.
August 8, 2011 at 10:41 am
I'm even closer, but it's still putting the balance in one column. I need it to split it out into multiple columns.
so a row has a balance of 100$ a period of 6/1/2011 and account of 1234. I want the row to look like this:
acct|perioddate|30|60 |90|120|150|180|
1234|6/1/2011 |0 |100|0 |0 |0 |0 |
possible?
August 8, 2011 at 10:44 am
If you have data and you're group by are fine the sum(case) will always work.
Can you post the full query, I'm sure it's really simple to fix!
August 8, 2011 at 10:53 am
You asked for it, it's icky!
SELECT
FactPeriodBalHist.FK_EntityKey,
FactPeriodBalHist.FK_AccountKey,
FactPeriodBalHist.FK_PayorKey,
FactPeriodBalHist.FK_PeriodDateKey,
DimPlan.ArAccount,
SUM(CASE WHEN datetime > dateadd(month, - 1, datetime) THEN balance ELSE 0 END) AS bal
FROM FactPeriodBalHist INNER JOIN
DimDate ON FactPeriodBalHist.FK_PeriodDateKey = DimDate.PK_DateKey LEFT OUTER JOIN
DimPlan ON FactPeriodBalHist.FK_PlanKey = DimPlan.PK_PlanKey AND FactPeriodBalHist.FK_EntityKey = DimPlan.FK_EntityKey
WHERE (FactPeriodBalHist.FK_ProcessedDateKey = 20110701)
GROUP BY FactPeriodBalHist.FK_AccountKey, FactPeriodBalHist.FK_EntityKey, FactPeriodBalHist.FK_AccountKey, FactPeriodBalHist.FK_PayorKey, FactPeriodBalHist.FK_PeriodDateKey, FactPeriodBalHist.Balance, DimPlan.ArAccount, DimDate.PK_DateKey
August 8, 2011 at 10:55 am
Did you have a look at this: T-SQL Tuesday #18 – Using CTEs to Pivot Data Into Date Ranges: http://www.sqlsoldier.com/wp/sqlserver/usingctestopivotdataintodateranges
August 8, 2011 at 11:01 am
Reformatted also to be easier on the eyes.
I'm pretty sure you need to remove the period key so that it pivots correctly, but I can't be sure without seeing the data.
Also I'm nt 100% sure balance is the correct column to pivot on, again can't tell without the data.
SELECT
FactPeriodBalHist.FK_EntityKey
, FactPeriodBalHist.FK_AccountKey
, FactPeriodBalHist.FK_PayorKey
, FactPeriodBalHist.FK_PeriodDateKey
, DimPlan.ArAccount
, SUM(CASE WHEN datetime > dateadd(month , -1 , datetime) THEN balance
ELSE 0
END) AS [30 days]
, SUM(CASE WHEN datetime > dateadd(month , -2 , datetime) THEN balance
ELSE 0
END) AS [60 days]
, SUM(CASE WHEN datetime > dateadd(month , -3 , datetime) THEN balance
ELSE 0
END) AS [90 days]
, SUM(CASE WHEN datetime > dateadd(month , -6 , datetime) THEN balance
ELSE 0
END) AS [180 days]
, SUM(balance) AS Balance
FROM
FactPeriodBalHist
INNER JOIN DimDate
ON FactPeriodBalHist.FK_PeriodDateKey = DimDate.PK_DateKey
LEFT OUTER JOIN DimPlan
ON FactPeriodBalHist.FK_PlanKey = DimPlan.PK_PlanKey
AND FactPeriodBalHist.FK_EntityKey = DimPlan.FK_EntityKey
WHERE
( FactPeriodBalHist.FK_ProcessedDateKey = 20110701 )
GROUP BY
FactPeriodBalHist.FK_AccountKey
, FactPeriodBalHist.FK_EntityKey
, FactPeriodBalHist.FK_AccountKey
, FactPeriodBalHist.FK_PayorKey
, FactPeriodBalHist.FK_PeriodDateKey
, FactPeriodBalHist.Balance
, DimPlan.ArAccount
, DimDate.PK_DateKey
August 8, 2011 at 11:02 am
oh MAN! You've got separate cases for each column. Okay, feeling a bit dim today 🙂 Thank you so much.
August 8, 2011 at 11:07 am
Told you the fix was simple. 🙂
Take 5 and get back to it. I'm sure you'll get it from now on 😉
August 8, 2011 at 11:44 am
gonna go grab some food 🙂 Thanks so much!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply