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...................
     
  2. marwanto.se

    marwanto.se New Member

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

    Maksudnya gimana tuh om?
    Kurang mengerti soal begituan, mungkin member lain tahu. :woa:
     
  3. Yusril Ibnu

    Yusril Ibnu Winpoin Staff Staff Member

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

    Itu bahasa C# ya....
    sayang saya cuma bisa VB.net aja.. :ketawa:
     
  4. Jazz

    Jazz Super Moderatór Staff Member

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

    Bukannya di MSDN ada ya Cara-caranya?
     
  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:
     
  6. Jazz

    Jazz Super Moderatór Staff Member

    Joined:
    Jan 6, 2014
    Messages:
    14,731
    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:
     
  7. Febian

    Febian Administrator Staff Member

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

    maap om g bs bantu
    saya tidak mengerti sama yang beginian :imut2:
     
  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
    
     
  9. Jazz

    Jazz Super Moderatór Staff Member

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

    ix ix ix...
    Akhirnya dia Sendiri yg menyelesaikan -,-
     

Share This Page