Subquery

Hello,

I'm new to Codeigniter and learn a lot every day
I have a subquery that works perfectly in MSSQL.

Code:

SELECT SUM(Weight) as Total, Recipegroup, Production
FROM (SELECT Rejectspecies, Production, Productionsite, Pullution, Weight, Recipegroup, Recipe, Datetime
FROM dbo.Reject
WHERE (Datetime BETWEEN DATEADD(day, - 7, CONVERT(DATETIME, CONVERT(CHAR(8), GETDATE(), 112) + ' ' + CONVERT(CHAR(8), '15:15:00', 108))) AND
        DATEADD(day, - 7, CONVERT(DATETIME, CONVERT(CHAR(8), GETDATE(), 112) + ' ' + CONVERT(CHAR(8), '23:15:00', 108)))) AND
        (Rejectspecies = 'Diervoeder koek' OR Rejectspecies = 'Diervoerder deeg' OR Rejectspecies = 'Restafval')) AS Totals
GROUP BY Production, Recipegroup

I want this to convert to codeigniet db helper but i wont work.
This is what i have.

PHP Code:

   public function get_reusable_early()
    {
        $strSubQuery = $this->db
            ->select("Rejectspecies, Production, Productionsite, Pullution, Weight, Recipegroup, Recipe, Datetime")
            ->from("dbo.Reject")
            ->where("(Datetime BETWEEN DATEADD(day, - 7, CONVERT(DATETIME, CONVERT(CHAR(8), GETDATE(), 112) + ' ' + CONVERT(CHAR(8), '15:15:00', 108))) AND 
        DATEADD(day, - 7, CONVERT(DATETIME, CONVERT(CHAR(8), GETDATE(), 112) + ' ' + CONVERT(CHAR(8), '23:15:00', 108)))) AND 
        (Rejectspecies = 'Diervoeder koek' OR Rejectspecies = 'Diervoerder deeg' OR Rejectspecies = 'Restafval')")
            ->get_compiled_select();

        $this->db->select('SUM(Weight) as Total, Recipegroup, Production', false);
        $this->db->from($strSubQuery, false);
        $this->db->group_by('Production, Recipegroup');
        $this->db->having('Production', 'Lijn A');
        $query = $this->db->get();

        return $query->result_array();
    } 


But won't work i get the next error.

Code:

A Database Error Occurred
Error Number: 42000/156

[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'FROM'.

SELECT SUM(Weight) as Total, Recipegroup, Production FROM "SELECT" "Rejectspecies", "Production", "Productionsite", "Pullution", "Weight", "Recipegroup", "Recipe", "Datetime" FROM "dbo"."Reject" WHERE (Datetime BETWEEN DATEADD(day, "-" 7, CONVERT(DATETIME, CONVERT(CHAR(8), GETDATE(), 112) + ' ' + CONVERT(CHAR(8), '15:15:00', 108))) AND DATEADD(day, "-" 7, CONVERT(DATETIME, CONVERT(CHAR(8), GETDATE(), 112) + ' ' + CONVERT(CHAR(8), '23:15:00', 108)))) AND ("Rejectspecies" = 'Diervoeder koek' OR "Rejectspecies" = 'Diervoerder deeg' OR "Rejectspecies" = 'Restafval') GROUP BY "Production", "Recipegroup" HAVING "Production" = 'Lijn A'

Filename: C:/inetpub/wwwroot/reject/system/database/DB_driver.php

Line Number: 691

Can anybody help me with the query builder?

Andre
From the Netherlands