Need help condensing sql server query using local variables. Also need hints for accounting for leap year

I recently switched jobs and therefore also switched from oracle to sql server. I'm attempting to write a stored procedure that will generate a report on demand. The report will show monthly amounts and the sum of these amounts. I am mostly having trouble with the summing part of this. The full query is at the very bottom of this post. As you will see from my super-long query, I've basically written it twice to get the sum amount in the Total_Current_Plus_Archived column. I was thinking of using a local variable for this (@totalReportSum). I wanted to update this variable each time a total column is calculated but get errors no matter what syntax I use. Here are a couple of the things I've tried:

Attempt 1.

--March total   
                     (SELECT @totalReportSum  =@totalReportSum + Count(*) AS Expr1 
                      FROM   dbo.accident_information AS ai 
                             INNER JOIN dbo.municipality AS mx 
                                     ON mx.agency_ori = ai.agency_ori 
                      WHERE  ( mx.ram = m.ram ) 
                             AND ( ai.crash_date >= @year + '-03-01' ) 
                             AND ( ai.crash_date <= @year + '-03-31' )) 
                     AS March_Total_Reports, 

Attempt 2.

--March total   
                 @totalReportSum = (SELECT Count(*) AS Expr1 
                      FROM   dbo.accident_information AS ai 
                             INNER JOIN dbo.municipality AS mx 
                                     ON mx.agency_ori = ai.agency_ori 
                      WHERE  ( mx.ram = m.ram ) 
                             AND ( ai.crash_date >= @year + '-03-01' ) 
                             AND ( ai.crash_date <= @year + '-03-31' )) 
                     AS March_Total_Reports, 

If possible, I would also like suggestions as to how I can account for leap years.

Any help or hints are greatly appreciated. Thanks.

CREATE PROCEDURE Sp_get_ram_report @year NCHAR(4) 
AS 
BEGIN--declare variable to hold total sum of reports 
  DECLARE @totalReportSum INT; 

  --initialize sum 
  SET @totalReportSum = 0; 
END 

SELECT TOP (100) PERCENT r.ramid 
                     AS Ram_ID, 
                     r.ram_fname 
                     AS RAM_First_Name, 
                     r.ram_lname 
                     AS RAM_Last_Name, 
                     Count(*) 
                     AS Number_of_Agencies, 
                     Isnull(Sum(m.yearly_avg_reports), 0) 
                     AS Yearly_Avg#_of_reports, 
                     Round(Isnull(Sum(m.yearly_avg_reports), 0) / 12, 2) 
                     AS Monthly_Reports_Expected, 
                     --January 
                     (SELECT Count(*) AS Expr1 
                      FROM   dbo.accident_information AS ai 
                             INNER JOIN dbo.municipality AS mx 
                                     ON mx.agency_ori = ai.agency_ori 
                      WHERE  ( mx.ram = m.ram ) 
                             AND ( ai.crash_date >= @year + '-01-01' ) 
                             AND ( ai.crash_date <= @year + '-01-31' ) 
                             AND ( ai.insert_datetime - ai.crash_date < 35 ) 
                     ) AS January, 
                     --January total   
                     (SELECT Count(*) AS Expr1 
                      FROM   dbo.accident_information AS ai 
                             INNER JOIN dbo.municipality AS mx 
                                     ON mx.agency_ori = ai.agency_ori 
                      WHERE  ( mx.ram = m.ram ) 
                             AND ( ai.crash_date >= @year + '-01-01' ) 
                             AND ( ai.crash_date <= @year + '-01-31' )) 
                     AS January_Total_Reports, 
                     --February 
                     (SELECT Count(*) AS Expr1 
                      FROM   dbo.accident_information AS ai 
                             INNER JOIN dbo.municipality AS mx 
                                     ON mx.agency_ori = ai.agency_ori 
                      WHERE  ( mx.ram = m.ram ) 
                             AND ( ai.crash_date >= @year + '-02-01' ) 
                             AND ( ai.crash_date <= @year + '-02-28' ) 
                             AND ( ai.insert_datetime - ai.crash_date < 35 ) 
                     ) AS February, 
                     --February total   
                     (SELECT Count(*) AS Expr1 
                      FROM   dbo.accident_information AS ai 
                             INNER JOIN dbo.municipality AS mx 
                                     ON mx.agency_ori = ai.agency_ori 
                      WHERE  ( mx.ram = m.ram ) 
                             AND ( ai.crash_date >= @year + '-02-01' ) 
                             AND ( ai.crash_date <= @year + '-02-28' )) 
                     AS February_Total_Reports, 
                     --March 
                     (SELECT Count(*) AS Expr1 
                      FROM   dbo.accident_information AS ai 
                             INNER JOIN dbo.municipality AS mx 
                                     ON mx.agency_ori = ai.agency_ori 
                      WHERE  ( mx.ram = m.ram ) 
                             AND ( ai.crash_date >= @year + '-03-01' ) 
                             AND ( ai.crash_date <= @year + '-03-31' ) 
                             AND ( ai.insert_datetime - ai.crash_date < 35 ) 
                     ) AS March, 
                     --March total   
                 (SELECT Count(*) AS Expr1 
                      FROM   dbo.accident_information AS ai 
                             INNER JOIN dbo.municipality AS mx 
                                     ON mx.agency_ori = ai.agency_ori 
                      WHERE  ( mx.ram = m.ram ) 
                             AND ( ai.crash_date >= @year + '-03-01' ) 
                             AND ( ai.crash_date <= @year + '-03-31' )) 
                     AS March_Total_Reports, 
                     --April 
                     (SELECT Count(*) AS Expr1 
                      FROM   dbo.accident_information AS ai 
                             INNER JOIN dbo.municipality AS mx 
                                     ON mx.agency_ori = ai.agency_ori 
                      WHERE  ( mx.ram = m.ram ) 
                             AND ( ai.crash_date >= @year + '-04-01' ) 
                             AND ( ai.crash_date <= @year + '-04-30' ) 
                             AND ( ai.insert_datetime - ai.crash_date < 35 ) 
                     ) AS April, 
                     --April total   
                     (SELECT Count(*) AS Expr1 
                      FROM   dbo.accident_information AS ai 
                             INNER JOIN dbo.municipality AS mx 
                                     ON mx.agency_ori = ai.agency_ori 
                      WHERE  ( mx.ram = m.ram ) 
                             AND ( ai.crash_date >= @year + '-04-01' ) 
                             AND ( ai.crash_date <= @year + '-04-30' )) 
                     AS April_Total_Reports, 
                     --May 
                     (SELECT Count(*) AS Expr1 
                      FROM   dbo.accident_information AS ai 
                             INNER JOIN dbo.municipality AS mx 
                                     ON mx.agency_ori = ai.agency_ori 
                      WHERE  ( mx.ram = m.ram ) 
                             AND ( ai.crash_date >= @year + '-05-01' ) 
                             AND ( ai.crash_date <= @year + '-05-31' ) 
                             AND ( ai.insert_datetime - ai.crash_date < 35 ) 
                     ) AS May, 
                     --May total   
                     (SELECT Count(*) AS Expr1 
                      FROM   dbo.accident_information AS ai 
                             INNER JOIN dbo.municipality AS mx 
                                     ON mx.agency_ori = ai.agency_ori 
                      WHERE  ( mx.ram = m.ram ) 
                             AND ( ai.crash_date >= @year + '-05-01' ) 
                             AND ( ai.crash_date <= @year + '-05-31' )) 
                     AS May_Total_Reports, 
                     --June 
                     (SELECT Count(*) AS Expr1 
                      FROM   dbo.accident_information AS ai 
                             INNER JOIN dbo.municipality AS mx 
                                     ON mx.agency_ori = ai.agency_ori 
                      WHERE  ( mx.ram = m.ram ) 
                             AND ( ai.crash_date >= @year + '-06-01' ) 
                             AND ( ai.crash_date <= @year + '-06-30' ) 
                             AND ( ai.insert_datetime - ai.crash_date < 35 ) 
                     ) AS June, 
                     --June total   
                     (SELECT Count(*) AS Expr1 
                      FROM   dbo.accident_information AS ai 
                             INNER JOIN dbo.municipality AS mx 
                                     ON mx.agency_ori = ai.agency_ori 
                      WHERE  ( mx.ram = m.ram ) 
                             AND ( ai.crash_date >= @year + '-06-01' ) 
                             AND ( ai.crash_date <= @year + '-06-30' )) 
                     AS June_Total_Reports, 
                     --July 
                     (SELECT Count(*) AS Expr1 
                      FROM   dbo.accident_information AS ai 
                             INNER JOIN dbo.municipality AS mx 
                                     ON mx.agency_ori = ai.agency_ori 
                      WHERE  ( mx.ram = m.ram ) 
                             AND ( ai.crash_date >= @year + '-07-01' ) 
                             AND ( ai.crash_date <= @year + '-07-31' ) 
                             AND ( ai.insert_datetime - ai.crash_date < 35 ) 
                     ) AS July, 
                     --July total   
                     (SELECT Count(*) AS Expr1 
                      FROM   dbo.accident_information AS ai 
                             INNER JOIN dbo.municipality AS mx 
                                     ON mx.agency_ori = ai.agency_ori 
                      WHERE  ( mx.ram = m.ram ) 
                             AND ( ai.crash_date >= @year + '-07-01' ) 
                             AND ( ai.crash_date <= @year + '-07-31' )) 
                     AS July_Total_Reports, 
                     --august 
                     (SELECT Count(*) AS Expr1 
                      FROM   dbo.accident_information AS ai 
                             INNER JOIN dbo.municipality AS mx 
                                     ON mx.agency_ori = ai.agency_ori 
                      WHERE  ( mx.ram = m.ram ) 
                             AND ( ai.crash_date >= @year + '-08-01' ) 
                             AND ( ai.crash_date <= @year + '-08-31' ) 
                             AND ( ai.insert_datetime - ai.crash_date < 35 ) 
                     ) AS August, 
                     --august total   
                     (SELECT Count(*) AS Expr1 
                      FROM   dbo.accident_information AS ai 
                             INNER JOIN dbo.municipality AS mx 
                                     ON mx.agency_ori = ai.agency_ori 
                      WHERE  ( mx.ram = m.ram ) 
                             AND ( ai.crash_date >= @year + '-08-01' ) 
                             AND ( ai.crash_date <= @year + '-08-31' )), 
                     --september 
                     (SELECT Count(*) AS Expr1 
                      FROM   dbo.accident_information AS ai 
                             INNER JOIN dbo.municipality AS mx 
                                     ON mx.agency_ori = ai.agency_ori 
                      WHERE  ( mx.ram = m.ram ) 
                             AND ( ai.crash_date >= @year + '-09-01' ) 
                             AND ( ai.crash_date <= @year + '-09-30' ) 
                             AND ( ai.insert_datetime - ai.crash_date < 35 ) 
                     ) AS September, 
                     --September Total 
                     (SELECT Count(*) AS Expr1 
                      FROM   dbo.accident_information AS ai 
                             INNER JOIN dbo.municipality AS mx 
                                     ON mx.agency_ori = ai.agency_ori 
                      WHERE  ( mx.ram = m.ram ) 
                             AND ( ai.crash_date >= @year + '-09-01' ) 
                             AND ( ai.crash_date <= @year + '-09-30' )) 
                     AS September_Total_Reports, 
                     --October 
                     (SELECT Count(*) AS Expr1 
                      FROM   dbo.accident_information AS ai 
                             INNER JOIN dbo.municipality AS mx 
                                     ON mx.agency_ori = ai.agency_ori 
                      WHERE  ( mx.ram = m.ram ) 
                             AND ( ai.crash_date >= @year + '-10-01' ) 
                             AND ( ai.crash_date <= @year + '-10-31' ) 
                             AND ( ai.insert_datetime - ai.crash_date < 35 ) 
                     ) AS October, 
                     --october total 
                     (SELECT Count(*) AS Expr1 
                      FROM   dbo.accident_information AS ai 
                             INNER JOIN dbo.municipality AS mx 
                                     ON mx.agency_ori = ai.agency_ori 
                      WHERE  ( mx.ram = m.ram ) 
                             AND ( ai.crash_date >= @year + '-10-01' ) 
                             AND ( ai.crash_date <= @year + '-10-31' )) 
                     AS October_Total_Reports, 
                     --november 
                     (SELECT Count(*) AS Expr1 
                      FROM   dbo.accident_information AS ai 
                             INNER JOIN dbo.municipality AS mx 
                                     ON mx.agency_ori = ai.agency_ori 
                      WHERE  ( mx.ram = m.ram ) 
                             AND ( ai.crash_date >= @year + '-11-01' ) 
                             AND ( ai.crash_date <= @year + '-11-30' ) 
                             AND ( ai.insert_datetime - ai.crash_date < 35 ) 
                     ) AS November, 
                     --november total 
                     (SELECT Count(*) 
                      FROM   dbo.accident_information AS ai 
                             INNER JOIN dbo.municipality AS mx 
                                     ON mx.agency_ori = ai.agency_ori 
                      WHERE  ( mx.ram = m.ram ) 
                             AND ( ai.crash_date >= @year + '-11-01' ) 
                             AND ( ai.crash_date <= @year + '-11-30' )) 
                     AS November_Total_Reports, 
                     --December 
                     (SELECT Count(*) AS Expr1 
                      FROM   dbo.accident_information AS ai 
                             INNER JOIN dbo.municipality AS mx 
                                     ON mx.agency_ori = ai.agency_ori 
                      WHERE  ( mx.ram = m.ram ) 
                             AND ( ai.crash_date >= @year + '-12-01' ) 
                             AND ( ai.crash_date <= @year + '-12-31' ) 
                             AND ( ai.insert_datetime - ai.crash_date < 35 ) 
                     ) AS December, 
                     --December Total 
                     (SELECT Count(*) 
                      FROM   dbo.accident_information AS ai 
                             INNER JOIN dbo.municipality AS mx 
                                     ON mx.agency_ori = ai.agency_ori 
                      WHERE  ( mx.ram = m.ram ) 
                             AND ( ai.crash_date >= @year + '-12-01' ) 
                             AND ( ai.crash_date <= @year + '-12-31' )) 
                     AS December_Total_Reports, 
      --Total Current +archived: Would like to replace this huge chunk w/ something a lot smaller 
                     ( (SELECT Count(*) AS Expr1 
                        FROM   dbo.accident_information AS ai 
                               INNER JOIN dbo.municipality AS mx 
                                       ON mx.agency_ori = ai.agency_ori 
                        WHERE  ( mx.ram = m.ram ) 
                               AND ( ai.crash_date >= @year + '-01-01' ) 
                               AND ( ai.crash_date <= @year + '-01-31' )) 
                       + (SELECT Count(*) AS Expr1 
                          FROM   dbo.accident_information AS ai 
                                 INNER JOIN dbo.municipality AS mx 
                                         ON mx.agency_ori = ai.agency_ori 
                          WHERE  ( mx.ram = m.ram ) 
                                 AND ( ai.crash_date >= @year + '-02-01' ) 
                                 AND ( ai.crash_date <= @year + '-02-28' )) 
                       + (SELECT Count(*) AS Expr1 
                          FROM   dbo.accident_information AS ai 
                                 INNER JOIN dbo.municipality AS mx 
                                         ON mx.agency_ori = ai.agency_ori 
                          WHERE  ( mx.ram = m.ram ) 
                                 AND ( ai.crash_date >= @year + '-03-01' ) 
                                 AND ( ai.crash_date <= @year + '-03-31' )) 
                       + (SELECT Count(*) AS Expr1 
                          FROM   dbo.accident_information AS ai 
                                 INNER JOIN dbo.municipality AS mx 
                                         ON mx.agency_ori = ai.agency_ori 
                          WHERE  ( mx.ram = m.ram ) 
                                 AND ( ai.crash_date >= @year + '-04-01' ) 
                                 AND ( ai.crash_date <= @year + '-04-30' )) 
                       + (SELECT Count(*) AS Expr1 
                          FROM   dbo.accident_information AS ai 
                                 INNER JOIN dbo.municipality AS mx 
                                         ON mx.agency_ori = ai.agency_ori 
                          WHERE  ( mx.ram = m.ram ) 
                                 AND ( ai.crash_date >= @year + '-05-01' ) 
                                 AND ( ai.crash_date <= @year + '-05-31' )) 
                       + (SELECT Count(*) AS Expr1 
                          FROM   dbo.accident_information AS ai 
                                 INNER JOIN dbo.municipality AS mx 
                                         ON mx.agency_ori = ai.agency_ori 
                          WHERE  ( mx.ram = m.ram ) 
                                 AND ( ai.crash_date >= @year + '-06-01' ) 
                                 AND ( ai.crash_date <= @year + '-06-30' )) 
                       + (SELECT Count(*) AS Expr1 
                          FROM   dbo.accident_information AS ai 
                                 INNER JOIN dbo.municipality AS mx 
                                         ON mx.agency_ori = ai.agency_ori 
                          WHERE  ( mx.ram = m.ram ) 
                                 AND ( ai.crash_date >= @year + '-07-01' ) 
                                 AND ( ai.crash_date <= @year + '-07-31' )) 
                       + (SELECT Count(*) AS Expr1 
                          FROM   dbo.accident_information AS ai 
                                 INNER JOIN dbo.municipality AS mx 
                                         ON mx.agency_ori = ai.agency_ori 
                          WHERE  ( mx.ram = m.ram ) 
                                 AND ( ai.crash_date >= @year + '-08-01' ) 
                                 AND ( ai.crash_date <= @year + '-08-31' )) 
                       + (SELECT Count(*) AS Expr1 
                          FROM   dbo.accident_information AS ai 
                                 INNER JOIN dbo.municipality AS mx 
                                         ON mx.agency_ori = ai.agency_ori 
                          WHERE  ( mx.ram = m.ram ) 
                                 AND ( ai.crash_date >= @year + '-09-01' ) 
                                 AND ( ai.crash_date <= @year + '-09-30' )) 
                       + (SELECT Count(*) AS Expr1 
                          FROM   dbo.accident_information AS ai 
                                 INNER JOIN dbo.municipality AS mx 
                                         ON mx.agency_ori = ai.agency_ori 
                          WHERE  ( mx.ram = m.ram ) 
                                 AND ( ai.crash_date >= @year + '-10-01' ) 
                                 AND ( ai.crash_date <= @year + '-10-31' )) 
                       + (SELECT Count(*) 
                          FROM   dbo.accident_information AS ai 
                                 INNER JOIN dbo.municipality AS mx 
                                         ON mx.agency_ori = ai.agency_ori 
                          WHERE  ( mx.ram = m.ram ) 
                                 AND ( ai.crash_date >= @year + '-11-01' ) 
                                 AND ( ai.crash_date <= @year + '-11-30' )) 
                       + (SELECT Count(*) 
                          FROM   dbo.accident_information AS ai 
                                 INNER JOIN dbo.municipality AS mx 
                                         ON mx.agency_ori = ai.agency_ori 
                          WHERE  ( mx.ram = m.ram ) 
                                 AND ( ai.crash_date >= @year + '-12-01' ) 
                                 AND ( ai.crash_date <= @year + '-12-31' )) 
                     ) AS 
                     Total_Current_Plus_Archived 
FROM   dbo.municipality AS m 
   INNER JOIN dbo.ram AS r 
           ON m.ram = r.ramid 
GROUP  BY m.ram, 
      r.ramid, 
      r.ram_fname, 
      r.ram_lname 
ORDER  BY ram_last_name 

Answers


Just an idea, I would like query this way. Then, you don't need care about leap years

;WITH monthly AS
(
    SELECT 
        mx.ram 
        ,DATEPART(month,crash_date) AS report_month
        ,SUM(CASE WHEN ai.insert_datetime - ai.crash_date < 35 THEN 1 ELSE 0 END) AS Expr1
        ,Count(*) AS Expr1_total
    FROM   dbo.accident_information AS ai 
    INNER JOIN dbo.municipality AS mx 
        ON mx.agency_ori = ai.agency_ori 
    WHERE DATEPART(year,crash_date) = @year             
    GROUP BY mx.ram,DATEPART(month,crash_date)
)                 
SELECT
    r.ramid AS Ram_ID, 
    r.ram_fname AS RAM_First_Name,
    ...
    ,(SELECT Expr1 FROM monthly mt WHERE mt.ram = m.ram AND report_month = 1) AS January
    ,(SELECT Expr1_total FROM monthly mt WHERE mt.ram = m.ram AND report_month = 1) AS  January_Total_Reports
    ,(SELECT Expr1 FROM monthly mt WHERE  mt.ram = m.ram AND report_month = 2) AS February
    ,(SELECT Expr1_total FROM monthly mt WHERE mt.ram = m.ram AND report_month = 2) AS  February_Total_Reports
    ...
    ,(SELECT SUM(Expr1_total) FROM monthly mt WHERE mt.ram = m.ram) AS Total_Current_Plus_Archived 
FROM   dbo.municipality AS m 
INNER JOIN dbo.ram AS r 
    ON m.ram = r.ramid         
GROUP  BY m.ram, 
      r.ramid, 
      r.ram_fname, 
      r.ram_lname 
ORDER  BY ram_last_name         

Need Your Help

C Pipe communicating write/read function cuts of text

c pipe communication

The write or read function always cut of everything except the first letter. anyone knows why?

Image upload creating problems

php mysql html5

iam new to php, i am using image upload script of php to update my logo, whenever user choose the image file the existing image file should be replaced by the new upoaded file and should be updated...

How to use cancellationToken in MVC3?

c# asp.net-mvc multithreading asp.net-mvc-3 asynchronous

I am newbie to MVC3. In my project MVC3, i'm using threading for import excel

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.