JAKARTA TIMUR - CILIWUNG

Sesi Pertama

SELECT     TsMovement3Cass.IATACode, MsAirline.AirlineName, MsCustomerCass.AgentCode, TsMovement3Cass.AccNo, MsCustomerCass.AccName,
                      SUM(TsMovement3Cass.BillCurr15 + TsMovement3Cass.BillCurr30) AS Sum_Of_Bill,
                      SUM(TsMovement3Cass.PayBillCurr15 + TsMovement3Cass.PayBillCurr30) AS Sum_Of_Payment,
                      MsMonth.MonthName + ‘ ‘ + TsCalendarCass.Cycle + ‘  ‘ + CONVERT(varchar, TsCalendarCass.CalendarYear) AS PeriodName, MsMonth.MonthName,
                      TsCalendarCass.Cycle, TsCalendarCass.CalendarYear, MsCustomerCass.AgentCode AS Expr1
FROM         TsMovement3Cass INNER JOIN
                      MsAirline ON TsMovement3Cass.IATACode = MsAirline.IATACode INNER JOIN
                      MsCustomerCass ON TsMovement3Cass.AccNo = MsCustomerCass.AccNo INNER JOIN
                      TsCalendarCass ON TsMovement3Cass.CalendarID = TsCalendarCass.CalendarID INNER JOIN
                      MsMonth ON TsCalendarCass.CalendarMonth = MsMonth.MonthID
WHERE     (TsMovement3Cass.Status = ‘A’)
GROUP BY TsMovement3Cass.IATACode, MsAirline.AirlineName, TsMovement3Cass.AccNo, MsCustomerCass.AccName, MsMonth.MonthName,
                      TsCalendarCass.Cycle, MsMonth.MonthName, TsCalendarCass.Cycle, TsCalendarCass.CalendarYear, MsCustomerCass.AgentCode

Sesi Kedua PIVOT Table

SELECT  A.CalendarID, A.CalendarYear, A.CalendarMonth,
 A.MonthName, A.Cycle, A.PeriodName, A.TrxDate,
 A.AccNo, A.AccName, A.AgentCode, A.CityAbrv,
        A.CcyName,
 SUM(CASE A.IATACode WHEN ‘020′
 THEN A.BillCurr30 ELSE 0 END) AS ‘Bill_30_020′,
        SUM(CASE A.IATACode WHEN ‘065′
 THEN A.BillCurr30 ELSE 0 END) AS ‘Bill_30_065′,
        SUM(CASE A.IATACode WHEN ‘020′
 THEN A.PayBillCurr30 ELSE 0 END) AS ‘Pay_30_020′,
        SUM(CASE A.IATACode WHEN ‘065′
 THEN A.PayBillCurr30 ELSE 0 END) AS ‘Pay_30_065′,
        SUM(CASE A.IATACode WHEN ‘020′
 THEN A.BillPrev30 ELSE 0 END) AS ‘Bill_30_P_020′,
        SUM(CASE A.IATACode WHEN ‘065′
 THEN A.BillPrev30 ELSE 0 END) AS ‘Bill_30_P_065′,
        SUM(CASE A.IATACode WHEN ‘020′
 THEN A.PayBillPrev30 ELSE 0 END) AS ‘Pay_30_P_020′,
        SUM(CASE A.IATACode WHEN ‘065′
 THEN A.PayBillPrev30 ELSE 0 END) AS ‘Pay_30_P_065′
FROM    (SELECT     TsMovement3Cass.CalendarID,
 TsCalendarCass.CalendarYear, TsCalendarCass.CalendarMonth,
 MsMonth.MonthName, TsCalendarCass.Cycle,
 MsMonth.MonthName + ‘ ‘ + TsCalendarCass.Cycle + ‘ - ‘ +
 CONVERT(varchar, TsCalendarCass.CalendarYear)
        AS PeriodName, TsMovement3Cass.TrxDate, TsMovement3Cass.AccNo,
 MsCustomerCass.AccName, MsCustomerCass.AgentCode,
        TsMovement3Cass.IATACode, MsCustomerCass.CityAbrv,
 MsCcy.CcyName, TsMovement3Cass.OpenBal, BillCurr30, PayBillCurr30,
        OutBillCurr30, BillPrev30, PayBillPrev30, CloseBalPrev30
FROM    TsMovement3Cass
INNER JOIN MsCustomerCass
 ON TsMovement3Cass.AccNo = MsCustomerCass.AccNo
INNER JOIN TsCalendarCass
 ON TsMovement3Cass.CalendarID = TsCalendarCass.CalendarID
INNER JOIN MsMonth
 ON TsCalendarCass.CalendarMonth = MsMonth.MonthID
INNER JOIN MsCcy ON MsCustomerCass.Ccy = MsCcy.Ccy) A
GROUP BY A.CalendarID, A.CalendarYear, A.CalendarMonth,
 A.MonthName, A.Cycle, A.PeriodName, A.TrxDate,
 A.AccNo, A.AccName, A.AgentCode, A.CityAbrv, A.CcyName

————————————————————————

SQL SERVER - 2005 - T-SQL Script to Attach and Detach Database

August 24, 2007 by pinaldave

Following script can be used to detach or attach database. If database is to be from one database to another database following script can be used detach from old server and attach to new server.

Process to move database :

—-Step 1 : Detach Database using following script

USE [master]

GO

EXEC master.dbo.sp_detach_db @dbname = N’AdventureWorks’,

@keepfulltextindexfile = N’true’

GO

—-Step 2 : Move Data files and Log files to new location

–—Step 3 : Attach Database using following script

USE [master]

GO

CREATE DATABASE [AdventureWorks] ON

( FILENAME = N’C:\Data\AdventureWorks_Data.mdf’ ),

( FILENAME = N’C:\Data\AdventureWorks_Log.ldf’ )

FOR ATTACH

GO

IF EXISTS (    SELECT name

FROM master.sys.databases sd

WHERE name = N’AdventureWorks’

AND SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() )

EXEC [AdventureWorks].dbo.sp_changedbowner @loginame=N’sa’,

@map=false

GO

Reference : Pinal Dave (http://www.SQLAuthority.com), SQL SERVER - 2005 Take Off Line or Detach Database

SQL SERVER - Import CSV File Into SQL Server Using Bulk Insert - Load Comma Delimited File Into SQL Server

February 6, 2008 by pinaldave

This is very common request recently - How to import CSV file into SQL Server? How to load CSV file into SQL Server Database Table? How to load comma delimited file into SQL Server? Let us see the solution in quick steps.

CSV stands for Comma Separated Values, sometimes also called Comma Delimited Values.

Create TestTable

USE TestData

GO

CREATE TABLE CSVTest

    (ID INT,

                FirstName VARCHAR(40),

                LastName VARCHAR(40),

                BirthDate SMALLDATETIME)

GO

Create CSV file in drive C: with name csvtest.txt with following content. The location of the file is C:\csvtest.txt

1,James,Smith,19750101

2,Meggie,Smith,19790122

3,Robert,Smith,20071101

4,Alex,Smith,20040202

Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted.

BULK 

INSERT CSVTest

        FROM ‘c:\csvtest.txt’

            WITH

    (

                FIELDTERMINATOR = ‘,’,

                ROWTERMINATOR = ‘\n’

    )

GO

Check the content of the table.

SELECT *

    FROM CSVTest

GO

Drop the table to clean up database.

SELECT *

    FROM CSVTest

GO

Reference : Pinal Dave (http://www.SQLAuthority.com)