1. Hello Guest, selamat datang di Forum WinPoin. Kamu bisa bertanya, berdiskusi, sharing, dan ngobrolin apapun seputar Windows, Windows Phone, PC, Gadget, atau hal seputar Teknologi lainnya. Selamat berkomunitas! ;)

TSQL Script - Bisa lebih Simple Gak ya?

Discussion in 'Developer Corner' started by mabaega, Jun 12, 2014.

  1. mabaega

    mabaega Well-Known Member

    Joined:
    Nov 9, 2013
    Messages:
    2,735
    Table

    Code:
    USE [QC_PILPRES_2014]
GO

/****** Object:  Table [dbo].[TB_LOKASI]    Script Date: 12/06/2014 23:37:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TB_LOKASI](
	[KODE_LOKASI] [nvarchar](8) NOT NULL,
	[NAMA_LOKASI] [nvarchar](40) NULL,
	[JML_TPS] [int] NULL,
	[DPT_L] [int] NULL,
	[DPT_P] [int] NULL,
	[TBH_L] [int] NULL,
	[TBH_P] [int] NULL,
	[SAH_1] [bigint] NULL,
	[SAH_2] [bigint] NULL,
	[SMS_REG] [nvarchar](14) NULL,
	[CONTACT] [nvarchar](30) NULL,
 CONSTRAINT [PK_TB_LOKASI] PRIMARY KEY CLUSTERED 
(
	[KODE_LOKASI] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
    Data yang ada hanya data TPS, Tinggal Script Buat Rekap Group...
    Otak-atik, gak ada Ide buat optimasi script.......

    Code:
    USE [QC_PILPRES_2014]
GO
MERGE INTO [dbo].[TB_LOKASI]
   USING (SELECT LEFT([KODE_LOKASI],6) AS [KODE_LOKASI]
      ,SUM([JML_TPS]) AS [JML_TPS]
      ,SUM([DPT_L]) AS [DPT_L]
      ,SUM([DPT_P]) AS [DPT_P]
      ,SUM([TBH_L]) AS [TBH_L]
      ,SUM([TBH_P]) AS [TBH_P]
      ,SUM([SAH_1]) AS [SAH_1]
      ,SUM([SAH_2]) AS [SAH_2]
  FROM [dbo].[TB_LOKASI]
  WHERE LEN([KoDe_LOKASI])=8
  GROUP BY LEFT([KODE_LOKASI],6)) AS TMP_TABLE
      ON [dbo].[TB_LOKASI].[KODE_LOKASI] = TMP_TABLE.[KODE_LOKASI]
WHEN MATCHED THEN
   UPDATE 
      SET [dbo].[TB_LOKASI].[JML_TPS] = TMP_TABLE.[JML_TPS]
       ,[dbo].[TB_LOKASI].[DPT_L] = TMP_TABLE.[DPT_L]
       ,[dbo].[TB_LOKASI].[DPT_P] = TMP_TABLE.[DPT_P]
       ,[dbo].[TB_LOKASI].[TBH_L] = TMP_TABLE.[TBH_L]
       ,[dbo].[TB_LOKASI].[TBH_P] = TMP_TABLE.[TBH_P]
       ,[dbo].[TB_LOKASI].[SAH_1] = TMP_TABLE.[SAH_1]
       ,[dbo].[TB_LOKASI].[SAH_2] = TMP_TABLE.[SAH_2];
GO

MERGE INTO [dbo].[TB_LOKASI]
   USING (SELECT LEFT([KODE_LOKASI],4) AS [KODE_LOKASI]
      ,SUM([JML_TPS]) AS [JML_TPS]
      ,SUM([DPT_L]) AS [DPT_L]
      ,SUM([DPT_P]) AS [DPT_P]
      ,SUM([TBH_L]) AS [TBH_L]
      ,SUM([TBH_P]) AS [TBH_P]
      ,SUM([SAH_1]) AS [SAH_1]
      ,SUM([SAH_2]) AS [SAH_2]
  FROM [dbo].[TB_LOKASI]
  WHERE LEN([KoDe_LOKASI])=6
  GROUP BY LEFT([KODE_LOKASI],4)) AS TMP_TABLE
      ON [dbo].[TB_LOKASI].[KODE_LOKASI] = TMP_TABLE.[KODE_LOKASI]
WHEN MATCHED THEN
   UPDATE 
      SET [dbo].[TB_LOKASI].[JML_TPS] = TMP_TABLE.[JML_TPS]
       ,[dbo].[TB_LOKASI].[DPT_L] = TMP_TABLE.[DPT_L]
       ,[dbo].[TB_LOKASI].[DPT_P] = TMP_TABLE.[DPT_P]
       ,[dbo].[TB_LOKASI].[TBH_L] = TMP_TABLE.[TBH_L]
       ,[dbo].[TB_LOKASI].[TBH_P] = TMP_TABLE.[TBH_P]
       ,[dbo].[TB_LOKASI].[SAH_1] = TMP_TABLE.[SAH_1]
       ,[dbo].[TB_LOKASI].[SAH_2] = TMP_TABLE.[SAH_2];

GO


MERGE INTO [dbo].[TB_LOKASI]
   USING (SELECT LEFT([KODE_LOKASI],2) AS [KODE_LOKASI]
      ,SUM([JML_TPS]) AS [JML_TPS]
      ,SUM([DPT_L]) AS [DPT_L]
      ,SUM([DPT_P]) AS [DPT_P]
      ,SUM([TBH_L]) AS [TBH_L]
      ,SUM([TBH_P]) AS [TBH_P]
      ,SUM([SAH_1]) AS [SAH_1]
      ,SUM([SAH_2]) AS [SAH_2]
  FROM [dbo].[TB_LOKASI]
  WHERE LEN([KoDe_LOKASI])=4
  GROUP BY LEFT([KODE_LOKASI],2)) AS TMP_TABLE
      ON [dbo].[TB_LOKASI].[KODE_LOKASI] = TMP_TABLE.[KODE_LOKASI]
WHEN MATCHED THEN
   UPDATE 
      SET [dbo].[TB_LOKASI].[JML_TPS] = TMP_TABLE.[JML_TPS]
       ,[dbo].[TB_LOKASI].[DPT_L] = TMP_TABLE.[DPT_L]
       ,[dbo].[TB_LOKASI].[DPT_P] = TMP_TABLE.[DPT_P]
       ,[dbo].[TB_LOKASI].[TBH_L] = TMP_TABLE.[TBH_L]
       ,[dbo].[TB_LOKASI].[TBH_P] = TMP_TABLE.[TBH_P]
       ,[dbo].[TB_LOKASI].[SAH_1] = TMP_TABLE.[SAH_1]
       ,[dbo].[TB_LOKASI].[SAH_2] = TMP_TABLE.[SAH_2];
GO
    ....

    bo..bo.... ah...................
     
    mabaega, Jun 12, 2014
    #1
  2. marwanto.se

    marwanto.se New Member

    Joined:
    May 7, 2013
    Messages:
    7,653
    RE: Bisa lebih Simple Gak ya?

    Maksudnya gimana tuh om?
    Kurang mengerti soal begituan, mungkin member lain tahu. :woa:
     
    marwanto.se, Jun 12, 2014
    #2
  3. Yusril Ibnu

    Yusril Ibnu Winpoin Staff Staff Member

    Joined:
    May 12, 2014
    Messages:
    6,373
    RE: Bisa lebih Simple Gak ya?

    Itu bahasa C# ya....
    sayang saya cuma bisa VB.net aja.. :ketawa:
     
    Yusril Ibnu, Jun 13, 2014
    #3
  4. Jazz

    Jazz Windows Freak Staff Member

    Joined:
    Jan 6, 2014
    Messages:
    14,720
    RE: Bisa lebih Simple Gak ya?

    Bukannya di MSDN ada ya Cara-caranya?
     
    Jazz, Jun 13, 2014
    #4
  5. mabaega

    mabaega Well-Known Member

    Joined:
    Nov 9, 2013
    Messages:
    2,735
    RE: Bisa lebih Simple Gak ya?

    Itu ada 3 script dengan perintah serupa, akan lebih efisien jika script diatas dapat dieksekusi 1 kali saja. memang tidak begitu berpengaruh terhadap performance, cuma gak puas aja... xi..xi..xi......

    Itu TSQL om, karena gak ada forumnya, aku tulis di VB/C#, karena nantinya akan dijalankan dari VBNET

    Cara apaan?
    kalau Manualbook, di Helpnya juga lengkap.
    tapi logic penyelesaian kasus, tetap pada kasus dan bagai mana cara berfikir penggunanya.

    ini otak-ku kayaknya lagi males diajak mikir.... xi..xi..xi......
    :ketawa:
     
    mabaega, Jun 13, 2014
    #5
  6. Jazz

    Jazz Windows Freak Staff Member

    Joined:
    Jan 6, 2014
    Messages:
    14,720
    RE: Bisa lebih Simple Gak ya?

    Transact-SQL[/b]]
    Code:
    CREATE TYPE [Sales].[SalesOrderDetailType_inmem] AS TABLE(
  [OrderQty] [smallint] NOT NULL,
  [ProductID] [int] NOT NULL,
  [SpecialOfferID] [int] NOT NULL,
  [LocalID] [int] NOT NULL,

  INDEX [IX_ProductID] HASH ([ProductID]) WITH ( BUCKET_COUNT = 8),
  INDEX [IX_SpecialOfferID] NONCLUSTERED 
)
WITH ( MEMORY_OPTIMIZED = ON )
    MEMORY_OPTIMIZED = ON = Table memory di Optimalkan

    Ix ix ix....
    :belajar:
     
    Jazz, Jun 13, 2014
    #6
  7. Febian

    Febian Administrator Staff Member

    Joined:
    May 7, 2013
    Messages:
    8,029
    RE: Bisa lebih Simple Gak ya?

    maap om g bs bantu
    saya tidak mengerti sama yang beginian :imut2:
     
    Febian, Jun 13, 2014
    #7
  8. mabaega

    mabaega Well-Known Member

    Joined:
    Nov 9, 2013
    Messages:
    2,735
    RE: Bisa lebih Simple Gak ya?

    wkowkowkwo.......
    Coba buka code forum om, pasti banyak TSQL nya.....
    :ketawa:

    Problem Solved ....

    Code:
    USE [QC_PILPRES_2014]
GO
MERGE INTO [dbo].[TB_LOKASI]
   USING (SELECT LEFT([KODE_LOKASI],6) AS [KODE_LOKASI]
      ,SUM([JML_TPS]) AS [JML_TPS]
      ,SUM([DPT_L]) AS [DPT_L]
      ,SUM([DPT_P]) AS [DPT_P]
      ,SUM([TBH_L]) AS [TBH_L]
      ,SUM([TBH_P]) AS [TBH_P]
      ,SUM([SAH_1]) AS [SAH_1]
      ,SUM([SAH_2]) AS [SAH_2]
  FROM [dbo].[TB_LOKASI]
  WHERE LEN([KoDe_LOKASI])=8
  GROUP BY LEFT([KODE_LOKASI],6)
  UNION
  SELECT LEFT([KODE_LOKASI],4) AS [KODE_LOKASI]
      ,SUM([JML_TPS]) AS [JML_TPS]
      ,SUM([DPT_L]) AS [DPT_L]
      ,SUM([DPT_P]) AS [DPT_P]
      ,SUM([TBH_L]) AS [TBH_L]
      ,SUM([TBH_P]) AS [TBH_P]
      ,SUM([SAH_1]) AS [SAH_1]
      ,SUM([SAH_2]) AS [SAH_2]
  FROM [dbo].[TB_LOKASI]
  WHERE LEN([KoDe_LOKASI])=8
  GROUP BY LEFT([KODE_LOKASI],4)
  UNION
  SELECT LEFT([KODE_LOKASI],2) AS [KODE_LOKASI]
      ,SUM([JML_TPS]) AS [JML_TPS]
      ,SUM([DPT_L]) AS [DPT_L]
      ,SUM([DPT_P]) AS [DPT_P]
      ,SUM([TBH_L]) AS [TBH_L]
      ,SUM([TBH_P]) AS [TBH_P]
      ,SUM([SAH_1]) AS [SAH_1]
      ,SUM([SAH_2]) AS [SAH_2]
  FROM [dbo].[TB_LOKASI]
  WHERE LEN([KoDe_LOKASI])=8
  GROUP BY LEFT([KODE_LOKASI],2)) AS TMP_TABLE
      ON [dbo].[TB_LOKASI].[KODE_LOKASI] = TMP_TABLE.[KODE_LOKASI]
WHEN MATCHED THEN
   UPDATE 
      SET [dbo].[TB_LOKASI].[JML_TPS] = TMP_TABLE.[JML_TPS]
       ,[dbo].[TB_LOKASI].[DPT_L] = TMP_TABLE.[DPT_L]
       ,[dbo].[TB_LOKASI].[DPT_P] = TMP_TABLE.[DPT_P]
       ,[dbo].[TB_LOKASI].[TBH_L] = TMP_TABLE.[TBH_L]
       ,[dbo].[TB_LOKASI].[TBH_P] = TMP_TABLE.[TBH_P]
       ,[dbo].[TB_LOKASI].[SAH_1] = TMP_TABLE.[SAH_1]
       ,[dbo].[TB_LOKASI].[SAH_2] = TMP_TABLE.[SAH_2];
GO
     
    mabaega, Jun 13, 2014
    #8
  9. Jazz

    Jazz Windows Freak Staff Member

    Joined:
    Jan 6, 2014
    Messages:
    14,720
    RE: Bisa lebih Simple Gak ya?

    ix ix ix...
    Akhirnya dia Sendiri yg menyelesaikan -,-
     
    Jazz, Jun 13, 2014
    #9

Share This Page