需要使用局部变量来压缩sql server查询的帮助。我还需要一些关于计算闰年的提示。
需要使用局部变量来压缩sql server查询的帮助。我还需要一些关于计算闰年的提示。
提问于 2012-09-11 00:03:51
我最近切换了工作，因此也从oracle切换到了sql server。我正在尝试编写一个按需生成报告的存储过程。报表将显示每月的金额以及这些金额的总和。我主要是在这部分的求和方面遇到了麻烦。完整的问题在这篇文章的最下面。正如您将从我的超长查询中看到的那样，我基本上已经编写了两次，以获得Total_Current_Plus_Archived列中的金额。为此，我正在考虑使用一个本地变量(@totalReportSum)。我想在每次计算总列数时更新这个变量，但无论使用什么语法都会出现错误。下面是我尝试过的几个方法：
尝试% 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, 尝试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, 如果可能的话，我也想要一些关于我如何解释闰年的建议。
任何帮助或提示都是非常感谢的。谢谢。
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 回答 1
Stack Overflow用户
回答已采纳
发布于 2012-09-11 01:44:13
这只是一个想法，我想这样查询。然后，你就不需要关心闰年了
;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 页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/12355395
复制相关文章
相似问题
腾讯云开发者
