| Author |
 Topic  |
|
|
jafrywilson
Constraint Violating Yak Guru
India
379 Posts |
 Posted - 07/19/2012 : 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)
Singapore
16769 Posts |
Posted - 07/19/2012 : 02:31:35
|
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
KH
Time is always against us
|
 |
|
|
jafrywilson
Constraint Violating Yak Guru
India
379 Posts |
Posted - 07/19/2012 : 02:52:03
|
| Thank you for your help.. |
|
|
| |
Topic |
|
|
|
Query simplification
