question

kkhelif avatar image
kkhelif 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'
sqlsql server 2014count
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

·
JohnM avatar image
JohnM answered
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.
2 comments
10 |1200

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

kkhelif avatar image kkhelif commented ·
John, That worked great. Had to shift things around but that's better than what I had. Thanks man.
0 Likes 0 ·
JohnM avatar image JohnM kkhelif commented ·
Excellent! Glad it helped!
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.