jeudi 1 décembre 2016

Laravel Query to Group by two different dates

Assuming I am searching from :01-01-2016 to : 03-12-2016 Here is my current table in the table:

created_at   start_at     status 
---------------------------------
2016-12-01   2016-01-01    A
2016-12-02   2016-02-01    A
2016-12-03   2016-03-01    A
2016-12-03   2016-04-01    A
2016-12-03   2016-12-01    P
2016-12-03   2016-12-01    P
2016-12-03   2016-12-01    P

I would like to output like this after the query >>

Month          P        A
---------------------------
Jan/2016       0        1
Feb/2016       0        1
March/2016     0        1
April/2016     0        1
Dec/2016       3        0

Here is my current laravel 5.2 query:

DB::select(
    DB::raw('select 
        sum(case when outreach_links.status="P" and outreach_links.created_at >= ? and outreach_links.created_at <=? then 1 else 0 end) as pp,
        sum(case when outreach_links.status="A" and outreach_links.start_date >= ? and outreach_links.start_date <=? then 1 else 0 end) as aa,
        sum(case when outreach_links.status="A" then outreach_links.cost else 0 end) as cc,
        MONTH(outreach_links.created_at) month,
        MONTHNAME(outreach_links.created_at) month_name,
        YEAR(outreach_links.created_at) year from outreach
        inner join outreach_links on outreach.id = outreach_links.outreach_id
        where outreach.profile_id=?
        group by month, year'),
    [$from, $to, $from, $to, $pro_id]
);

the output for this one since I am grouping by (created_at) is only this: ( how can I group all status that is "A" by the start_at not created_at?)

    Month          P        A 
    ---------------------------
    Dec/2016       3        4

Can you help me to modify the query?

Thanks



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire