question

zillabaug avatar image
zillabaug asked

Can someone show me how to improve this query?

Can someone show me show a better way to improve the query below. I would like to get ride of the sub queries if possible... Thanks. DECLARE @Month AS INT; DECLARE @Day AS INT; SET @Month = 1; --set the day to today SET @Day = 28; SELECT ISNULL(YearTotal.Year, ISNULL(YTD.Year, ISNULL(MonthTotal.Year, MTD.Year))) AS "Year", "Year Total", YTD AS "Year-to-Date", "Month Total", MTD AS "Month-to-Date" FROM (SELECT YEAR(cat_exam_in.ExamDate) AS "Year", COUNT(*) AS "Year Total" FROM cat_exam_in WITH (INDEX(exam_dt_idx, scored_idx)) WHERE Processed_ind <> 'P' GROUP BY YEAR(cat_exam_in.ExamDate)) AS YearTotal FULL OUTER JOIN ((SELECT YEAR(cat_exam_in.ExamDate) AS "Year", COUNT(*) AS "YTD" FROM cat_exam_in WHERE MONTH(cat_exam_in.ExamDate) * 100 + DAY(cat_exam_in.ExamDate) <= @Month * 100 + @Day AND Processed_ind <> 'P' GROUP BY YEAR(cat_exam_in.ExamDate)) AS YTD FULL OUTER JOIN ((SELECT YEAR(cat_exam_in.ExamDate) AS "Year", COUNT(*) AS "Month Total" FROM cat_exam_in WHERE MONTH(cat_exam_in.ExamDate) = @Month AND Processed_ind <> 'P' GROUP BY YEAR(cat_exam_in.ExamDate)) AS MonthTotal FULL OUTER JOIN (SELECT YEAR(cat_exam_in.ExamDate) AS "Year", COUNT(*) AS "MTD" FROM cat_exam_in WHERE MONTH(cat_exam_in.ExamDate) = @Month AND DAY(cat_exam_in.ExamDate) <= @Day AND Processed_ind <> 'P' GROUP BY YEAR(cat_exam_in.ExamDate)) AS MTD ON (MonthTotal.Year = MTD.Year)) ON (YTD.Year = ISNULL(MonthTotal.Year, MTD.Year))) ON (YearTotal.Year = ISNULL(YTD.Year, ISNULL(MonthTotal.Year, MTD.Year))) ORDER BY ISNULL(YearTotal.Year, ISNULL(YTD.Year, ISNULL(MonthTotal.Year, MTD.Year)));
tsqlsubquery
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
David Wimbush avatar image
David Wimbush answered
I think this should do the same thing. It's still got a subquery but it's easier to read. declare @Month as int = month(getdate()); declare @Day as int = day(getdate()); select y , sum(YearTotal) as YearTotal , sum(YearToDate) as YearToDate , sum(MonthTotal) as MonthTotal , sum(MonthToDate) as MonthToDate from ( select y , 1 as YearTotal , case when m < @Month or (m = @Month and d 'p' ) a ) b group by y order by y; (Thanks to Scott for extreme programming this with me!)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

That worked and looks a lot cleaner and readable. Thanks David and Scott.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.