Hello. I'm generating a report that could possibly have 500 rows and over 31 columns. Most of the columns represent a single date in a date range that can go up to a month. The rows represent each of the items in the DB with data for all of those dates. The way I have the SQL now can get huge and not very fast or optimized, yet I don't know how to simplify it. Here's what I mean:
As you can see, I can have many more items in the SELECT clause, which is fine, but the LEFT JOINs can also go up to 31 which is just too much to me. Plus the TIDs in the WHERE clause is what can go up to 500 or something (500 numbers in the IN part). That, and if I wanted to add a column total to the very last row returned, I'd have to do a UNION ALL and basically copy the query (with some minor changes) and thus double the entire query's size. There's got to be a better way...
Any suggestions, please? Maybe a stored procedure? Maybe somehow using the ROLLUP clause for the column totals? Thanks!
I'm here to help.
Code:
SELECT a.CID, a.TID, a.Field3, a.Field4, a.Field5, locc.Field6, tmp<date1ofrange>.Balance AS '<date1ofrange>', tmp<date2ofrange>.Balance AS '<date2ofrange>', tmp<date3ofrange>.Balance AS '<date3ofrange>', ...
FROM Term a
LEFT JOIN Business locb ON locb.CID = a.CID AND locb.BID = a.LBID
LEFT JOIN Contact locc ON locc.CID = locb.CID AND locc.CID = locb.CID
JOIN TStatus b on a.TID = b.TID and b.ftrxtime <= '<endofdaterange>'
LEFT JOIN (SELECT Sdate, TID, Balance FROM TSummary WHERE CID = 10 AND Sdate = '<date1ofrange>') tmp<date1ofrange> ON a.TID = tmp<date1ofrange>.TID
LEFT JOIN (SELECT Sdate, TID, Balance FROM TSummary WHERE CID = 10 AND Sdate = '<date2ofrange>') tmp<date2ofrange> ON a.TID = tmp<date2ofrange>.TID
LEFT JOIN (SELECT Sdate, TID, Balance FROM TSummary WHERE CID = 10 AND Sdate = '<date3ofrange>') tmp<date3ofrange> ON a.TID = tmp<date3ofrange>.TID
...
...
WHERE a.CID = 10 AND a.TID in (10866,12818,13788,15810,16006,19682,...,...)
As you can see, I can have many more items in the SELECT clause, which is fine, but the LEFT JOINs can also go up to 31 which is just too much to me. Plus the TIDs in the WHERE clause is what can go up to 500 or something (500 numbers in the IN part). That, and if I wanted to add a column total to the very last row returned, I'd have to do a UNION ALL and basically copy the query (with some minor changes) and thus double the entire query's size. There's got to be a better way...
Any suggestions, please? Maybe a stored procedure? Maybe somehow using the ROLLUP clause for the column totals? Thanks!
I'm here to help.