asked
Better way of doing the following script
Hi guys, I have a script that determines the count of incidents every month and run a total for the year and total for all years. I want to see if there is a better way of doing so from the SQL GURUS out there. Here is one of the years pasted down below and I keep repeating the same for every year. Thanks SELECT COUNT([Incident #]) FROM _SMDBA_.Incident WHERE _SMDBA_.Incident.[Open Date & Time] between '01/01/2014' and '01/31/2014' ) AS 'Total Incident for January 2014', ( SELECT COUNT([Incident #]) FROM _SMDBA_.Incident WHERE _SMDBA_.Incident.[Open Date & Time] between '02/01/2014' and '02/28/2014' ) AS 'Total Incident for February 2014', ( SELECT COUNT([Incident #]) FROM _SMDBA_.Incident WHERE _SMDBA_.Incident.[Open Date & Time] between '03/01/2014' and '03/31/2014' ) AS 'Total Incident for March 2014', ( SELECT COUNT([Incident #]) FROM _SMDBA_.Incident WHERE _SMDBA_.Incident.[Open Date & Time] between '04/01/2014' and '04/30/2014' ) AS 'Total Incident for April 2014', ( SELECT COUNT([Incident #]) FROM _SMDBA_.Incident WHERE _SMDBA_.Incident.[Open Date & Time] between '01/01/2014' and '12/31/2014' ) AS 'Total For Year 2014'
Would something like this help? SELECT 'Monthly Total', CAST(MONTH([Open Date & Time]) AS VARCHAR(2))+ '/' + CAST(YEAR([Open Date & Time]) AS VARCHAR(4)) AS 'Date', COUNT(1) AS 'Total for the Month' FROM _SMDBA_.Incident.[Open Date & Time] GROUP BY CAST(MONTH([Open Date & Time]) AS VARCHAR(2)) + '/' + cast(YEAR([Open Date & Time]) AS varchar(4)) UNION SELECT 'Year Total', CAST(YEAR(SMDBA_.Incident.[Open Date & Time]) AS VARCHAR(4)), COUNT(1) FROM _SMDBA_.Incident.[Open Date & Time] GROUP BY YEAR(_SMDBA_.Incident.[Open Date & Time]) I used some data that I have that has dates in it and it seemed to function as I expected.
15 People are following this question.
