2.0.0.0 Selfserving .NET 2.0 SQL Server 2005

I need to convert this query to llblgen

SELECT 
    A.TrackingId
    ,[more fields here, not important]
    ,ISNULL(OP,0) AS 'OP'
FROM vwOrderTracking A
    INNER JOIN OrderTracking_OrderLog B ON  A.TrackingId = B.TrackingId
    LEFT JOIN (SELECT TrackingId,COUNT(*) AS 'OP' FROM OrderTracking_OrderProblems WHERE ResolvedYn = 0 GROUP BY TrackingId) C ON  A.TrackingId = C.TrackingId
WHERE  RepId = 1408
    AND (DATEADD(DAY,15,B.CreatedOn) >= GETDATE()
        OR OP > 0
        OR OrderEditStatusId = 0
        OR MaStatusId = 0
        OR LeasingStatusId = 0
        OR BillingStatusId = 0
        OR CompStatusId = 0)

I can use a ScalarQueryExpression to move the derived table into the select part but I also need that as part of my where clause and I read doing it the same way isn't a good idea.

Hi TogasPoon. Is there the possibility to GROUP the whole query so you can join normally to OrderTracking_OrderProblems, include the count at the select and filter OP at HAVING clause?

The Sql query works

SELECT 
    A.TrackingId
    ,CustName
    ,Address
    ,City
    ,State
    ,Zip
    ,RepId
    ,FullName
    ,EquipId
    ,Serial
    ,ProductName
    ,Type
    ,MaStatusId
    ,LeasingStatusId
    ,BillingStatusId
    ,CompStatusId
    ,SUM(CASE WHEN ResolvedYn = 0 THEN 1 ELSE 0 END) AS 'OP'
FROM vwOrderTracking A
    INNER JOIN OrderTracking_OrderLog B ON  A.TrackingId = B.TrackingId
    LEFT JOIN OrderTracking_OrderProblems C ON  A.TrackingId = C.TrackingId
WHERE  RepId = 1408
GROUP BY A.TrackingId,CustName
    ,Address
    ,City
    ,State
    ,Zip
    ,RepId
    ,FullName
    ,EquipId
    ,Serial
    ,ProductName
    ,Type
    ,OrderEditStatusId
    ,MaStatusId
    ,LeasingStatusId
    ,BillingStatusId
    ,CompStatusId
    ,B.CreatedOn
HAVING (SUM(CASE WHEN ResolvedYn = 0 THEN 1 ELSE 0 END) > 0
        OR (DATEADD(DaY,15,B.CreatedOn) >= GETDATE())
        OR OrderEditStatusId = 0
        OR MaStatusId = 0
        OR LeasingStatusId = 0
        OR BillingStatusId = 0
        OR CompStatusId = 0)

Good, so I guess you have succeeded in formulating it with LLBLGen code.

Nope, but I haven't tried yet.