IF NOT EXISTS (
    select * from sysobjects where name='metaInColumns' and xtype='U'
) CREATE TABLE metaInColumns (
    code NVARCHAR(32) NOT NULL,
    description NVARCHAR(128) NOT NULL,
    class VARCHAR(5) NOT NULL CHECK(class IN('Flow', 'Stock')),
    scale TINYINT NOT NULL,
    frequency VARCHAR(12) NOT NULL CHECK(frequency IN('Daily','Weekly','Monthly','BiMonthly','Quarterly','QuadMonthly','SemiAnnual','Annual')),
    region NVARCHAR(4) NOT NULL,
    category NVARCHAR(128) NOT NULL
    PRIMARY KEY(code)
);
GO

INSERT INTO metaInColumns
VALUES (N'plccpi', N'Core CPI', N'Stock', 0, N'Monthly', N'pl', N'Prices'),
       (N'plcpi', N'Total CPI', N'Stock', 0, N'Monthly', N'pl', N'Prices'),
       (N'plgdp', N'Total, PP=100', 'Flow', 0, 'Quarterly', 'pl', N'GDP'),
       (N'secpi', N'Total CPI', N'Stock', 0, N'Monthly', N'se', N'Prices'),
       (N'secpif', N'CPI at constant interest rates', N'Stock', 0, N'Monthly', N'se', N'Prices'),
       (N'segdp', N'Total', N'Flow', 6, N'Quarterly', N'se', N'GDP'),
       (N'Escpi', N'Core CPI', N'Class', 0, N'Monthly', N'es', N'Prices'),
       (N'pt cpi', N'Core CPI', N'Stock', 0, N'Monthly', N'pt', N'Prices');
GO


IF NOT EXISTS (
    select * from sysobjects where name='obs' and xtype='U'
) CREATE TABLE obs (
    code NVARCHAR(32) NOT NULL,
    ts DATE NOT NULL,
    v FLOAT NOT NULL
    PRIMARY KEY(code, ts)
);
GO

INSERT INTO obs
VALUES (N'plccpi', '2015-01-01', 99.762),
    (N'plccpi', '2015-02-01', 99.85),
    (N'plccpi', '2015-03-01', 100.202),
    (N'plccpi', '2015-04-01', 100.445),
    (N'plccpi', '2015-05-01', 100),
    (N'plccpi', '2015-06-01', 100.049),
    (N'plccpi', '2015-07-01', 99.907),
    (N'plcpi', '2015-01-01', 169.2),
    (N'plcpi', '2015-02-01', 169),
    (N'plcpi', '2015-03-01', 169.3),
    (N'plcpi', '2015-04-01', 170),
    (N'plcpi', '2015-05-01', 170),
    (N'plcpi', '2015-06-01', 170),
    (N'plcpi', '2015-07-01', 169.8),
    (N'plgdp', '2015-01-01', 101),
    (N'plgdp', '2015-04-01', 100.9),
    (N'secpi', '2015-01-01', 310.75),
    (N'secpi', '2015-02-01', 312.93),
    (N'secpi', '2015-03-01', 313.19),
    (N'secpi', '2015-04-01', 313.16),
    (N'secpi', '2015-05-01', 314.24),
    (N'secpi', '2015-06-01', 313.33),
    (N'secpi', '2015-07-01', 313.43),
    (N'secpif', '2015-01-01', 200.87),
    (N'secpif', '2015-02-01', 202.48),
    (N'secpif', '2015-03-01', 202.74),
    (N'secpif', '2015-04-01', 203.06),
    (N'secpif', '2015-05-01', 203.95),
    (N'secpif', '2015-06-01', 203.52),
    (N'secpif', '2015-07-01', 203.81),
    (N'segdp', '2015-01-01', 9966),
    (N'segdp', '2015-04-01', 10063),
    (N'Escpi', '2017-01-01', 500),
    (N'Escpi', '2017-02-01', 750),
    (N'Escpi', '2017-03-01', 1000),
    (N'pt cpi', '2016-01-01', 500),
    (N'pt cpi', '2016-02-01', 750),
    (N'pt cpi', '2016-03-01', 1000);

GO


