Format a number with commas SQL Server

select format([Number], 'N0')

select format([Number], 'N2')
2 place of decimal ==> sql server 2012 and +




Aging Report using SQL-Server

declare @AdvanceDate datetime

set @AdvanceDate='02/29/2016'

--select DATEADD(DD,-90,@AdvanceDate)

select

ST, LOB,

case when  cast([Advance Date] as datetime)<DATEADD(DD,0,@AdvanceDate) and cast([Advance Date] as datetime)>=DATEADD(DD,-30,@AdvanceDate) then [Current Balance] else 0 end [Age30],

case when  cast([Advance Date] as datetime)<DATEADD(DD,-30,@AdvanceDate) and cast([Advance Date] as datetime)>=DATEADD(DD,-60,@AdvanceDate) then [Current Balance] else 0 end [Age60],

case when  cast([Advance Date] as datetime)<DATEADD(DD,-60,@AdvanceDate) and cast([Advance Date] as datetime)>=DATEADD(DD,-90,@AdvanceDate) then [Current Balance] else 0 end [Age90],

case when  cast([Advance Date] as datetime)<DATEADD(DD,-90,@AdvanceDate) and cast([Advance Date] as datetime)>=DATEADD(DD,-120,@AdvanceDate) then [Current Balance] else 0 end [Age120],

case when  cast([Advance Date] as datetime)<DATEADD(DD,-120,@AdvanceDate) and cast([Advance Date] as datetime)>=DATEADD(DD,-150,@AdvanceDate) then [Current Balance] else 0 end [Age150],

case when  cast([Advance Date] as datetime)<DATEADD(DD,-150,@AdvanceDate) and cast([Advance Date] as datetime)>=DATEADD(DD,-180,@AdvanceDate) then [Current Balance] else 0 end [Age180],

case when  cast([Advance Date] as datetime)<DATEADD(DD,-180,@AdvanceDate) and cast([Advance Date] as datetime)>=DATEADD(DD,-210,@AdvanceDate) then [Current Balance] else 0 end [Age210],

case when  cast([Advance Date] as datetime)<DATEADD(DD,-210,@AdvanceDate) then [Current Balance] else 0 end [Age>210]  into #temp11

--cast([Advance Date] as datetime)[Advance Date]

from #temp1 where  cast([Advance Date] as datetime)<=@AdvanceDate

select

ST,LOB,sum(Age30)Age30,    sum(Age60)Age60,     sum(Age90)Age90,     sum(Age120)Age120,       sum(Age150)Age150,   sum(Age180)Age180, sum(Age210)Age210,    sum([Age>210])[Age>210]

from  #temp11 group by st,[lob]

SQL For Account Balance fix -F0911 vs F0902

--drop table #temp902

select  GBAID,gbsbl,gbsblt,sum(gban01) Period_01  ,sum(gban02) Period_02 ,

sum(gban03) Period_03 ,sum(gban04) Period_04 ,sum(gban05) Period_05 ,sum(gban06) Period_06 ,sum(gban07) Period_07 ,

sum(gban08) Period_08 ,sum(gban09) Period_09 ,sum(gban10) Period_10 ,sum(gban11) Period_11 ,sum(gban12) Period_12 

,sum(gban13) Period_13

into #temp902 from proddta.f0902 where gbco='00600' and gbfy=15 and gblt='AA'

group by GBAID,gbsbl,gbsblt

--having sum(gban01)<>0

delete #temp902 where period_01=0 AND  period_02=0 AND  period_03=0 AND  period_04=0 AND  period_05=0 AND  period_06=0

 AND  period_07=0 AND  period_08=0 AND  period_09=0 AND  period_10=0 AND  period_11=0 AND  period_12=0  and Period_13=0

select glpn, GLAID,GLsbl,GLsblt,sum(GLAA) Amount into #temp1 from proddta.f0911 where glco='00600'  and glfy=15 

and gllt='AA'  and glpost='P'

group by GLAID,GLsbl,GLsblt,glpn

select glpn,GLAID,GLsbl,GLsblt,

case When glpn=1 then amount else 0 end Period_01,  

case When glpn=2 then amount else 0 end Period_02,  

case When glpn=3 then amount else 0 end Period_03,  

case When glpn=4 then amount else 0 end Period_04,  

case When glpn=5 then amount else 0 end Period_05,  

case When glpn=6 then amount else 0 end Period_06,  

case When glpn=7 then amount else 0 end Period_07,  

case When glpn=8 then amount else 0 end Period_08,  

case When glpn=9 then amount else 0 end Period_09,  

case When glpn=10 then amount else 0 end Period_10,  

case When glpn=11 then amount else 0 end Period_11,  

case When glpn=12 then amount else 0 end Period_12 ,

case When glpn=13 then amount else 0 end Period_13 

into #temp2

from #temp1

--drop table #temp3

select GLAID,GLsbl,GLsblt,

Sum(Period_01)Period_01,Sum(Period_02)Period_02,Sum(Period_03)Period_03,Sum(Period_04)Period_04,Sum(Period_05)Period_05,

Sum(Period_06)Period_06,Sum(Period_07)Period_07,Sum(Period_08)Period_08,Sum(Period_09)Period_09,Sum(Period_10)Period_10,

Sum(Period_11)Period_11,Sum(Period_12)Period_12,Sum(Period_13)Period_13 into #temp3

 from #temp2

 group by GLAID,GLsbl,GLsblt

 delete #temp3

 where period_01=0 AND  period_02=0 AND  period_03=0 AND  period_04=0 AND  period_05=0 AND  period_06=0

 AND  period_07=0 AND  period_08=0 AND  period_09=0 AND  period_10=0 AND  period_11=0 AND  period_12=0  and Period_13=0

-- declare @aid varchar(10)='10205217'

--select * from #temp902 where  gbaid=@aid and gbsbl='01424411'

--select * from #temp3 where  glaid=@aid and glsbl='01424411'

--select * from #temp902 order by 1,2,3

--select * from #temp3 order by 1,2,3

select GLAID,GLsbl,GLsblt,a.period_13,b.period_13 from #temp902 a

left outer join #temp3 b on GLAID=GBAID and GLsbl=GBSBL and GLsblt=gbsblt

where 

a.period_13<>b.period_13