Create Calendar use SQL Script

kadang kita pingin bikin periode kalender dari tanggal berapa s/d berapa.
berikut scriptnya. Tapi hanya berlaku mulai SQL 2005 Server.
di sql server 2000 tidak bisa (harus dengan cara lain).

1.) bikin tabel kalender dulu,
berikut scriptnya

create table kalender
(
no int identity(1,1) not null,
[date] datetime not null,

)

2.) bikin funcion yang namanya 'tabeltanggal' yang akan generate periode kalendernya
berikut scriptnya


CREATE FUNCTION [dbo].[tabeltanggal]
(
@FirstDate datetime,
@LastDate datetime
)
RETURNS @datetable TABLE (
[date] datetime
)
AS
BEGIN

SELECT @FirstDate = DATEADD(dd, 0, DATEDIFF(dd, 0, @FirstDate)); SELECT @LastDate = DATEADD(dd, 0, DATEDIFF(dd, 0, @LastDate));
WITH CTE_DatesTable
AS
(
SELECT @FirstDate AS [date]
UNION ALL
SELECT DATEADD(dd, 1, [date])
FROM CTE_DatesTable
WHERE DATEADD(dd, 1, [date]) <= @LastDate
)
INSERT INTO @datetable ([date])
SELECT [date] FROM CTE_DatesTable
OPTION (MAXRECURSION 0)

RETURN
END

3.) bikin script untuk generate periode kalender yang diinginkan
misal akan bikin kalender periode 01 desember 2010 s/d 10 desember 2010
yaitu sbb:

insert into kalender ([date])
select [date] from tabeltanggal('20101201', '20101210')

4.) silakan liat hasilnya di tabel kalender;

select * from kalender

select certain datetime use convert varchar at SQL Server

bila suatu tabel di SQL server terdapat field dengan type datetime, dengan kondisi time nya ikut diperhitungkan (bukan 00:00:00.000) . Berikut cara select date saja dan timenya diabaikan:

misal cari data antara tanggal '2011-03-08' s/d '2011-03-10'



select * from tabel where CONVERT( varchar(10),namakolom,121) between '2011-03-08' and '2011-03-10'

row set and top at SQL Server

if your SQL server cannot run Select TOP 10 from table_name

please use :
SET ROWCOUNT 10 will limit the number of rows SQL Server returns to 10.
To turn it off, SET ROWCOUNT 0.

e.g

SET ROWCOUNT 10
select * from lusr_tbl
SET ROWCOUNT 0

script update data in to same table

below script to update top 5 row a column, into same table:


update tbl_user set user_gift= '99' from (select top 5 user_id from tbl_user ) a where tbl_user.user_id =a.user_id


and below for table:

user_id user_name user_gift
-------- ------------ -------------
1 user01 99
2 user02 99
3 user03 99
4 user04 99
5 user05 99
6 user06 00
7 user07 00

MS SQL Server: how backup database to mapping drive

Below steps to set backup database to mapping drive;

(1). please make sure the service of MSSQL Server instant not logon as Local Account:
- go to control panel - services
- select MSSQL Server service then right click
- changes logon from local to administrator or same domain as login to mapping drive
- restart the services to make effect
(2) . from query analizer of Ms. SQL Server: use below script;
BACKUP DATABASE yourDBname TO DISK ='\\192.168.100.15\yourDBname.bak'

to best performance: please use LAN Card and SWITCH 1GB for server and client

SQL script to update another table

berikut contoh script SQL Server untuk update suatu column dari tabel lain: dengan kondisi jumlah row dari data source lebih sedikit dari jumlah row dari tabel yang akan di update:

Update mpn SET EQTY_MPN = aa.quantity from (select part,quantity from vwimport ) AS aa where mpn.PN_MPN = aa.part

bila ingin quantity kolom asal dijumlah dengan quantity data updatedmaka akan menjadi sbb:


Update mpn SET EQTY_MPN = (EQTY_MPN+aa.quantity) from (select part,quantity from vwimport ) AS aa where mpn.PN_MPN = aa.part

macam query di SQLServer

karena sering lupa, maka perlu ditulis disini:
biar mudah dicari bila diperlukan.

::: current date time
update pre_tbl set PRE_DATE = GETDATE() where PRE_CRDNO = 'CRD-123460' ;
insert into PRE_TBL values ('CRD-123461','V03A00049900',50,GETDATE());
SELECT * FROM tabel where tabel_date = CURDATE( );

::: group berdasarkan nama bulan, in dan out

select hari as bulan,
sum (case when a.remark = 'I' then jumlah else 0 end)Incoming,
sum (case when a.remark = 'O' then jumlah else 0 end)Outgoing
from (SELECT remark, DATENAME(month,tanggal) hari, sum(qty) jumlah from vwunion group by remark, DATENAME(month,tanggal))a
group by hari



:::: menentukan sisa quantity memakai HAVING
select DocNo,
sum (case when a.remark = 'I' then jumlah else 0 end)INCOMING ,
sum (case when a.remark = 'O' then jumlah else 0 end)OUTGOING,
(sum (case when a.remark = 'I' then jumlah else 0 end) - sum (case when a.remark = 'O' then jumlah else 0 end))SISA
from (select remark, docno, sum(qty)jumlah from vwunion group by docno,remark)a
group by DocNo
having (sum (case when a.remark = 'I' then jumlah else 0 end) - sum (case when a.remark = 'O' then jumlah else 0 end)) > '0.00'


::: untuk group berdasar row code and set to 0 if NULL
select hari as bulan,
sum (case when a.remark = 'I' then jumlah else 0 end)Incoming,
sum (case when a.remark = 'O' then jumlah else 0 end)Outgoing
from (SELECT remark, month(tanggal) hari, sum(qty) jumlah from vwunion group by remark, month(tanggal))a
group by hari


::: untuk group date berdasarkan bulan.
SELECT remark, month(tanggal) hari, sum(qty) jumlah from vwunion group by remark, month(tanggal)


:: updated tabel#1 dengan referensi tabel#2
update MITM_TBL SET MITM_STS = '1' from IN_TBL,mitm_tbl where IN_ITM = MITM_ITM and IN_DOCNO = 'IN-444444'

random in SQL server database

below script to random all row number:
select * from namatabel order by newid()


Query generates a random number between 1 and 10.

UPDATE @TT
SET DayAlloted = ABS(CHECKSUM(NEWID())) % 100 + 24

generate top 10 random field

SELECT TOP 10 RAND() AS [RandomNumber], [CustomerID], [CompanyName], [ContactName]
FROM [dbo].[Customers]

trigger di SQL Server

Trigger merupakan store procedure yang dijalankan secara automatis saat user melakukan modifikasi data pada tabel. Modifikasi data yang dilakukan pada tabel yaitu berupa perintah INSERT, UPDATE, dan DELETE.

INSERT , UPDATE dan DELETE bisa digabung jadi satu trigger yang dinamakan Multiple Trigger.

formatnya sbb:

CREATE TRIGGER nama_trigger

ON nama_tabel

FOR INSERT, UPDATE, DELETE

AS

isi statement-statement Anda disini.

GO

store procedure di SQL Server

beda store procedure dengan view adalah:
... view tidak bisa dipasang parameter ....
... Stored Procedure lebih flexible karena ada parameter didalamnya ...
view yang diberi parameter.. kita biasa menyebutnya function .. (yg mereturn table... )


CREATE PROC au_info @lastname varchar(40), @firstname varchar(20)
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id
JOIN titles ON titleauthor.title_id = titles.title_id
JOIN publishers ON titles.pub_id = publishers.pub_id
WHERE au_fname = @firstname
AND au_lname = @lastname
GO

untuk menjalankan store procedure diatas,
memakai script berikut di SQL Analyzer di database pubs:

EXECUTE au_info Ringer, Anne
GO

penggunaan View di SQL Server

View mirip dengan tabel, namun SQL Server tidak menyimpan data di dalamnya melainkan
definisi (query) suatu tabel. Untuk mudahnya, view adalah (seperti) "virtual tabel".

mengapa perlu memakai view, berikut beberapa alasannya:
- untuk membagi beban resource yang terpakai saat aplikasi di jalankan. Karena view dijalan/dieksekusi di sisi server. Maka resource di PC clients akan lebih hemat.
- untuk mempermudah dalam menampilkan data. Kadang struktur database yang kita buat
agak mempersulit kita dalam proses query untuk ditampilkan di report. Contoh report untuk crosstab/pivot tabel (pivot baru ada di SQL Server 2008).
- untuk mempermudah maintain aplikasi, apabila ada proses query yang sama dan berulang
maka akan lebih simple dalam proses pembuatan aplikasi.
- untuk mengimplementasikan security. User tidak bisa melihat colom colom tabel sebenarnya.
- kekurangannya: View tidak bisa menerima parameter sebagaimana Stored Procedured (SP).

View sangat membantu kita untuk merefleksikan field-field yang tidak ada di satu table tetapi ada di table lain, untuk itu kita bisa gunakan view ini dengan cara membuat relasi (join) antar tabletable tersebut. Tetapi yang perlu diperhatikan dalam pembuatan view ini adalah tentunya
keterkaitan antara table-table yang dipakai dalam view ini, semakin banyak relasi tentuanya akan semakin berat si SQL dalam melakukan suatu proses query dalam view tersebut, karena SQL akan melakukan proses loading semua data yang ada dalam table ke dalam view, baru kemudian SQL akan memfilternya, kalau kita lakukan query berdasarkan kriteria tertentu pada view tersebut.

contoh;
misal ingin membuat repot bentuk crosstab/ pivot tabel dengan crystall report 7 dan database SQL server2000. Pivot ada di SQL server 2008.

bila di SQL query analyzer, script berikut akan menjadi pivot:
SELECT OrderID,CustomerID,
(CASE ShipVia WHEN '1' THEN Freight ELSE 0 END) AS Q1,
(CASE ShipVia WHEN '2' THEN Freight ELSE 0 END) AS Q2,
(CASE ShipVia WHEN '3' THEN Freight ELSE 0 END) AS Q3
FROM orders

tapi bila ditampilkan ke crystal report 7 akan susah (kecuali versi 10 keatas) didesain.
sehingga memerlukan view untuk membuat virtual tabel yang nantinya mudah di design memakai crystal report 7.

berikut membuat viewnya:

create view order_view as
SELECT OrderID,CustomerID,
(CASE ShipVia WHEN '1' THEN Freight ELSE 0 END) AS Q1,
(CASE ShipVia WHEN '2' THEN Freight ELSE 0 END) AS Q2,
(CASE ShipVia WHEN '3' THEN Freight ELSE 0 END) AS Q3
FROM orders

shrink database Ms SQL Server

usually used for reduce log file size at Ms. SQL

below steps script for reduce NORTHWIND database from 80GB become to 40GB:
(1) . backup the Log
BACKUP LOG NORTHWIND WITH NO_LOG
(2). cek logical name log
sp_helpdb NORTHWIND
(3). shrink the logical log become to 1024kb
DBCC SHRINKFILE (NORTHWIND_Log,1)

Limit function at SQL Server

below Limit function for MySQL server:
select * from berita limit $posisi,$batas

and below for Ms SQL Server:

select top $batas * from wgrn_tbl where WGRN_ITMCD like '%$nama%' and WGRN_GRLNO not in ( select top $posisi WGRN_GRLNO from wgrn_tbl where WGRN_ITMCD like '%$nama%' order by WGRN_ITMCD,WGRN_RCVDT desc) order by WGRN_ITMCD,WGRN_RCVDT desc

Limit function used for e.g: at paging data for website

beda VARCHAR dan CHAR di SQL Server

Tipe data char adalah tipe data karakter yang panjangnya tetap (fixed-length). Ini artinya SQL Server akan mengalokasikan memori pada media penyimpanan untuk menyimpan tipe data ini sebesar ukuran maksimum yang kita minta. Contohnya deklarasi char(5) artinya SQL Server akan otomatis melakukan alokasi ukuran sebesar lima character pada media penyimpanan walaupun kita hanya mengisi data sebanyak tiga karakter misalnya.Tipe data varchar adalah tipe data karakter yang panjangnya tidak tetap (variable-length). Ini berarti SQL Server akan mengalokasikan memori pada media penyimpanan hanya sebesar atau sepanjang ukuran data aktual yang diisikan. Contohnya pada saat kita mendeklarasikan varchar(5) dan pada field tersebut kita isi 3 karakter maka pada media penyimpanan hanya akan dialokasikan sebesar 3 karakter saja.

Dari sini kita bisa menentukan kapan harus menggunakan tipe data char atau varchar. Tipe data char digunakan pada field atau data yang sifat panjangnya tetap, misalnya seperti kode barang, kode item, kode customer dan lain lain yang panjangnya tetap. Sebaliknya Anda bisa menggunakan varchar untuk data atau field yang sifat panjangnya tidak tetap misalnya nama, alamat, kota, deskripsi dan lain sebagainya. Jadi pilihlah tipe data yang tepat pada saat Anda membuat field-field pada database Anda.

Fatal error: Call to undefined function mssql_connect()

Got error messsage Fatal error: Call to undefined function mssql_connect() in C:\AppServ\www .. when perform PHP use MsSQL server 2005, and Appsrv. Whereas below code at php.in already unremark
extension=php_mssql.dll
;extension=php_msql.dll
extension=php_mysql.dll
extension=php_mysqli.dll
;extension=php_oci8.dll
and Apache services already restart, PC also.
and below dll files also keep at C:\AppServ\php5\ext:
- php_msql.dll and
- php_mssql.dll

please download ntwdblib.dll at http://webzila.com/?wz=dll#results then restore to folder:
C:\AppServ\Apache2.2\bin then restart the apache services

script dan storeprocedure di SQL Server

Berikut script dan storeprosedure beserta kegunaannya:
didokumentasikan karena sering lupa/ jarang pakai.
dijalaninn pake SQL Analyser atau lainnya.

koneksi ke sesama SQL Server
EXEC sp_addlinkedserver ‘[Nama Server Tujuan]‘,N’SQL Server’

Versi dari SQL Server;
select @@version
pernah kejadian salah install personal edition untuk Live database production. gara-gara tidak cek dulu. Diomelin semua di orang 4 negara (Japan, Hongkong, Malaysia dan Indonesia sendiri). Tapi jalan lo untuk 50 user, cuma lambat aje.

Apabila server tujuan tidak menggunakan user ’sa’, maka perlu di set user n passwordnya.dengan menjalankan query berikut.
EXEC sp_addlinkedsrvlogin ‘[Server Name]‘, ‘false’, NULL, ‘[SQLUser]‘, ‘[Password]‘

attach database
EXEC sp_attach_db @dbname = 'KARYA_KITA',
@filename1 = 'D:\mssql\karya_kita.mdf',
@filename2 = 'D:\mssql\karya_kitalog.ldf'
cara ini lebih mudah ketika kita ingin nama database nya kita rubah namanya.
lebih mudah daripada memakai cara di SQL server management studio.


drop database KARYA_KITA
USE MASTER
GO
DROP DATABASE KARYA_KITA
-->hati hati karena file database akan terhapus juga.
kalo ingin database nya tetap keep di folder,
pakai detach dari SQL server management studio

:::liat semua nama tabel di satu database:::.
USE YourDBName
GO
SELECT *
FROM sys.Tables
GO

:::liat semua nama database di satu server:::.
use master
select * from sysdatabases

:::liat semua nama tabel di satu server:::.
sp_msforeachdb @command1='USE ?;SELECT * FROM sys.Tables'


:::liat semua tabel di satu database:::.
select * from sysobjects where type='u'

:::liat semua store procedure di satu database:::.
select * from sysobjects where type='p'

:::insert data ke suatu tabel:::.
INSERT INTO nama_tabel (kolom1, kolom2..)
VALUES (nilai1, nilai2...)
contoh:
insert into pegawai (nopeg,nama,jabatan,gaji)
values ('011','rina','akuntan','5000000');


:::updated/ ubah data:::.
UPDATE nama_tabel
SET kolom1=nilai1
[,kolom2=nilai2]..
WHERE kondisi
contoh:
update pegawai set jabatan='direktur'
where nama='putri'

:::delete / hapus data:::.
DELETE FROM nama_tabel
WHERE kondisi
contoh:
delete from pegawai where nama = 'rina';

:::create / bikin tabel baru dan bikin primary key :::.
contoh: di Oracle spt ini
create table tb_dummy
(
Sr varchar2(3),
PayMonth varchar2(6),
Badge_No varchar2(6),
Nama varchar2(50),
Dept varchar2(10
constraint dummy_pk primary key (paymonth , badge_no )) ;







Crosstab query di SQL 2005

dulu untuk bikin crosstab Pivot tabel pakai cara berikut:

SELECT Bulan,
SUM(CASE Sales WHEN 'Agus' THEN Jumlah ELSE 0 END) AS Agus,
SUM(CASE Sales WHEN 'Dani' THEN Jumlah ELSE 0 END) AS Dani,
SUM(CASE Sales WHEN 'Fahmi' THEN Jumlah ELSE 0 END) AS Fahmi,
SUM(CASE Sales WHEN 'Firstman' THEN Jumlah ELSE 0 END) AS Firstman,
SUM(CASE Sales WHEN 'Rully' THEN Jumlah ELSE 0 END) AS Rully,
SUM(Jumlah) as [Y Total]
FROM tblSamplePivot
GROUP BY Bulan
UNION
SELECT 'X Total' AS Bulan,
SUM(CASE Sales WHEN 'Agus' THEN Jumlah ELSE 0 END) AS Agus,
SUM(CASE Sales WHEN 'Dani' THEN Jumlah ELSE 0 END) AS Dani,
SUM(CASE Sales WHEN 'Fahmi' THEN Jumlah ELSE 0 END) AS Fahmi,
SUM(CASE Sales WHEN 'Firstman' THEN Jumlah ELSE 0 END) AS Firstman,
SUM(CASE Sales WHEN 'Rully' THEN Jumlah ELSE 0 END) AS Rully,
SUM(Jumlah) as [Y Total]
FROM tblSamplePivot

sekarang di SQL 2005 pake cara berikut bisa:

SELECT Bulan,
ISNULL([Agus],0) AS Agus,
ISNULL([Dani], 0) AS Dani,
ISNULL([Fahmi], 0) AS Fahmi,
ISNULL([Firstman], 0) AS Firstman,
ISNULL([Rully], 0) AS Rully,
[X Total]
FROM(
SELECT Bulan, Sales, ISNULL(Jumlah, 0) AS Jumlah FROM tblSamplePivot
UNION
--Retrieve Data untuk Total dari tiap bulan
SELECT Bulan, 'X Total' as Sales, SUM(Jumlah) AS Jumlah FROM tblSamplePivot GROUP BY Bulan
UNION
--Retrieve Data untuk Total dari tiap Sales
SELECT 'Y Total' AS Bulan, Sales, SUM(Jumlah) AS Jumlah FROM tblSamplePivot GROUP BY Sales
UNION
--Retrieve Data untuk Total Keseluruhan
SELECT 'Y Total' AS Bulan, 'X Total' AS Sales, SUM(Jumlah) AS Jumlah FROM tblSamplePivot
) AS PS
PIVOT
(
SUM(Jumlah) FOR Sales IN(Agus, Dani, Fahmi, Firstman, Rully, [X Total])
) P

diambil dari : http://waroengvb.net/crosstab-query-menggunakan-pivot-pada-sql-server/

Launching Windows 7, Exchange Server 2010 & Windows Server 2008 R2 Overview and Demo

join acara launching Windows 7, Exchange Server 2010 & Windows Server 2008 R2 Overview and Demo di JCC. Lumayan

PB connection to Ms SQL 2005

sometime we got problem when connected PB application to Ms. SQL Server 2005. Please use below some example that you can use at file ini connection:


[Absensi]
DBMS=MSS Microsoft SQL Server
Database=dbname
UserId=sa
DatabasePassword =xxxxxxxx
LogId=
LogPassword=
ServerName=192.168.100.5,1433
//DbParm=CommitOnDisconnect='No'

or

[Absensi]
DBMS=MSS Microsoft SQL Server
Database=dbname
UserId= sa
DatabasePassword = xxxxxxxxxx
LogId=
LogPassword=
ServerName= XYZ
//DbParm=CommitOnDisconnect='No'

or

[Absensi]
DBMS=MSS Microsoft SQL Server
Database=dbname
UserId=sa
DatabasePassword =xxxxxxxxxxxxx
LogId=
LogPassword=
ServerName=XYZSQL\XYZ_SQL
//DbParm=CommitOnDisconnect='No'

or

you can use ODBC-DNS connection,
please add sql server driver.

same above setting if you set dbrofile at PB software