Get the number of rows inserted in the database per hour

user_14667897 0
Viewed 153

I know there are similar questions already, but I can't find a clear and simple answer.

I have this query :

"SELECT dbo.tbTransaction.dateHeure, dbo.tbTransaction.Etat, dbo.tbPoste.nomPoste
        FROM dbo.tbTransaction 
        INNER JOIN dbo.tbPoste ON dbo.tbTransaction.ID_poste = dbo.tbPoste.ID_POSTE
        WHERE dbo.tbPoste.id_site LIKE 47 
        AND dbo.tbPoste.nomPoste LIKE '0909_CLIENT'
        AND dateHeure >= DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)
        ORDER BY dateHeure DESC";

It enables me to fetch all the database insertions of the day(>= DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)) on a particular post (0909_CLIENT), sorted by the most recent time of insertion(ORDER BY dateHeure DESC).

I would like to be able to retrieve the number of inserts per hour, the goal is to be able to generate live graphics hour by hour.

I manage to get the total number of insertions during the day with :

$params = array();
    $options = array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
    $result = sqlsrv_query( $conn, $sql , $params, $options );
    $row_count = sqlsrv_num_rows( $result ). PHP_EOL;
    echo '<br>';
     if ($row_count === false)
     echo "Error in retrieveing row count;
    else {
      echo $row_count;
     }

I can see the lines entered in the database at a particular time with :

$five = "05:";
$six = "06:";
$seven = "07:";


if ($result === false) {
    die(print_r(sqlsrv_errors(), true));
} else {
    while ($row = sqlsrv_fetch_array($result)) {
        $heureformate = $row["dateHeure"]->format('H:i');
        
        if (strpos($heureformate, $five) !== false) {
            echo $heureformate;
            echo '<br>';
            //count(array($heureformate));
        } elseif (strpos($heureformate, $six) !== false) {
            echo $heureformate;
            echo '<br>';
        } elseif (strpos($heureformate, $seven) !== false) {
            echo $heureformate;
            echo '<br>';
        } 
    } 
}

But as I told you, I don't manage to get the number of lines entered per hour and it's this number that interests me to create graphics.

If you have any ideas on how to get this number in SQL or PHP, please help me.

date-arithmetic count sql-server sql php
1 Answers

Do you want aggregation?

select  datepart(hour, t.dateheure), count(*) as cnt
from dbo.tbtransaction t
inner join dbo.tbposte p on t.id_poste = p.id_poste
where p.id_site = 47 and p.nomposte = '0909_client' and t.dateheure >= cast(gedate() as date)
group by datepart(hour, dateheure)

This filters the dataset for rows whose dateheure belongs to the current day, and counts how many records there is in each hour.

Note that I simplified the date filtering logic, and turned the like conditions to equalities (since no wildcards are involved, both are equivalent).

user_106767160
Related