Query simplification - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query simplification

Author

Topic

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2012-07-19 : 01:04:22

I have created this query this works fine for me.. Is there any possibility to improve my query..

SELECT
DATEADD(mm,DATEDIFF(mm,0,CreatedDate),0) AS FirstDayofCreatedMonth,
sum(isnull(AmtPaid,0)) AmountPaid,instrument_name
into #temp FROM
(Class_Payment_History join class on class.ClassId=Class_Payment_History.ClassId

join InstrumentDetails on InstrumentDetails.UID=class.instrument)

GROUP BY
DATEADD(mm,DATEDIFF(mm,0,CreatedDate),0),class.instrument,instrument_name

create table #tmpFinal (InsName varchar(20),Jan float ,Feb float,Mar float,Apr float,May float,Jun float,Jul float,
Aug float,Sep float,Oct float,Nov float,Dec float)

insert into #tmpFinal (InsName)
select instrument_name from #temp

----------Jan -1 ---------
update #tmpFinal set Jan = t1.AmountPaid
from #tmpFinal tF,#temp t1 where month(t1.FirstDayofCreatedMonth) = 1 and tF.InsName = t1.instrument_name
----------Feb -2 ---------
update #tmpFinal set Feb = t1.AmountPaid
from #tmpFinal tF,#temp t1 where month(t1.FirstDayofCreatedMonth) = 2 and tF.InsName = t1.instrument_name
----------Mar -3 ---------
update #tmpFinal set Mar = t1.AmountPaid
from #tmpFinal tF,#temp t1 where month(t1.FirstDayofCreatedMonth) = 3 and tF.InsName = t1.instrument_name
----------Apr -4 ---------
update #tmpFinal set Apr = t1.AmountPaid
from #tmpFinal tF,#temp t1 where month(t1.FirstDayofCreatedMonth) = 4 and tF.InsName = t1.instrument_name
----------May -5 ---------
update #tmpFinal set May = t1.AmountPaid
from #tmpFinal tF,#temp t1 where month(t1.FirstDayofCreatedMonth) = 5 and tF.InsName = t1.instrument_name
----------Jun -6 ---------
update #tmpFinal set Jun = t1.AmountPaid
from #tmpFinal tF,#temp t1 where month(t1.FirstDayofCreatedMonth) = 6 and tF.InsName = t1.instrument_name
----------Jul -7 ---------
update #tmpFinal set Jul = t1.AmountPaid
from #tmpFinal tF,#temp t1 where month(t1.FirstDayofCreatedMonth) = 7 and tF.InsName = t1.instrument_name
----------Aug -8 ---------
update #tmpFinal set Aug = t1.AmountPaid
from #tmpFinal tF,#temp t1 where month(t1.FirstDayofCreatedMonth) = 8 and tF.InsName = t1.instrument_name
----------Sep -9 ---------
update #tmpFinal set Sep = t1.AmountPaid
from #tmpFinal tF,#temp t1 where month(t1.FirstDayofCreatedMonth) = 9 and tF.InsName = t1.instrument_name
----------OCt -10 ---------
update #tmpFinal set Oct = t1.AmountPaid
from #tmpFinal tF,#temp t1 where month(t1.FirstDayofCreatedMonth) = 10 and tF.InsName = t1.instrument_name
----------Nov -11 ---------
update #tmpFinal set Nov = t1.AmountPaid
from #tmpFinal tF,#temp t1 where month(t1.FirstDayofCreatedMonth) = 11 and tF.InsName = t1.instrument_name
----------Dec -12 ---------
update #tmpFinal set Dec = t1.AmountPaid
from #tmpFinal tF,#temp t1 where month(t1.FirstDayofCreatedMonth) = 12 and tF.InsName = t1.instrument_name

---------------------------
select * from #tmpFinal

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-07-19 : 02:31:35

[code]
SELECT *
FROM
(
SELECT instrument_name,
month(CreatedDate) AS Mth,
sum(isnull(AmtPaid,0)) AS AmountPaid
FROM Class_Payment_History
join class on class.ClassId = Class_Payment_History.ClassId
join InstrumentDetails on InstrumentDetails.UID= class.instrument
GROUP BY
DATEADD(mm,DATEDIFF(mm,0,CreatedDate),0),class.instrument,instrument_name
) as D
PIVOT
(
SUM(AmountPaid)
FOR Mth in ([1], [2], [3], ... [12])
) as P[/code]

KH
[spoiler]Time is always against us[/spoiler]

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2012-07-19 : 02:52:03

Thank you for your help..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources