1 files changed, 76 insertions, 0 deletions
diff --git a/sql/mlpdb.sql b/sql/mlpdb.sql
new file mode 100644
index 00000000..4ac869f7
--- /dev/null
+++ b/sql/mlpdb.sql
@@ -0,0 +1,76 @@
+CREATE TABLE tranche_data (
+       QuoteDate date,
+       TrancheId integer,
+       BasketId integer,
+       Maturity	 date,
+       Tenor varchar(4),
+       RefBasketPrice float,
+       BPS varchar(1),
+       BasketDuration float,
+       TQC varchar(1),
+       TrancheDuration float,
+       TrancheDelta float,
+       CorrAtDetachment float,
+       Basis float,
+       QuoteSource varchar(2),
+       Index varchar(4),
+       Series smallint,
+       Upfront float,
+       Running float,
+       Attach smallint,
+       Detach smallint,
+       IndexFactor float,
+       CumulativeLoss float
+);
+
+GRANT ALL ON tranche_data TO mlpdb_user;
+
+CREATE TABLE index_desc(
+       BasketID integer,
+       Index varchar(4),
+       Series smallint,
+       Maturity date,
+       Tenor varchar(4),
+       IndexFactor float,
+       CumulativeLoss float,
+       PRIMARY KEY(BasketID, Tenor)
+);
+
+GRANT ALL ON index_desc TO mlpdb_user;
+
+CREATE TABLE tranche_desc(
+       TrancheID integer,
+       BasketID integer,
+       Tenor varchar(4),
+       Attach smallint,
+       Detach smallint,
+       Foreign Key(BasketID, Tenor) REFERENCES index_desc(BasketID, Tenor),
+       Primary Key(TrancheID, Tenor)
+);
+
+GRANT ALL ON tranche_desc TO mlpdb_user;
+
+CREATE TABLE quotes (
+       QuoteDate date,
+       TrancheID integer,
+       BasketID integer,
+       RefBasketPrice float,
+       BasketDuration float,
+       TrancheDuration float,
+       TrancheDelta float,
+       CorrAtDetachment float,
+       Basis float,
+       QuoteSource varchar(4),
+       Upfront float,
+       Running float,
+       Tenor varchar(4),
+       FOREIGN KEY(BasketID, Tenor) REFERENCES index_desc(BasketID, Tenor),
+       FOREIGN KEY(TrancheID, Tenor) REFERENCES tranche_desc(TrancheID, Tenor)
+);
+
+GRANT ALL ON quotes TO mlpdb_user;
+
+
+select * from quotes inner join (select tranche_desc.* from tranche_desc inner join (select * from index_desc where tenor='Y10' and series=9 and index='IG') b on tranche_desc.basketID = b.basketID
+and tranche_desc.tenor = b.tenor where attach=7 and detach=10) a
+ on quotes.trancheID=a.trancheID and quotes.tenor = a.tenor order by quotedate desc;