both smss and dbForge have no problem diagraming it.
I’ve converted the e.programid = 6 to e.programid in (6), but it doesn’t help toad.
toad will run the query just fine, but it seems to be more quirky about diagrams than its competition.
i’m not defending the query. it’s a pos, but it’s what i’ve got to work with.
thanks
SELECT TOP (100) PERCENT
DATEADD (dd
, DATEDIFF (dd
, 0
, p.TransDate)
, 0)
AS PayDate
, c.idAgent
, e.status AS Program
, SUM (CASE WHEN ProgramName = ‘xxx’ THEN AmountPaid ELSE 0 END)
AS Enrollment
, SUM (CASE WHEN ProgramName = ‘yyy’ THEN AmountPaid ELSE 0 END)
AS Access
, SUM (
CASE
WHEN ProgramName = ‘xxx’ THEN 0
WHEN ProgramName = ‘yyy’ THEN 0
ELSE AmountPaid
END)
AS Other
FROM dbo.Payments AS p
INNER JOIN dbo.Orders AS o ON p.idOrder = o.idOrder
LEFT OUTER JOIN dbo.Products AS pr ON o.idProduct = pr.idProduct
LEFT OUTER JOIN dbo.enrollments AS e
ON p.idCustomer = e.studentid AND e.programid = 6
LEFT OUTER JOIN dbo.FVHSStatusFlag AS fs ON e.status = fs.idStatus
LEFT OUTER JOIN dbo.Customers AS c ON p.idCustomer = c.idCustomer
WHERE (p.ResponseCode = 1)
GROUP BY DATEADD (dd
, DATEDIFF (dd
, 0
, p.TransDate)
, 0)
, c.idAgent
, e.status
ORDER BY PayDate
