kit kit - 3 months ago 10
SQL Question

sql, appending and determine value within a cell

I have the following schema:

CREATE TABLE table1(colA DATE, colB INT, colC TEXT);

INSERT INTO table1(colA, colB, colC) VALUES ("2016-01-01", "1", "C1");
INSERT INTO table1(colA, colB, colC) VALUES ("2016-01-02", "2", "C2");

CREATE TABLE table2(colD TEXT, colE TEXT, colF TEXT);

INSERT INTO table2(colD, colE, colF) VALUES ("2016-01-03", "3", "F1");
INSERT INTO table2(colD, colE, colF) VALUES ("2016-01-04", "4", "F2");


What I am trying to do is append one table to the other and then add another column (colG). A different message should be displayed in each cell in colG depending on the date range in colA. I will also be adding another column that will have a message depending on the value of the integer. So I am looking for a general approach. Any ideas how to achieve that? Much appreciated. This is what I have tried:

SELECT colA,
colB,
CASE When date <2016-01-01 "daterange A"
When date <2016-01-01<2016-01-02 "daterangeB"
When date <2016-01-02<2016-01-03 "daterangeC"
When date <2016-01-03<2016-01-04 "daterangeD"

AS colG
FROM (
SELECT colA, colB FROM table1
UNION ALL
SELECT colD, colE FROM table2
) union_tbl;

Answer

If your syntax were correct, your SQL should do what you want. Something like this:

SELECT colA, 
       colB, 
       (CASE When date < '2016-01-01' then 'daterange A'
             When date < '2016-01-02' then 'daterangeB'
             When date < '2016-01-03' then 'daterangeC'
             When date < '2016-01-04' then 'daterangeD'
        END) AS colG 
FROM (SELECT colA, colB FROM table1
      UNION ALL
      SELECT colD, colE FROM table2
     ) t;

Notes:

  • Date constants need to be wrapped in single quotes (in most databases).
  • String constants should be wrapped in single quotes (the ANSI standard).
  • case statements evaluate each clause in order, so you can simplify the logic.