USE GrpInsurance

SELECT
	*
FROM
	dbo.custport_provider
	
--SELECT [id_provider]
--      ,[no_provider]
--      ,[provider_name]
--      ,[address]
--      ,[city]
--      ,[province]
--      ,[country]
--      ,[op]
--      ,[ip]
--      ,[phone]
--      ,[notes]
--      ,[local_provider]
--      ,[is_aai]
--      ,[is_admedika]
--      ,[is_eli]
--      ,[status]
--      ,[create_by]
--      ,[create_date]
--      ,[change_by]
--      ,[change_date]
--  FROM [GrpInsurance].[dbo].[custport_provider]
--WHERE
--  is_aai = 0
--DELETE FROM [GrpInsurance].[dbo].[custport_provider] WHERE is_aai = 0
--INSERT INTO dbo.custport_provider
--        (no_provider,
--         provider_name,
--         address,
--         city,
--         province,
--         country,
--         op,
--         ip,
--         phone,
--         notes,
--         local_provider,
--         is_aai,
--         is_admedika,
--         is_eli,
--         status,
--         create_by,
--         create_date,
--         change_by,
--         change_date)
--SELECT
--	NULL AS no_provider,
--	p.Nama,
--	p.Alamat,
--	p.Kota,
--	w.Nama AS province,
--	'Indonesia' AS country,
--	0,
--	0,
--	'+'+Telp1_kode_negara+' '+RIGHT(Telp1_kode_area,LEN(Telp1_kode_area)-1)+' '+Telp1_nomor AS phone,
--	NULL AS notes,
--	1 AS local_provider,
--	0, 
--	COALESCE(p.Provider_Adm,0),
--	1,
--	'I',
--	'SYS',
--	GETDATE(),
--	NULL,
--	NULL
--FROM
--	dbo.Provider AS p LEFT JOIN
--	dbo.Wilayah AS w ON p.Kode_Wilayah = w.Kode
--WHERE
--  p.Status = 'I'

--SELECT
--  '2016.'+CAST(id_provider AS VARCHAR(6)),
--	*
--FROM
--	dbo.custport_provider
--WHERE
--  no_provider IS NULL

--UPDATE
--	dbo.custport_provider
--SET
--  no_provider = '2016.'+CAST(id_provider AS VARCHAR(6))
--WHERE
--  no_provider IS NULL

SELECT DISTINCT city FROM	dbo.custport_provider WHERE is_aai=0 ORDER BY city

--UPDATE dbo.custport_provider WHERE province='Bekasi'
--DELETE FROM dbo.custport_provider WHERE id_provider IN (3549)
SELECT LEFT(province, 1)+ lower(RIGHT(province, len(province)-1) ), * FROM	dbo.custport_provider WHERE is_aai = 0 AND province IS NOT NULL
SELECT DISTINCT province FROM	dbo.custport_provider WHERE is_aai = 1 AND province IS NOT NULL AND province NOT LIKE '% %'
SELECT DISTINCT province FROM	dbo.custport_provider WHERE is_aai = 0 AND province IS NOT NULL AND province NOT LIKE '% %'
--UPDATE dbo.custport_provider SET province=LEFT(province, 1)+ lower(RIGHT(province, len(province)-1) ) WHERE is_aai = 0 AND province IS NOT NULL AND province NOT LIKE '% %'
--UPDATE dbo.custport_provider SET province='Jawa Tengah' WHERE is_aai = 0 AND id_provider=4510
SELECT * FROM	dbo.custport_provider WHERE province='I'
SELECT * FROM	dbo.Provider WHERE Nama LIKE '%klaten%'
SELECT * FROM	dbo.Wilayah WHERE Kode='KA'
SELECT * FROM	dbo.Provider
