June 14, 2013 at 10:43 am
I am trying to create a PIVOT table that produces the sum of balances that newly became exactly 1, 10, 21, 31, 61, 91, 121, 151, 181, 211 days past due each day since December 1, 2012. The data I need is entirely contained in a historical data warehouse called ACCT_MASTER_HISTORY. Here are my queries...
select ASOFDATE, DAYSPD, COUNT (account) as 'Acct', sum (ttl_bal-disc) as 'Balance', sum (ttl_bal) as 'TotalBal'
into #temp
from dbo.ACCT_MASTER_HISTORY
where DAYSPD in (1, 10, 21, 31, 61, 91, 121, 151, 181, 211) and TTL_BAL>0 and ASOFDATE > 1130531 and CLASS_CD not between '90' and '99'
group by ASOFDATE, DAYSPD
order by asofdate, DAYSPD
select DAYSPD as Bucket, [1],[10],[21],[31],[61],[91],[121],[151],[181],[211]
from
(select asofdate, totalbal
from #temp) as SourceTable
Pivot
(asofdate, totalbal for dayspd in ([1],[10],[21],[31],[61],[91],[121],[151],[181],[211])
as PivotTable;
I am new to this type of query and usually would dump the raw to Excel and do the pivot there. Any help would be greatly appreciated.
Thanks all!
June 14, 2013 at 10:45 am
I should have said since 06/01/2013... I was keeping my data set small while I hammered this out.
June 14, 2013 at 10:46 am
I fixed the reference to DaysPd to Bucket... that did not work.
June 14, 2013 at 10:52 am
Without sample data to test, I might be giving you the wrong solution but with the previous mentioned articles you should be able to fix it or ask specific questions.
SELECT ASOFDATE,
SUM( CASE WHEN DAYSPD = 1 THEN TotalBal END) AS 1,
SUM( CASE WHEN DAYSPD = 10 THEN TotalBal END) AS 10,
SUM( CASE WHEN DAYSPD = 21 THEN TotalBal END) AS 21,
SUM( CASE WHEN DAYSPD = 31 THEN TotalBal END) AS 31,
SUM( CASE WHEN DAYSPD = 61 THEN TotalBal END) AS 61,
SUM( CASE WHEN DAYSPD = 91 THEN TotalBal END) AS 91,
SUM( CASE WHEN DAYSPD = 121 THEN TotalBal END) AS 121,
SUM( CASE WHEN DAYSPD = 151 THEN TotalBal END) AS 151,
SUM( CASE WHEN DAYSPD = 181 THEN TotalBal END) AS 181,
SUM( CASE WHEN DAYSPD = 211 THEN TotalBal END) AS 211
FROM(
select ASOFDATE, DAYSPD, COUNT (account) as 'Acct', sum (ttl_bal-disc) as 'Balance', sum (ttl_bal) as 'TotalBal'
from dbo.ACCT_MASTER_HISTORY
where DAYSPD in (1, 10, 21, 31, 61, 91, 121, 151, 181, 211) and TTL_BAL>0 and ASOFDATE > 1130531 and CLASS_CD not between '90' and '99'
group by ASOFDATE, DAYSPD) x
group by ASOFDATE
order by asofdate, DAYSPD
You should take a look at the link in my signature to get better help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply