SQL

Cobalah membaca skrip saya dengan cepat.
Kecepatan membuat sketsa untuk mempermudah pemahaman dimasa yang akan datang
Itulah tujuan Query saya..

Menambah kolom table pada posisi tertentu..

tool->option->designers->table and database designers->hilangkan/checklist prevent saving changes that require table re-creation

Referensi :

https://stackoverflow.com/questions/6810425/sql-server-saving-changes-is-not-permitted-error-prevent-saving-changes-that

Jika tidak ada SQL SERVER 2008 nya
Silahkan Download terlebih dahulu  di Menu Download yaa..

Langkat – langkahnya adalah sebagai berikut :
1.aktifkan mysql_d nya melalui start/program/phptriad/mysql/mysql_d
2.start/run
3.dirun ketik cmd
4.cd \
5.cd apache\mysql\bin
6.mysql
7.show databases;//untuk melihat semua database yag telah dibuat
8.create database stmik;//untuk membuat database stmik
9.use stmik;//mengedit database stmik
10.create table mahasiswa(
-nim varchar(10),
-nama varchar(50),
-jurusan varchar(30),
-angkatan varchar(4));//untuk membuat table mahasiswa dengan 4 field/kolom
-insert into mahasiswa values(‘0711482′,’efori’,’TI’,’2007′);//untuk mengisi table
-select *from mahasiswa;//untuk melihat isi table
-delete from mahasiswa where nip=’0711482′; //menghapus isi tabel dengan nip=0711482
-upade mahasiswa set nama=’efori buulolo’ where nip=’0711482′;//mengedit nama
-desc mahasiswa;//untuk melihat gambaran table
-show tables;//untuk melihat table dalam satu database;
insert into barang (no,nama) values ('1','sance');
Contoh saya:
use sance;
select * from mahasiswa;
create table mahasiswa1(no int primary key,nama varchar(20) );
insert into mahasiswa1 values('1','sance');
insert into mahasiswa1 values('2','andi');
select * from mahasiswa1 where no='2';
delete from mahasiswa1 where nama='andi';
update mahasiswa1 set nama ='sance2' where no='2';
select * from mahasiswa1;
================================

awal untuk menggabungkan dua data

select tmp.Date,sum(tmp.conversion)

from (

Select

CAST(SB.CreateDate as date) as [Date],

ISNULL(SB.TrafficSource,'') as Marketing_Partner,

ISNULL(SB.PaymentSource,'') as Carrier,

COUNT(*) as Conversion

From [dbo].[Subscription] SB with(nolock)

left join [dbo].[SubscriberTransaction] SBT 

ON SB.ID = SBT.SubscriptionID

Where SB.PortalID = 462

And SBT.PortalID = 462

And CAST(SB.CreateDate as date) > '2021-09-01'

And CAST(SB.CreateDate as date) < '2021-09-10'

Group By 

CAST(SB.CreateDate as date),

SB.TrafficSource,

SB.PaymentSource

-----------------------------

Union all

-----------------------------

Select 

CAST(SBD.DeleteDate as date) as [Date],

ISNULL(SBD.TrafficSource,'') as Marketing_Partner,

ISNULL(SBD.PaymentSource,'') as Carrier,

COUNT(*) as Conversion

From [dbo].[SubscriptionDeleted] SBD with(nolock)

left join [dbo].[SubscriberTransaction] SBT 

ON SBD.SubscriptionID = SBT.SubscriptionID

Where SBD.PortalID = 439

And SBT.PortalID = 439

And CAST(SBD.DeleteDate as date) > '2020-09-01'

And CAST(SBD.DeleteDate as date) < '2020-09-10'

Group By 

CAST(SBD.DeleteDate as date),

SBD.TrafficSource,

SBD.PaymentSource

)as tmp

group by tmp.Date

order by tmp.Date desc

akhir untuk menggabungkan dua data

================================

-- carrir = payment source 

select top 1 id from [dbo].[Subscription] with(nolock)

Union

Select top 1 id from [dbo].[SubscriberTransaction] with(nolock)

--Subcription

Select

CAST(SB.CreateDate as date) as [Date],

ISNULL(SB.TrafficSource,'') as Marketing_Partner,

ISNULL(SB.PaymentSource,'') as Carrier,

COUNT(*) as Conversion

From [dbo].[Subscription] SB with(nolock)

left join [dbo].[SubscriberTransaction] SBT 

ON SB.ID = SBT.SubscriptionID

Where SB.PortalID = 462

And SBT.PortalID = 462

And CAST(SB.CreateDate as date) > '2021-09-01'

And CAST(SB.CreateDate as date) < '2021-09-30'

Group By 

CAST(SB.CreateDate as date),

SB.TrafficSource,

SB.PaymentSource

Order By 

SB.TrafficSource,

SB.PaymentSource asc

-----------------------------

--select top 1 * from [dbo].[SubscriptionDeleted] with(nolock)

--where SubscriptionID= 17316133 and PortalID = 439

--select top 1 * from [dbo].[SubscriberTransaction] with(nolock)

--where SubscriptionID= 17316133 and PortalID = 439

--Union

Select

CAST(SBD.DeleteDate as date) as [Date],

ISNULL(SBD.TrafficSource,'') as Marketing_Partner,

ISNULL(SBD.PaymentSource,'') as Carrier,

COUNT(*) as Conversion

From [dbo].[SubscriptionDeleted] SBD with(nolock)

left join [dbo].[SubscriberTransaction] SBT 

ON SBD.SubscriptionID = SBT.SubscriptionID

Where SBD.PortalID = 439

And SBT.PortalID = 439

And CAST(SBD.DeleteDate as date) > '2020-06-01'

And CAST(SBD.DeleteDate as date) < '2020-06-30'

Group By 

CAST(SBD.DeleteDate as date),

SBD.TrafficSource,

SBD.PaymentSource

Order By 

SBD.TrafficSource,

SBD.PaymentSource asc

=================================

Trafic

use [8ePing]

Declare @ustadKu int;

Declare @date varchar(50);

Select @ustadKu = 462;

Set @date = '2022-01-07'

Select  

  CAST (CreateDate as Date) as [Date],

(CASE WHEN ISNULL(TrafficSource,'') = '' then 'ORGANIC' else TrafficSource end) as Marketing_Partner,

(CASE WHEN ISNULL(Campaign,'') ='' then 'ORGANIC'  else Campaign end) as Campaign_Name,

(CASE WHEN ISNULL(LandingPage,'') ='' then 'ORGANIC' else LandingPage end)LandingPage,

COUNT(*) as  Total_Klik

from [dbo].[ActionTracking](NOLOCK)

where PortalId = (@ustadKu) 

and CAST(CreateDate as Date) = (@date)

group by Cast(CreateDate as date) ,TrafficSource, Campaign,LandingPage

order by TrafficSource,Campaign,LandingPage asc

================================

use [8ePing]

--Declare @showData int;

Declare @ustadKu int;

Declare @date varchar(50);

--Declare @count varchar(50);

--Basic Setting

Select @ustadKu = 462;

---Select @showData = 100;

Set @date = '2022-01-07'

--Select @count = 'TrafficSource'

Select  

--top (@showData) 

CAST (CreateDate as Date) as [Date],

(CASE WHEN ISNULL(TrafficSource,'') = '' then 'ORGANIC' else TrafficSource end) as Marketing_Partner,

(CASE WHEN ISNULL(Campaign,'') ='' then 'ORGANIC'  else Campaign end) as Campaign_Name,

(CASE WHEN ISNULL(LandingPage,'') ='' then 'ORGANIC' else LandingPage end)LandingPage,

COUNT(*) as  Total_Klik

from [dbo].[ActionTracking](NOLOCK)

where PortalId = (@ustadKu) 

and CAST(CreateDate as Date) = (@date)

group by Cast(CreateDate as date) ,TrafficSource, Campaign,LandingPage

order by TrafficSource,Campaign,LandingPage asc

=================================

use [8ePing]

Declare @showData int;

Declare @ustadKu int;

Declare @date varchar(50);

Declare @count varchar(50);

--Basic Setting

Select @ustadKu = 462;

Select @showData = 700;

Select @date = '2021-12-29'

Select @count = 'Campaign'

Select  top (@showData) CreateDate as [Date],TrafficSource as Marketing_Partner,

(case when Campaign = null or Campaign ='' then 'ORGANIC'  else Campaign end) as Campaign_Name,LandingPage,

COUNT((@count)) as  Total_Klik

from [dbo].[ActionTracking]

where PortalId = (@ustadKu) and CAST(CreateDate as Date) = (@date)

group by CreateDate ,TrafficSource, Campaign,LandingPage

===================================
create database sance2;
create database sance3;
use sance2;
create table mahasiswa1(no int primary key,nama varchar(20));
create table mahasiswa2(no int primary key,nama varchar(20));
create table mahasiswa3(no int primary key,nama varchar(20));
select * from mahasiswa1;
insert into mahasiswa1 values('1','sance');
insert into mahasiswa1 values('2','andi');
insert into mahasiswa1 values ('3','babakan wanajaya');

insert into mahasiswa1 values ('4','balabala');
insert into mahasiswa1 values ('5','bk');
select * from mahasiswa1 where no='1';
update mahasiswa1 set nama='abdurrahman sance' where no='1';
select * from mahasiswa1 where no='2';
update mahasiswa1 set nama='abdurrahman assidiq' where no='2';
select * from mahasiswa1 where no='3';
update mahasiswa1 set nama='abdurahman' where no='3';
select * from mahasiswa1 where no='4';
update mahasiswa1 set nama='abrurahmana' where no='4';
select * from mahasiswa1;
delete from mahasiswa1 where no='5';
delete from mahasiswa1 where no='4';
delete from mahasiswa1 where no='3';
delete from mahasiswa1 where no='2';
delete from mahasiswa1 where no='1';
alter table mahasiswa add jumlah_barang2 integer;
untuk melihat database yang disimpan  :
Clik kann database kemudian properties -> file

Lihat https://efori.wordpress.com/2011/02/25/cara-membuat-database-di-mysql/
http://achulblog.blogspot.co.id/2012/05/cara-insert-update-delete-database-sql.html
alter table mahasiswa add bebelak varchar(20);
alter table mahasiswa add belakqang int;

select * from ItemLedgerEntry
select SUM (Quantity) from ItemLedgerEntry
select CustomerCode,SUM (Quantity) from ItemLedgerEntry Where Postingdate <= '2015-05-07' group by CustomerCode
select * from EntryType
select * from item;
select CustomerCode,Postingdate,SUM (Quantity) from ItemLedgerEntry where postingdate <= '2015-05-07'
group by CustomerCode,PostingDate;

select * from items where ItemCode='J111300002'
select * from item where ItemCode='J111300002'
select * from ItemZonePrice where ItemCode='J111300002'
select * from ItemLedgerEntry where EntryType in('7','15')
select * from ItemLedgerEntry where EntryType between 7 and 15
select * from EntryType left join ItemLedgerEntry on
EntryType.EntryID=ItemLedgerEntry.EntryType
select EntryType,CustomerCode  from ItemLedgerEntry
select SUM(Quantity) from ItemLedgerEntry
select CustomerCode,Createdby,SUM(EntryType) from ItemLedgerEntry where
PostingDate <='2016-02-26' group by CustomerCode,Createdby
select CustomerCode from ItemLedgerEntry


<?php
$host="localhost";
$user="root";
$password="";
$db="dt";
$koneksi=mysql_connect($host,$user,$password);
mysql_select_db($db,$koneksi);

$sql="select * from siswa";
$tampil=mysql_query($sql);
while($data=mysql_fetch_array($tampil))
{
echo "<p>Nama  :</p>";
}

?>

select * from DeliveryOrder
select * from DeliveryOrderDetail
select * from ItemLedgerEntry

select * from PurchaseReceipt
select * from PurchaseReceiptDetail
select * from ItemLedgerEntry


DOSM-CK-GCVV00-25
select * from DeliveryOrder where DocumentNO='DOSM-CK-GCVV00-25'
select * from DeliveryOrderDetail where DeliveryOrderTransNo='DO-CK-GCVV00-28'
select itemCode,SUM(Quantity) from DeliveryOrder
inner join DeliveryOrderDetail
on  DeliveryOrder.TransactionNo=DeliveryOrderDetail.DeliveryOrderTransNo
where DocumentNo='DOSM-CK-GCVV00-25'
Group By ItemCode


select * from ItemLedgerEntry where DocumentNo='DOSM-CK-GCVV00-25'
select SUM(Quantity) from ItemLedgerEntry where DocumentNo='DOSM-CK-GCVV00-25'

select SUM(Quantity) from DeliveryOrderDetail  where DocumentNo='DOSM-CK-GCVV00-25'

Select COUNT(TransactionNo) From ItemLedgerEntry
Where Quantity = 1




select * from EntryType
select * from ItemLedgerEntry where EntryType ='7'

89322
select Count(EntryType) from ItemLedgerEntry where EntryType='7'
select SUM(EntryType) from ItemLedgerEntry where EntryType='7'


DeliveryOrder => pengiriman barang
Jumlah Quantity ,percustomer,sampai ndg tanggal 31 Desedmber

select * from DeliveryOrder where PostingDate >='2014-06-25'

select CustomerCode ,SUM(Quantity) from ItemLedgerEntry where entrytype ='15' and postingdate <='2016-12-31'group by customercode
select * from ItemLedgerEntry
===================
1.
-pegiriman barng DeleveryOrder EI =1
-Quantiti percustomer (group by CustmerCode)
-sampy 31 des 2016
============================================
select CustomerCode,SUM(Quantity) from ItemLedgerEntry where
EntryType='1' and PostingDate <='2016-12-31' group by CustomerCode
select * from EntryType
select * from ItemLedgerEntry where Postingdate='2016-12-31'


=======================
---no1.done
select CustomerCode,SUM(Quantity) from ItemLedgerEntry where
EntryType='1' and PostingDate <='2016-12-31' group by CustomerCode
select * from DeliveryOrder
======================
--No2.
--group by Customer jmlah Quantity dari 2015
select * from Entrytype
DeliveryReceipt EI=2
select * from ItemLedgerEntry where ItemCode='ZK200'
select * from deliveryOrderdetail where ItemCode='ZK200'
select * from ItemLedgerEntry
--done
select CustomerCode,SUM(Quantity) From ItemLedgerEntry where
EntryType='2' and ItemCode='ZK200' and PostingDate >='2015-01-01'group by CustomerCode

===============================

--no 3
--SELECT TOP 5 NmTable.Kd_Brg,Sum(NmTable.Qty) as Tot_Qty FROM NmTable group by Kd_Brg order by Tot_Qty
SELECT TOP 5 NmTable.Kd_Brg,Sum(NmTable.Qty) as Tot_Qty FROM NmTable
group by Kd_Brg order by Tot_Qty
select top 5
select * from salesorderheader where TransDate='2014-06-21'

SELECT barang.`nama_barang`,SUM(jumlah) AS 'total_terjual'
FROM detail_trans_jual,barang
WHERE detail_trans_jual.`kode_barang`=barang.`kode_barang`
GROUP BY (nama_barang)
ORDER BY (total_terjual) DESC LIMIT 5;


SELECT CustomerCode, COUNT(CustomerCode) AS GrandTotal
From salesorderheader
INNER JOIN fromCustomerCode ON transaksi_detail.'Kd_barang'=catalog.'Kd_barang'
GROUP BY Kd_barang
ORDER BY total_terjual DESC LIMIT 5
select FromCustomerCode,SUM(TotalReceivable) as 'TotalReceivable' from SalesOrderHeader group by fromCustomerCode
order by (TotalReceivable) DESC LIMIT 5;

==============================
--pengiriman barang = Delivery order =>buat table pengirimn
--jumlah quantity   = DeliveryOrderDetail
--dikirim percustomer = DeliveryOrder
--sampai tanggal 31 Des 2016 <  ===DeliveryOrder

select * from DeliveryOrderDetail
select FromCustomerCode,Sum(Quantity) from DeliveryOrder inner join DeliveryOrderdetail
on DeliveryOrder.TransactionNo=DeliveryOrderDetail.TransactionNo where PostingDate <= '2016-12-31'
group by FromCustomerCode
=====
--1
select FromCustomerCode,SUM(Quantity) from DeliveryOrder
inner join DeliveryOrderDetail
on  DeliveryOrder.TransactionNo=DeliveryOrderDetail.DeliveryOrderTransNo
where PostingDate < '2017-01-01'
Group By FromCustomerCode
--1 done
----
ve
select * from Deliveryreceipt where TransactionNo='DOR-BF-K-VVBM-1'
select * from DeliveryReceiptDetail where ItemCode='ZK200'
select FromCustomerCode,Sum(Quantity) from DeliveryReceipt inner join
DeliveryReceiptDetail on DeliveryReceipt.TransactionNo=DeliveryReceiptDetail.DeliveryReceiptTransNo
where ItemCode='ZK200' and PostingDate between '2014-12-31' and '2016-1-1'
 group by FromCustomerCode
--no 2.

--penerima barang--
--ZK200
--Percustomer--
--Quantity--
-- > dari tahun 2015

select * from PurchaseReceipt where TransactionNo='LPB-BF-GVV00-1'
select * from PurchaseReceiptDetail where PurchaseReceiptTransNo='LPB-BF-GVV00-1'
select * from ItemLedgerEntry

select CustomerCode,SUM(Quantity) from PurchaseReceipt inner join
PurchaseReceiptDetail on purchaseReceipt.TransactionNo=PurchaseReceiptDetail.PurchaseReceiptTransNo
where ItemCode='ZK200' and PostingDate between '2014-12-31' and '2016-1-1'
group by CustomerCode
--done no 2
======================
--no 3
select * from SalesOrderHeader Where TransactionNo='20140620-K-VVMM3-00001' --penjualan
select * from SalesOrderDetail where itemCode='PN-SS'

select * from SalesInvoiceHeader where TransactionNo='2014-06-21 00:00:00.000'
select * from SalesReturnHeader
---barang nama  detail
--quantity   detail
-- tahun 2016  header

===================
--No 3
SELECT top 5 ItemCode, SUM(Quantity) AS total
From SalesOrderHeader
INNER JOIN SalesOrderDetail ON SalesOrderHeader.TransactionNo=SalesOrderDetail.SalesOrderTransNo
where PostingDate between '2015-12-31' and '2017-1-1'
GROUP BY itemCode
ORDER BY total DESC


select * from EntryType


select * from ItemLedgerEntry
select SUM(Quantity) from ItemLedgerEntry

select CustomerCode, SUM(Quantity) from itemledgerentry where PostingDate <= '2016-12-31'
Group By CustomerCode


select CustomerCode, PostingDate, SUM(Quantity) from itemledgerentry where PostingDate <= '2016-12-31'
Group By CustomerCode, PostingDate

select CustomerCode ,SUM(Quantity) from ItemLedgerEntry where entrytype ='15' and postingdate <='2016-12-31'group by customercode

select customercode,sum (quantity) from itemledgerentry where

--1
select FromCustomerCode,SUM(Quantity) from DeliveryOrder
inner join DeliveryOrderDetail
on  DeliveryOrder.TransactionNo=DeliveryOrderDetail.DeliveryOrderTransNo
where PostingDate < '2017-01-01'
Group By FromCustomerCode
--1 done

--2

select FromCustomerCode,Sum(Quantity) from DeliveryReceipt inner join
DeliveryReceiptDetail on DeliveryReceipt.TransactionNo=DeliveryReceiptDetail.DeliveryReceiptTransNo
where ItemCode='ZK200' and PostingDate between '2015-01-01' and '2016-01-01'
 group by FromCustomerCode
---3

SELECT top 5 ItemCode, SUM(Quantity) AS total
From SalesOrderHeader
INNER JOIN SalesOrderDetail ON SalesOrderHeader.TransactionNo=SalesOrderDetail.SalesOrderTransNo
where PostingDate between '2016-01-01' and '2017-1-1'
GROUP BY itemCode
ORDER BY total DESC



select * from Items where ItemCode = 'J111300002'
select * from ItemByLocation Where ItemID = '95073'
==>cek unit price

xxxxx
select * from Item Where ItemCode = 'J111300002'
select * from SpecialItem where ItemID = '95073'


--1
select FromCustomerCode,SUM(Quantity) from DeliveryOrder
inner join DeliveryOrderDetail
on  DeliveryOrder.TransactionNo=DeliveryOrderDetail.DeliveryOrderTransNo
where PostingDate < '2017-01-01'
Group By FromCustomerCode
--1 done

--2

select FromCustomerCode,Sum(Quantity) from DeliveryReceipt inner join
DeliveryReceiptDetail on DeliveryReceipt.TransactionNo=DeliveryReceiptDetail.DeliveryReceiptTransNo
where ItemCode='ZK200' and PostingDate between '2015-01-01' and '2016-01-01'
 group by FromCustomerCode
---3

SELECT top 5 ItemCode, SUM(Quantity) AS total
From SalesOrderHeader
INNER JOIN SalesOrderDetail ON SalesOrderHeader.TransactionNo=SalesOrderDetail.SalesOrderTransNo
where PostingDate between '2016-01-01' and '2017-1-1'
GROUP BY itemCode
ORDER BY total DESC






For Sql
A
AA
AAA
AAAA
AAAAA
AAAA
AAA
AA
A

Declare @A Varchar(8000)
Declare @B INT
Declare @C INT
Declare @D Varchar(8000)
Declare @F INT
Set @A = ''
Set @B = 1
set @C = 9
set @D = ''
set @F = 1
While(@B <= 10)
   Begin
  set @D = @D + 'A'
  Select @D
  Set @B = @B + 1
   End

While(@F <= 10)
while(@C >=1)
Begin
      Set @A = @A + @D    
select substring(@A,1,@C)
Set @F = @F + 1
        Set @C = @C -1
End
=======================================

A
AA
AAA
AAAA
AAAAA
AAAAAA

Declare @A Varchar(8000)
Declare @B INT
Set @A = ''
Set @B = 1

While(@B <= 10)
Begin
Set @A = @A + 'A'
select @A
Set @B = @B + 1
End

============================================
A
AA
AAA
AA
A

Declare @B INT
Declare @D Varchar(8000)
Set @B = 1
set @D = ''
--@B 1+9=10
While(@B <= 9)
   Begin
  set @D = @D + 'A'
  Select @D
  Set @B = @B + 1
   End

--D=10A-A=9A
while(@B >=1)
Begin
      Set @D = @D + 'A'  
select substring(@D,1,@B)
Set @B = @B -1
End
====================================
Nambahin Dan Nilai 0


select Nama,isnull(sum(jumlah),0) from dbtess
group by Nama
Order by sum(jumlah)
========

select B.CustomerCode,ISNULL(SUM(C.Quantity),0) as Quantity From AdjustmentHeader A
left join Customer B   ON B.CustomerCode=A.CustomerCode
left join AdjustmentDetail C ON C.AdjustmentTransactionNo=A.TransactionNo
Where A.FinaReleasedDate between '2016-12-01' and '2017-01-01'
Group by B.CustomerCode,A.FinareleasedDate
Order by ISNULL(SUM(C.Quantity),0) Desc

====================================
8 january 2018

create database dbSance
use dbSance
create table tbLogin(
id int primary key identity(1,1),
name varchar (50),
password varchar(50),
status smallint,
)

insert into tbLogin(name,password,status)values('sance','123456',1)
insert into tbLogin(name,password,status)values('shin','123456',2)
insert into tbLogin(name,password,status)values('mila','123456',2)
insert into tbLogin(name,password,status)values('mili','123456',1)

Select * from tbLogin
Select * from tbLogin
order by id Desc

set identity_insert tbLogin on//mengaktifkan untk menmbah
insert into tbLogin(id,name,password,status)values(7,'mili','123456',1)
set Identity_insert tbLogin off//menonaktifkan untuk menambah

insert into tbLogin(id,name,password,status)values(6,'mili','123456',1)

====================================
17 January 2018

--merename column
sp_rename 'tbInsert.[id]','id_name','column'
--menambah column
alter table tbInsert add ket varchar(50)
--merubah type data column
alter table tbInsert alter column ket int;
--menghapus colum
alter table tbInsert Drop column ket

--mereset nilai identity/mengembalikan nilai identity ke 1

DBCC CHECKIDENT('tb_data', RESEED, 0)

====================================
New
=> insert into mahasiswa2
Select nama,alamat,GETDATE() as date from mahasiswa
where id between 1 and 5

blum dipindahin ke blog satunya

====================================


blog datanya terkonkesi dengan
http://phpsance.blogspot.com/2018/01/kumpulan-query.html



QUERY

1.DB null ganti jam dan terlanjur end balance

Author : Sance

Share this

Related Posts