Rob-350472 (4/15/2013)
Indeed, quite why I didn't just do something like this:SELECT
t.ID,
t.Title,
(SELECT count(l.log_id) FROM #logs l WHERE l.id = t.id) as AllTime,
(SELECT count(l.log_id) FROM #logs l WHERE l.id = t.id and l.log_date >= DATEADD(mm, -3, getdate()))as Mnth
FROM #Items t
GROUP BY t.id, t.title
Is beyond me, it's only Monday too!
Probably don't need to use 2 subselects here. You can use your original query.
SELECT
t.ID,
t.Title,
COUNT(l.Log_ID) as AllTime
, (SELECT count(l.log_id) FROM #logs l WHERE l.id = t.id and l.log_date >= DATEADD(mm, -3, getdate()))as Mnth
FROM #Items t
JOIN #logs l on l.ID = t.id
GROUP BY t.id, t.title
You might find that using a CTE for the Months will end up performing a bit better.
Something like this (untested of course because I don't have real data to work with):
;with MonthValues as
(
SELECT count(l.log_id) as Months, ID
FROM #logs l
WHERE l.log_date >= DATEADD(mm, -3, getdate()))
group by ID
)
SELECT
t.ID,
t.Title,
COUNT(l.Log_ID) as AllTime,
mv.Months
FROM #Items t
JOIN #logs l on l.ID = t.id
join MonthValues mv on mv.ID = t.ID
GROUP BY t.id, t.title
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/