Report Bulanan OCA

gistfile1.txt

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		NULL
-- Create date: 
-- Description:	
-- =============================================
ALTER PROCEDURE [dbo].[Report_OCA] 
	-- Add the parameters for the stored procedure here
	@tahun varchar (4),
	@from varchar(2),
	@to varchar(2)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	select tahun, bulan,sum(cicilan) as cicilan,sum(oca) oca,sum(payment) payment,sum(oca)+sum(cicilan) total,
	round((sum(payment)/sum(cicilan)* 100 ),2) persen
from
(
	select tahun, bulan,sum(cicilan) as cicilan,'' oca,'' payment
	from 
	(
		select year(cicilan1_dt) tahun, month(cicilan1_dt) bulan, cicilan1 cicilan from tb_dtinstallment 
		union all 
		select year(cicilan2_dt) tahun, month(cicilan2_dt) bulan, cicilan2 cicilan from tb_dtinstallment 
		union all 
		select year(cicilan3_dt) tahun, month(cicilan3_dt) bulan, cicilan3 cicilan from tb_dtinstallment 
		union all 
		select year(cicilan4_dt) tahun, month(cicilan4_dt) bulan, cicilan4 cicilan from tb_dtinstallment 
		union all 
		select year(cicilan5_dt) tahun, month(cicilan5_dt) bulan, cicilan5 cicilan from tb_dtinstallment 
		union all 
		select year(cicilan6_dt) tahun, month(cicilan6_dt) bulan, cicilan6 cicilan from tb_dtinstallment 
		union all
		select year(cicilan7_dt) tahun, month(cicilan7_dt) bulan, cicilan7 cicilan from tb_dtinstallment
		union all
		select year(cicilan8_dt) tahun, month(cicilan8_dt) bulan, cicilan8 cicilan from tb_dtinstallment
		union all
		select year(cicilan9_dt) tahun, month(cicilan9_dt) bulan, cicilan9 cicilan from tb_dtinstallment
		union all
		select year(cicilan10_dt) tahun, month(cicilan10_dt) bulan, cicilan10 cicilan from tb_dtinstallment
		union all
		select year(cicilan11_dt) tahun, month(cicilan11_dt) bulan, cicilan11 cicilan from tb_dtinstallment
		union all
		select year(cicilan12_dt) tahun, month(cicilan12_dt) bulan, cicilan12 cicilan from tb_dtinstallment
		union all
		select year(cicilan13_dt) tahun, month(cicilan13_dt) bulan, cicilan13 cicilan from tb_dtinstallment
		union all
		select year(cicilan14_dt) tahun, month(cicilan14_dt) bulan, cicilan14 cicilan from tb_dtinstallment
		union all
		select year(cicilan15_dt) tahun, month(cicilan15_dt) bulan, cicilan15 cicilan from tb_dtinstallment
	) tx 
	--where tahun is not null and tahun=2014 and bulan between 01 and 05 
	group by tahun, bulan 

	union all

	select tahun, bulan,'' cicilan,sum(cicilan) as OCA ,'' payment
	from 
	( 
		select year(OCA_BULAN) tahun, month(OCA_BULAN) bulan, SISA_TAGIHAN cicilan from tb_dtinstallment 
	) tx 
	--where tahun is not null and tahun=2014 and bulan between 01 and 05 
	group by tahun, bulan 
	
	union all

	select tahun, bulan, ''cicilan,'' oca,sum(payment) as payment 
	from 
	( 
		select year(payment1_dt) tahun, month(payment1_dt) bulan, payment1 payment from tb_dtinstallment 
		union all 
		select year(payment2_dt) tahun, month(payment2_dt) bulan, payment2 payment from tb_dtinstallment 
		union all 
		select year(payment3_dt) tahun, month(payment3_dt) bulan, payment3 payment from tb_dtinstallment 
		union all 
		select year(payment4_dt) tahun, month(payment4_dt) bulan, payment4 payment from tb_dtinstallment 
		union all 
		select year(payment5_dt) tahun, month(payment5_dt) bulan, payment5 payment from tb_dtinstallment 
		union all
		select year(payment6_dt) tahun, month(payment6_dt) bulan, payment6 payment from tb_dtinstallment
		union all
		select year(payment7_dt) tahun, month(payment7_dt) bulan, payment7 payment from tb_dtinstallment
		union all
		select year(payment8_dt) tahun, month(payment8_dt) bulan, payment8 payment from tb_dtinstallment
		union all
		select year(payment9_dt) tahun, month(payment9_dt) bulan, payment9 payment from tb_dtinstallment
		union all
		select year(payment10_dt) tahun, month(payment10_dt) bulan, payment10 payment from tb_dtinstallment
		union all
		select year(payment11_dt) tahun, month(payment11_dt) bulan, payment11 payment from tb_dtinstallment
		union all
		select year(payment12_dt) tahun, month(payment12_dt) bulan, payment12 payment from tb_dtinstallment
		union all
		select year(payment13_dt) tahun, month(payment13_dt) bulan, payment13 payment from tb_dtinstallment
		union all
		select year(payment14_dt) tahun, month(payment14_dt) bulan, payment14 payment from tb_dtinstallment
		union all
		select year(payment15_dt) tahun, month(payment15_dt) bulan, payment15 payment from tb_dtinstallment
	) tx 
	--where tahun is not null and tahun=2014 and bulan between 01 and 04 
	group by tahun, bulan
		
)z 
where tahun is not null and tahun=@tahun and bulan between @from and @to 
group by tahun,bulan
END