Difference between revisions of "ByQuarter"

From SQLZOO
Jump to navigation Jump to search
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 datetime,
   TransExp date
+
   TransExp datetime
 
);
 
);
 
INSERT INTO Insurance VALUES
 
INSERT INTO Insurance VALUES
Line 17: Line 17:
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
SELECT policynumber,
 
SELECT policynumber,
           CASE TransEff < '2012-04-01' AND TransEff>= '2012-01-01' THEN 1 END
+
           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
 
FROM Insurance
 
</source>
 
</source>
 
</div>
 
</div>
 
{{Hacks Ref}}
 
{{Hacks Ref}}

Revision as of 21:11, 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 datetime,
  TransExp datetime
);
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 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