Get Records Statistics

Hi everyone, I was tasked with creating statistics of some records and I've been struggling with it for some days now.

What I'm to get is the sum of the transactions in a month, showing them weekly i.e showing the start date of the week to the end date of the week for a selected month.

I'm supposed to count all patient transactions and show whether the subscription is a new subscription when the subscription count is equal to one and if it is more than one it means it is a renewal of the subscription.

I'm also supposed to show the service name and currency

Table patients {
  id bigint [pk]
  name varchar [not null]
  name varchar [not null]
  created_at timestamp [null]
  updated_at timestamp [null]
  deleted_at timestamp [null]
}

Table subscriptions {
  id bigint [pk]
  patient_id bigint [not null, ref: > patients.id]
  service_id bigint [not null, ref:services.id]
  amount_paid digit [not null, 8]
  currency varchar [not null]
  created_at timestamp [null]
  updated_at timestamp [null]
  deleted_at timestamp [null]
}

Table services {
  id bigint [pk]
  name varchar [not null]
  amount digit [not null, 8]
  created_at timestamp [null]
  updated_at timestamp [null]
  deleted_at timestamp [null]
}

From this whole thing, I'm supposed to get the sum of transactions for each week grouped by either new subscription or renewed subscription Here is what I have so far but still doesn't work like I want it to

$subscriptions = UserTransaction::query()
            ->join('patients', 'patients.id', '=', 'user_transactions.patient_id')
            ->join('services', 'services.id', '=', 'user_transactions.service_id')
            ->select([
                'services.service_name',
                'user_transactions.currency',
                'patients.id as patient_id',
                'user_transactions.created_at',
            ]);

        foreach ($weeks as $week) {
            $subscriptions->addSelect([
                DB::raw(
                    "SUM("
                    ."CASE WHEN user_transactions.created_at >= '{$week['startOfWeek']}' " 
                    ."AND user_transactions.created_at <= '{$week['endOfWeek']}' "
                    ."THEN user_transactions.pay_amount ELSE 0 END) AS week_{$week['week']}"
                )
            ]
            );
        }

        return $subscriptions->get();

LaryAI

3 years ago

Level 58

The code provided in the question is a good starting point. However, it needs some modifications to achieve the desired result. Here is a possible solution:

// First, we need to group the transactions by week and subscription type
$transactions = UserTransaction::query()
    ->join('patients', 'patients.id', '=', 'user_transactions.patient_id')
    ->join('services', 'services.id', '=', 'user_transactions.service_id')
    ->select([
        'services.name as service_name',
        'user_transactions.currency',
        'patients.id as patient_id',
        DB::raw('WEEK(user_transactions.created_at) as week'),
        DB::raw('COUNT(*) as subscription_count'),
        DB::raw('SUM(user_transactions.amount_paid) as total_amount'),
    ])
    ->groupBy('week', 'subscription_count', 'service_name', 'currency');

// Then, we can loop through the weeks and build the result array
$result = [];
foreach ($weeks as $week) {
    $weekNumber = $week['week'];
    $startDate = $week['startOfWeek'];
    $endDate = $week['endOfWeek'];

    $newSubscriptions = $transactions
        ->where('subscription_count', 1)
        ->whereBetween('user_transactions.created_at', [$startDate, $endDate])
        ->get();

    $renewedSubscriptions = $transactions
        ->where('subscription_count', '>', 1)
        ->whereBetween('user_transactions.created_at', [$startDate, $endDate])
        ->get();

    $result[$weekNumber] = [
        'start_date' => $startDate,
        'end_date' => $endDate,
        'new_subscriptions' => $newSubscriptions,
        'renewed_subscriptions' => $renewedSubscriptions,
    ];
}

// Finally, we can return the result
return $result;

This code assumes that $weeks is an array of week start and end dates, similar to what is used in the original code. The result is an array that contains the start and end dates of each week, as well as the new and renewed subscriptions for that week.

caleb65

3 years ago

Level 1

I get an error that says subscription_count column is unknown

Please or to participate in this conversation.