creating multiple rows based on record in an array - laravel

SELECT id, name, DATE_FORMAT(DATE_ADD(start_date, INTERVAL Units.i + Tens.i * 10 + Hundreds.i * 100 MONTH), '%Y-%m') AS `month`, (amount / duration) AS `amount_this_month`
FROM SomeTable
CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Units
CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Tens
CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Hundreds
WHERE (Units.i + Tens.i * 10 + Hundreds.i * 100) < duration

   SELECT
    c.id
   FROM conversations c
   JOIN conversation_participants p ON p.conversation_id = c.id
   JOIN conversation_participants p2 ON p2.conversation_id = p.conversation_id
   WHERE  c.project_id = ?
   AND p.user_id = ? and p2.user_id = ?

p.user_id = 1   --as user1
p2.user_id = 2  --as user2
c.project_id = 1 

/*groups the expiring policies for each user*/
$with_expiring = $expiring->groupBy('client_id');

/*counts the number of clients with expiring policies instead of
 *counting the number of expiring policies first then checking
 *against a list of 'client_id's
 */
$total_with_expiring = $with_expiring->count();

/*groups the expired policies for each user*/
$with_expired = $expired->groupBy('client_id');

/*counts the number of clients with expired policies instead of
 *counting the number of expired policies first then checking
 *against a list of 'client_id's
 */
$total_with_expired = $with_expired->count();

-- sample data  
WITH table_data
AS (
    SELECT 1 AS id
        ,cast('2017-08-14' AS DATE) AS start_dt
        ,cast('2018-12-16' AS DATE) AS end_dt

UNION ALL

SELECT 2 AS id
    ,cast('2017-09-14' AS DATE) AS start_dt
    ,cast('2019-01-16' AS DATE) AS end_dt
)

-- find minimum date from the data  
,starting_date (start_date)
AS (
SELECT min(start_dt)
FROM TABLE_DATA
)

--get all months between min and max dates  
,all_dates
AS (
SELECT last_day(add_months(date_trunc('month', start_date), idx * 1)) month_date
FROM starting_date
CROSS JOIN _v_vector_idx
WHERE month_date <= add_months(start_date, abs(months_between((
                    SELECT min(start_dt) FROM TABLE_DATA), (SELECT max(end_dt) FROM TABLE_DATA))) + 1)
ORDER BY month_date
)
SELECT id  
,extract(year FROM month_date)  
,extract(month FROM month_date)  
,td.start_dt  
,td.end_dt  
FROM table_data td  
INNER JOIN all_dates ad  
    ON ad.month_date > td.start_dt  
        AND ad.month_date <= last_day(td.end_dt)  
ORDER BY 1  
    ,2  

import numpy as np
import pandas as pd

df = pd.DataFrame({'work_date': [1, 2, 3, 4, np.nan], 'time_code': ['WRK', 'OFF', 'VAT', 'BONUS', 'OT15']})

select_time_codes = ['VAT', 'WRK', 'OFF']

df.loc[df['work_date'].notna() & df['time_code'].isin(select_time_codes), 'spotted'] = 'No'

   work_date time_code spotted
0        1.0       WRK      No
1        2.0       OFF      No
2        3.0       VAT      No
3        4.0     BONUS     NaN
4        NaN      OT15     NaN