create table tis_temp_dsr(tis_customer_no varchar(10),tis_account_name varchar(30)
,tis_amount_with_vat float,tis_posting_date date);
insert into tis_temp_dsr (tis_customer_no,tis_account_name,tis_amount_with_vat,tis_posting_date) values
('20','Juan', 8,'2025-01-15')
,('20','Juan',12,'2025-01-16')
,('20','Juan',10,'2025-01-10')
,('20','Juan',10,'2025-03-11')
,('20','Juan',10,'2025-03-12')
,('20','Juan',30,'2025-03-22')
,('20','Juan',40,'2025-04-04')
,('20','Juan',50,'2025-05-05')
,('20','Juan',60,'2025-06-06')
,('21','Other',77,'2025-01-06')
;
select * from tis_temp_dsr;
declare @startDate date='2025-01-01';
declare @endDate date='2025-03-31';
select tis_customer_no,tis_account_name
,format(tis_posting_date,'MMMyyyy') mon
,sum(tis_amount_with_vat) tSum
,sum(sum(tis_amount_with_vat))over(partition by tis_customer_no, tis_account_name )totSum
from tis_temp_dsr
where tis_posting_date between @startDate and @endDate
group by tis_customer_no, tis_account_name, format(tis_posting_date,'MMMyyyy')
<?php
use Carbon\Carbon;
$now = Carbon::now()->format('d/m/Y');
printf("Today is %s\nCurrent PHP version: %s \n\n", $now, phpversion());
//$query = "SELECT VERSION() as version, @p1 as p1;";
$query = "SELECT @@VERSION as version;";
// get DB version using PDO
$stmt = $pdo->prepare($query);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
printf('DB version (PDO): %s ' . PHP_EOL, $row['version']);
$p1='20';
$startDate date='2025-01-01';
$endDate date=date::'2025-03-31';
$query = "
select coalesce(tis_customer_no,'TotalForCustomer') tis_customer_no
,coalesce(tis_account_name,'TotalForAccount') tis_account_name
,format(tis_posting_date,'MMMyyyy') mon
,sum(tis_amount_with_vat) tSum
,sum(sum(tis_amount_with_vat))over(partition by tis_customer_no, tis_account_name )totSum
from tis_temp_dsr
where tis_posting_date between @startDate and @endDate
group by tis_customer_no, tis_account_name, format(tis_posting_date,'MMMyyyy') ";
$stmt = $pdo->prepare($query);
$stmt->execute([':p1'=>$p1]);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
printf('customer_no: %s account_name %s ' . PHP_EOL, $row['tis_customer_no'], $row['tis_account_name']);
printf('mon1: %s mon2 %s ' . PHP_EOL, $row[0], $row[3]);
echo '<pre>'. print_r($row, 1) .'</pre>';
?>