Difference between revisions of "ByQuarter"

From SQLZOO
Jump to navigation Jump to search
Line 2: Line 2:
 
<div class='ht'>
 
<div class='ht'>
 
<div class=params>schema:scott</div>
 
<div class=params>schema:scott</div>
<source lang=sql class='tidy'>DROP TABLE Insurance;</source>
+
<source lang=sql class='tidy'>DROP TABLE Insurance;DROP TABLE quarters;</source>
 
<source lang=sql class='setup'>CREATE TABLE Insurance (
 
<source lang=sql class='setup'>CREATE TABLE Insurance (
 
   policynumber VARCHAR(10) NOT NULL PRIMARY KEY,
 
   policynumber VARCHAR(10) NOT NULL PRIMARY KEY,
Line 11: Line 11:
 
INSERT INTO Insurance VALUES
 
INSERT INTO Insurance VALUES
 
('PACA1',490,'2012-04-27','2013-04-27');
 
('PACA1',490,'2012-04-27','2013-04-27');
 +
CREATE TABLE quarter(
 +
  lo DATETIME NOT NULL PRIMARY KEY,
 +
  hi DATETIME NOT NULL
 +
);
 +
INSERT INTO quarters VALUES ('2012-01-01','2012-03-31');
 +
INSERT INTO quarters VALUES ('2012-04-01','2012-06-31');
 +
INSERT INTO quarters VALUES ('2012-07-01','2012-09-30');
 +
INSERT INTO quarters VALUES ('2012-10-01','2012-12-31');
 +
INSERT INTO quarters VALUES ('2013-01-01','2013-03-31');
 +
INSERT INTO quarters VALUES ('2013-04-01','2013-06-31');
 +
INSERT INTO quarters VALUES ('2013-07-01','2013-09-30');
 +
INSERT INTO quarters VALUES ('2013-10-01','2013-12-31');
 
</source>
 
</source>
 
<div>
 
<div>
Line 16: Line 28:
 
</div>
 
</div>
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
SELECT policynumber,
 
          CASE WHEN TransEff < '2012-04-01' AND TransEff>= '2012-01-01' THEN 1 END
 
          CASE WHEN TransEff < '2012-07-01' AND TransEff>= '2012-03-01' THEN 1 END
 
          CASE WHEN TransEff < '2012-10-01' AND TransEff>= '2012-06-01' THEN 1 END
 
          CASE WHEN TransEff < '2013-01-01' AND TransEff>= '2012-09-01' THEN 1 END
 
FROM Insurance
 
</source>
 
 
</div>
 
</div>
 
{{Hacks Ref}}
 
{{Hacks Ref}}

Revision as of 21:18, 17 December 2016

Distribute values across quarters

schema:scott
DROP TABLE Insurance;DROP TABLE quarters;
CREATE TABLE Insurance (
  policynumber VARCHAR(10) NOT NULL PRIMARY KEY,
  premium INT,
  TransEff datetime,
  TransExp datetime
);
INSERT INTO Insurance VALUES
('PACA1',490,'2012-04-27','2013-04-27');
CREATE TABLE quarter(
  lo DATETIME NOT NULL PRIMARY KEY,
  hi DATETIME NOT NULL
);
INSERT INTO quarters VALUES ('2012-01-01','2012-03-31');
INSERT INTO quarters VALUES ('2012-04-01','2012-06-31');
INSERT INTO quarters VALUES ('2012-07-01','2012-09-30');
INSERT INTO quarters VALUES ('2012-10-01','2012-12-31');
INSERT INTO quarters VALUES ('2013-01-01','2013-03-31');
INSERT INTO quarters VALUES ('2013-04-01','2013-06-31');
INSERT INTO quarters VALUES ('2013-07-01','2013-09-30');
INSERT INTO quarters VALUES ('2013-10-01','2013-12-31');

In this example you want to distribute the income across several quarters

<source lang='sql' class='def'>