SQL
mssql unpivot
Wednesday, 02 November 2011 16:55 
ALTER PROCEDURE [dbo].[5upr1_sp_cobaCoba] 
(	
	-- Add the parameters for the function here
@calMonth varchar(2)
,@calYear varchar(4)
) AS
DECLARE @cmd varchar(max)
,@columns varchar(max)

set @columns = ''
select @columns = @columns + '['+cmp_CompanyName+'],'  from masters.Company where cmp_ParentCompany = '6001' and  cmp_ParentCompany <> cmp_CompanyCode
select @columns = substring(@columns, 0, len(@columns))

set @cmd = 'select * from (
SELECT bss_ID      
      ,bss_CalYear
      ,bss_CalMonth
      ,bss_DescriptionCode
      ,dec_DescriptionName
      ,cmp_CompanyName
      ,bss_YTDBudget
  FROM [reports].[BalanceSheetSummary] a
  left join masters.Company b on a.bss_EntityID = b.cmp_CompanyCode 
  left join masters.DescriptionCode c on a.bss_DescriptionCode = c.dec_DescriptionCode
  where bss_CalMonth = '+ @calMonth +' and bss_CalYear =' +  @calYear +'
  ) p
  PIVOT
  (sum(bss_YTDBudget) FOR cmp_CompanyName IN (  
  '+ @columns +'
  )) bud order by bss_DescriptionCode'  
 exec (@cmd)
 
MSSQL - Membuat total cumulative
Wednesday, 02 November 2011 12:13 
select a.calYear, a.calMonth, a.descriptionCode, a.budgetValue, SUM(b.budgetValue) cumulative
from dbo.fn_reportBSDetailMonthly('6500','2012','01','12') a
left join 
(
select calYear, calMonth, descriptionCode, budgetValue
from dbo.fn_reportBSDetailMonthly('6500','2012','01','12')
--where descriptionCode = 'BSDE-8-2-3-0000'

) b on a.calYear = b.calYear and a.descriptionCode = b.descriptionCode and a.calMonth >= b.calMonth
--where a.descriptionCode = 'BSDE-8-2-3-0000'
group by a.calYear, a.calMonth, a.descriptionCode,a.budgetValue
 
SQL Server merubah data UPDATE dengan SELECT
Thursday, 08 September 2011 10:49

Di SQL Server anda dapat memasukan data kedalam table dari  table lain

INSERT INTO table(col,col2,col3)
SELECT col,col2,col3 FROM other_table WHERE sql = 'cool'

begitu juga  merubah table dengan cara sama dari table lain

UPDATE target_table SET  copy_table.col1 = src.col5 FROM source_table src 
JOIN table_target ON table_target.col2 = src.col2

dan untuk menduplikasi table

SELECT col,col2,col3 INTO copy_table FROM other_table WHERE sql = 'cool'

 
 
mengidentifikasi Versi dan Edisi MSSQL Server
Friday, 03 June 2011 16:13 

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
sumber: http://support.microsoft.com/kb/321185

 
 
Error: System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing ‘dbName’ database
Friday, 15 April 2011 16:21

Masalah,

Gagal waktu mencoba merestore backup file SQL Server 2008 yang muncul error :

Restore failed for Server ‘SQL Server name‘. (Microsoft.SqlServer.Smo) Additional information: System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing [dbName] database. (Microsoft.SqlServer.Smo)

Pesan error tersebut menunjukan kareana proses restore ke database yang berbeda.

Solusinya,

Setelah memilih file yang akan direstore dan databasenya

1. Dari Menu Restore Database, di menu kiri pilih Options kemudian di Restore options pilih "Overwritte the existing database (WITH REPLACE)", pilihan ini akan mengubah semua data yang ada di database tujuan.

2. Tekan tombol OK untuk merestore file bakup-nya, hasilnya proses restore berhasil
 
More Articles...
<< Start < Prev 1 2 Next > End >>

Page 1 of 2
Joomla templates by a4joomla