Pages

Tuesday, May 27, 2014

Contoh Stored Procedures SQL Server


Berikut Ini Contoh Stored Procedures Sederhana .



USE [ACCOUNTINGDB]
GO
/****** Object:  StoredProcedure [dbo].[spvw_SummFreight_ShippingCharge]    Script Date: 05/28/2014 08:18:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
-- Create store procedure
-- By Kusdi Mei-2014
CREATE PROCEDURE [dbo].[spvw_SummFreight_ShippingCharge]
(
@month int,
@year int
)
 AS
--deklarasi variable 
declare 
@FRD_FRDCode int,
@FI_CurrencyCode int, 
@air_import numeric(18,2),
@air_export numeric(18,2), 
@sea_import numeric(18,2), 
@sea_export numeric(18,2)

--set @month=5
--set @year=2014
begin 
    create table #Temp_FreightInvoice( 
no_urut int,
forwadercode int,
CurrencyCode int,
air_import numeric(18,2) null,
air_export numeric(18,2) null,
sea_import numeric(18,2) null,
sea_export numeric(18,2) null)
--select * from #Temp_FreightInvoice
--insert from  select
insert into #Temp_FreightInvoice
(forwadercode,CurrencyCode)
select FRD_FRDCode,FI_CurrencyCode   
from  accountingdb..TBTI_FreightInvoice 
inner join  accountingdb..vw_mst_currency on FI_CurrencyCode  = cast(CRC_CurrencyCode as int)
inner join PPSDB..TB_MST_Forwarders on FRD_FRDCode = FI_ForwarderCode
where FI_flag <> 25 AND FI_Source = 22 and FI_Flag = 2 and month(FI_PaymentDate)=@month and  year(FI_PaymentDate)=@year
group by FRD_FRDCode,FI_CurrencyCode
    --buat deklarasi cursor
declare FreightInvoice cursor fast_forward for
select FRD_FRDCode,FI_CurrencyCode      
from  accountingdb..TBTI_FreightInvoice 
inner join  accountingdb..vw_mst_currency on FI_CurrencyCode  = cast(CRC_CurrencyCode as int)
inner join PPSDB..TB_MST_Forwarders on FRD_FRDCode = FI_ForwarderCode
where FI_flag <> 25 AND FI_Source = 22 and FI_Flag = 2 and month(FI_PaymentDate)=@month and  year(FI_PaymentDate)=@year
group by FRD_FRDCode,FI_CurrencyCode

Open FreightInvoice
Fetch next from FreightInvoice into @FRD_FRDCode,@FI_CurrencyCode
while @@Fetch_status = 0
begin
--summ air - import
select  @air_import= sum(FI_Amount)  
from  accountingdb..TBTI_FreightInvoice
where month(FI_PaymentDate)=@month and  year(FI_PaymentDate)=@year   
and FI_ShippingMode='Air - Import' and FI_ForwarderCode=@FRD_FRDCode 
and FI_CurrencyCode=@FI_CurrencyCode
  
--summ Air - Export
select @air_export= sum(FI_Amount)
from  TBTI_FreightInvoice
where month(FI_PaymentDate)=@month and  year(FI_PaymentDate)=@year 
and FI_ShippingMode='Air - Export' and FI_ForwarderCode=@FRD_FRDCode
and FI_CurrencyCode=@FI_CurrencyCode
--summ Sea - Import
select  @sea_import= sum(FI_Amount)
from  TBTI_FreightInvoice
where month(FI_PaymentDate)=@month and  year(FI_PaymentDate)=@year 
and FI_ShippingMode='Sea - Import' and FI_ForwarderCode=@FRD_FRDCode
and FI_CurrencyCode=@FI_CurrencyCode
--summ Sea - Import
select @sea_export= sum(FI_Amount)
from  TBTI_FreightInvoice
where month(FI_PaymentDate)=@month and  year(FI_PaymentDate)=@year 
and FI_ShippingMode='Sea - Export' and FI_ForwarderCode=@FRD_FRDCode
and FI_CurrencyCode=@FI_CurrencyCode   
   
   --update temp tabel  
update #Temp_FreightInvoice
set 
air_import=@air_import,
air_export=@air_export,
sea_import=@sea_import,
sea_export=@sea_export
Where forwadercode =@FRD_FRDCode and CurrencyCode =@FI_CurrencyCode

Fetch next from FreightInvoice into @FRD_FRDCode,@FI_CurrencyCode
end
Close FreightInvoice
Deallocate FreightInvoice 
end   

--select data temporary
select FRD_FRDName, CRC_CurrencyName, air_import, air_export ,sea_import , sea_export
from #Temp_FreightInvoice
inner join accountingdb..vw_mst_currency on CurrencyCode   = cast(CRC_CurrencyCode as int)
inner join PPSDB..TB_MST_Forwarders on  forwadercode=FRD_FRDCode 

--drop temporary table 
Drop table #Temp_FreightInvoice

Monday, May 26, 2014

Cara mengaktifkan copy paste pada virtualbox ubuntu

Masuk Pada menu :

Devices - > install guest Aditionals

kemudian Click Download
apabila sudah selesai Install sampai selesai dengan cara menekan tombol next sampai Finish.
kemudian restart OS Guest nya (OS yang ada dalam virtual Box).

Selamat Mencoba