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

What environment variables can I use in custom behavior scripts in Xcode 4?

xcode scripting xcode4 behavior

Xcode 4 lets you create custom behaviors that are performed from the Xcode > Behaviors menu, or are triggered by a keyboard shortcut. One of the actions that can be taken is the execution of an

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.