Difference between revisions of "ByQuarter"

From SQLZOO
Jump to: navigation, search
(Created page with "<p>Distribute values across quarters</p> <div class='ht'> <div class=params>schema:scott</div> <source lang=sql class='tidy'>DROP TABLE Insurance;</source> <source lang=sql cl...")
 
Line 6: Line 6:
 
   policynumber VARCHAR(10) NOT NULL PRIMARY KEY,
 
   policynumber VARCHAR(10) NOT NULL PRIMARY KEY,
 
   premium INT,
 
   premium INT,
   TransEff DATE,
+
   TransEff date,
   TransExp DATE
+
   TransExp date
 
);
 
);
 
INSERT INTO Insurance VALUES
 
INSERT INTO Insurance VALUES

Revision as of 20:06, 17 December 2016

Distribute values across quarters

schema:scott
DROP TABLE Insurance;
CREATE TABLE Insurance (
  policynumber VARCHAR(10) NOT NULL PRIMARY KEY,
  premium INT,
  TransEff date,
  TransExp date
);
INSERT INTO Insurance VALUES
('PACA1',490,'2012-04-27','2013-04-27');

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

SELECT policynumber,
          CASE TransEff < '2012-04-01' AND TransEff>= '2012-01-01' THEN 1 END
FROM Insurance