Create View that separates a line in various

Question

I have this table:

-- Product --
id_product
date-begin
date_end
period

And fill with:

id_project | date_begin | date_end | period
1 | 2015-01-01 | 2016-01-01 | 1 month
2 | 2015-12-01 | 2015-12-29 | 1 week

I wanted the View to show it:

id_project | date_payment
1 | 2015-02-01
1 | 2015-03-01
1 | 2015-04-01
...
1 | 2016-01-01
2 | 2015-12-01
2 | 2015-12-08
2 | 2015-12-15
2 | 2015-12-22
2 | 2015-12-29

How do I divide a line into several?

I am using mysql, and I want create a View – Giest Dec 14 '15 at 18:57 — Giest, Dec 14 '15 at 18:57

score 1 · Accepted Answer · edited May 23 '17 at 11:59

Here is a pretty basic example of what you can do to solve your problem.

The first thing it does is create some numbers views for you so we can join later. these are some cartesian joins to create large sets of number.. 16 to 1 million..

CREATE OR REPLACE VIEW generator_16
AS SELECT 0 n UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL 
   SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL
   SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL
   SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
   SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL 
   SELECT 15;

CREATE OR REPLACE VIEW generator_256
AS SELECT ( ( hi.n << 4 ) | lo.n ) AS n
     FROM generator_16 lo, generator_16 hi;

CREATE OR REPLACE VIEW generator_4k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
     FROM generator_256 lo, generator_16 hi;

CREATE OR REPLACE VIEW generator_64k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
     FROM generator_256 lo, generator_256 hi;

Found that code here.. Creating a "Numbers Table" in mysql

Then you can create your view using this statement.

CREATE OR REPLACE VIEW product_payment_dates
AS SELECT   p.id_project,  
        CASE  
            WHEN p.period LIKE '%month%' THEN DATE_ADD(p.date_begin,INTERVAL n.n MONTH)
            WHEN p.period LIKE '%week%' THEN DATE_ADD(p.date_begin,INTERVAL n.n WEEK)
            WHEN p.period LIKE '%day%' THEN DATE_ADD(p.date_begin,INTERVAL n.n DAY)
        END as date_payment
FROM    Product p
JOIN    generator_64k n ON (CASE 
                        WHEN p.period LIKE '%month%' THEN DATE_ADD(p.date_begin, INTERVAL n.n MONTH)
                        WHEN p.period LIKE '%week%' THEN DATE_ADD(p.date_begin, INTERVAL n.n WEEK)
                        WHEN p.period LIKE '%day%' THEN DATE_ADD(p.date_begin, INTERVAL n.n DAY)
                    END) <= p.date_end
ORDER BY p.id_project, date_payment;

This looks at the period to determine if you should add months, weeks, or days to the begin_date to build the list. You can also customize this to pull the interval number from the period, in case you want to add 2 months, or 3 weeks etc..

Here is a SQL Fiddle of everything

score 0 · Answer 2 · answered Dec 14 '15 at 18:46

0

All you need is dates table then do a Inner Join your table with the dates table

select * 
from yourtable A
inner join Dates_table b 
on b.dates between a.date_begin and a.date_end

If you are using Sql Server check this link for dates table code

answered Dec 14 '15 at 18:46

Pரதீப்

91,748
19
131
172

I want to create a view as I showed above. Starting a record, I intend that this View be generated ((date_end - date_begin) / period) rows. – Giest Dec 14 '15 at 18:58
1

@Giest So you'd just slap this answer inside a 'create view' statement, but you need a table that has the list of dates to join to. – Hart CO Dec 14 '15 at 19:03
Then this way is impossible. Thanks Not having this issue solution, it must eliminate it? – Giest Dec 14 '15 at 19:10

Create View that separates a line in various

2 Answers2