CODEWITHSUNDEEP
sqlsqlite
compiler
compiler

Reputation: 496

Creating views with different columns than table

I have a SQL table, which has entries out of which one of the columns is date.

table1

       ------------------------------------------
       | visit_to | visit_by | visit_on | value |
       ------------------------------------------
            x           a     2015-02-02    1
            x           b     2015-02-16    2
            y           c     2015-02-18    3

I want to present the data to the user like this:

table2

       -----------------------------------
       | value | Fortnite 1 | fortnite 2 |
       -----------------------------------
           x         a            b
           y                      c

now a fortnite is from 1st day of the month to 15th day of the same month.
table1 has the list of visits made by a, b and c and the dates of theirs visits, the person x gets 2 visits one in each fortnite. And y gets only 1 visit in second fortnite.

The table2 should compare these dates and present data in shown format. How can I do this? I'm using Sqlite.

Heres what is was able to do:

select visit_to, visit_by as fortnite_one
  from visit
  where julianday(date) - julianday('now','start of month','+1 month','-16 day') <= 0 
union
select visit_to, visit_by as fortnite_two
  from visit
  where julianday(date) - julianday('now','start of month','+1 month','-16 day') >= 0

But it gives the result for both(fortnite1 and fortnite2) in the fortnite_one column only.

Upvotes: 0

Views: 64

Answers (1)

daniel
daniel

Reputation: 1070

I assume the table and data as below. 

create table visit (visit_to text, visit_by text, visit_on datetime, value int);
insert into visit values('x', 'a', '2015-02-02 00:00:00', 1);
insert into visit values('x', 'b', '2015-02-16 00:00:00', 2);
insert into visit values('y', 'c', '2015-02-18 00:00:00', 3);
insert into visit values('x', 'd', '2015-02-14 00:00:00', 4);

And the query is like this.

select
    visit_to,
    date(visit_on, 'start of month') year_month,
    replace(rtrim(group_concat((case when date(date(visit_on, '-15 days'), 'start of month') <> date(visit_on, 'start of month') then visit_by else '' end), (case when date(date(visit_on, '-15 days'), 'start of month') <> date(visit_on, 'start of month') then ' ' else '' end))), ' ', ',') fortnite1,
    replace(rtrim(group_concat((case when date(date(visit_on, '-15 days'), 'start of month') = date(visit_on, 'start of month') then visit_by else '' end), (case when date(date(visit_on, '-15 days'), 'start of month') = date(visit_on, 'start of month') then ' ' else '' end))), ' ', ',') fortnite2
from visit
group by visit_to, date(visit_on, 'start of month')
;

You can try http://goo.gl/TXomRO

Hope this help.

Upvotes: 1

Related Questions