按月比较产品销售额的 SQL 查询 [英] SQL query to compare product sales by month
问题描述
我有一个每月状态数据库视图，我需要基于它来构建报告.视图中的数据如下所示:
类别 |收入 |年 |月自行车 10 000 2008 1自行车 12 000 2008 2自行车 12 000 2008 3自行车 15 000 2008 1自行车 11 000 2007 2自行车 11 500 2007 3自行车 15 400 2007 4
... 等等
该视图具有产品类别、收入、年份和月份.我想创建一个比较 2007 年和 2008 年的报告，在没有销售的月份显示 0.所以报告应该是这样的:
类别 |月 |牧师今年|牧师去年自行车 1 10 000 0自行车 2 12 000 11 000自行车 3 12 000 11 500自行车 4 0 15 400
需要注意的关键是第 1 个月只有 2008 年的销售额，因此 2007 年为 0.此外，第 4 个月只有 2008 年没有销售额，因此为 0，而它在 2007 年有销售额并且仍然出现.
此外，该报告实际上是针对财政年度的 - 因此，如果 2007 年或 2008 年的第 5 个月没有销售，我希望在两个列中都有 0 的空列.
我得到的查询看起来像这样:
SELECTSP1.程序，SP1.年，SP1.月，SP1.总收入，IsNull(SP2.TotalRevenue, 0) AS LastYearTotalRevenue来自 PVMonthlyStatusReport AS SP1LEFT OUTER JOIN PVMonthlyStatusReport AS SP2 ONSP1.Program = SP2.Program ANDSP2.Year = SP1.Year - 1 ANDSP1.Month = SP2.Month在哪里SP1.Program = '自行车' ANDSP1.Category = @Category AND(SP1.Year >= @FinancialYear AND SP1.Year <= @FinancialYear + 1) AND((SP1.Year = @FinancialYear AND SP1.Month > 6) 或(SP1.Year = @FinancialYear + 1 AND SP1.Month <= 6))按 SP1.Year, SP1.Month 排序这个查询的问题是它不会返回我上面示例数据中的第四行，因为我们在 2008 年没有任何销售额，但实际上我们在 2007 年有.
这可能是一个常见的查询/问题，但是我的SQL在做了这么长时间的前端开发之后生锈了.非常感谢任何帮助！
哦，顺便说一句，我使用 SQL 2005 进行此查询，所以如果有任何有用的新功能可以帮助我知道.
Case Statement 是我最好的 sql 朋友.您还需要一个时间表来在两个月内生成 0 转.
假设基于下表的可用性:
<块引用>销售:类别|收入 |年 |月
和
<块引用>tm: 年 |月(填充所有报告所需的日期)
示例 1 没有空行:
选择类别，月,SUM(CASE WHEN YEAR = 2008 THEN Revenue ELSE 0 END) this_year,SUM(CASE WHEN YEAR = 2007 THEN Revenue ELSE 0 END) last_year从销售量在哪里年 (2008,2007)通过...分组类别，月返回:
类别 |月 |牧师今年|牧师去年自行车 1 10 000 0自行车 2 12 000 11 000自行车 3 12 000 11 500自行车 4 0 15 400带有空行的示例 2:我将使用子查询(但其他人可能不会)，并将为每个产品和年月组合返回一个空行.
选择填充.类别,fill.month,SUM(CASE WHEN YEAR = 2008 THEN Revenue ELSE 0 END) this_year,SUM(CASE WHEN YEAR = 2007 THEN Revenue ELSE 0 END) last_year从销售量右连接(选择不同的 -- 尝试左连接、右连接和交叉连接来测试结果.产品，年，月从销售 - 理想情况下，这将来自产品表交叉连接 tm在哪里年份 (2008,2007)) 填写在哪里填写年份(2008、2007)通过...分组填充.类别,fill.month返回:
类别 |月 |牧师今年|牧师去年自行车 1 10 000 0自行车 2 12 000 11 000自行车 3 12 000 11 500自行车 4 0 15 400自行车 5 0 0自行车 6 0 0自行车 7 0 0自行车 8 0 0请注意，大多数报告工具都会执行此交叉表或矩阵功能，现在我认为 SQL Server 2005 的数据透视语法也可以执行此操作.
这里有一些额外的资源.案件http://www.4guysfromrolla.com/webtech/102704-1.shtmlSQL SERVER 2005 枢轴http://msdn.microsoft.com/en-us/library/ms177410.aspx
I have a Monthly Status database view I need to build a report based on. The data in the view looks something like this:
Category | Revenue | Yearh | Month
Bikes 10 000 2008 1
Bikes 12 000 2008 2
Bikes 12 000 2008 3
Bikes 15 000 2008 1
Bikes 11 000 2007 2
Bikes 11 500 2007 3
Bikes 15 400 2007 4
... And so forth
The view has a product category, a revenue, a year and a month. I want to create a report comparing 2007 and 2008, showing 0 for the months with no sales. So the report should look something like this:
Category | Month | Rev. This Year | Rev. Last Year
Bikes 1 10 000 0
Bikes 2 12 000 11 000
Bikes 3 12 000 11 500
Bikes 4 0 15 400
The key thing to notice is how month 1 only has sales in 2008, and therefore is 0 for 2007. Also, month 4 only has no sales in 2008, hence the 0, while it has sales in 2007 and still show up.
Also, the report is actually for financial year - so I would love to have empty columns with 0 in both if there was no sales in say month 5 for either 2007 or 2008.
The query I got looks something like this:
SELECT
SP1.Program,
SP1.Year,
SP1.Month,
SP1.TotalRevenue,
IsNull(SP2.TotalRevenue, 0) AS LastYearTotalRevenue
FROM PVMonthlyStatusReport AS SP1
LEFT OUTER JOIN PVMonthlyStatusReport AS SP2 ON
SP1.Program = SP2.Program AND
SP2.Year = SP1.Year - 1 AND
SP1.Month = SP2.Month
WHERE
SP1.Program = 'Bikes' AND
SP1.Category = @Category AND
(SP1.Year >= @FinancialYear AND SP1.Year <= @FinancialYear + 1) AND
((SP1.Year = @FinancialYear AND SP1.Month > 6) OR
(SP1.Year = @FinancialYear + 1 AND SP1.Month <= 6))
ORDER BY SP1.Year, SP1.Month
The problem with this query is that it would not return the fourth row in my example data above, since we didn't have any sales in 2008, but we actually did in 2007.
This is probably a common query/problem, but my SQL is rusty after doing front-end development for so long. Any help is greatly appreciated!
Oh, btw, I'm using SQL 2005 for this query so if there are any helpful new features that might help me let me know.
The Case Statement is my best sql friend. You also need a table for time to generate your 0 rev in both months.
Assumptions are based on the availability of following tables:
sales: Category | Revenue | Yearh | Month
and
tm: Year | Month (populated with all dates required for reporting)
Example 1 without empty rows:
select
Category
,month
,SUM(CASE WHEN YEAR = 2008 THEN Revenue ELSE 0 END) this_year
,SUM(CASE WHEN YEAR = 2007 THEN Revenue ELSE 0 END) last_year
from
sales
where
year in (2008,2007)
group by
Category
,month
RETURNS:
Category | Month | Rev. This Year | Rev. Last Year
Bikes 1 10 000 0
Bikes 2 12 000 11 000
Bikes 3 12 000 11 500
Bikes 4 0 15 400
Example 2 with empty rows: I am going to use a sub query (but others may not) and will return an empty row for every product and year month combo.
select
fill.Category
,fill.month
,SUM(CASE WHEN YEAR = 2008 THEN Revenue ELSE 0 END) this_year
,SUM(CASE WHEN YEAR = 2007 THEN Revenue ELSE 0 END) last_year
from
sales
Right join (select distinct --try out left, right and cross joins to test results.
product
,year
,month
from
sales --this ideally would be from a products table
cross join tm
where
year in (2008,2007)) fill
where
fill.year in (2008,2007)
group by
fill.Category
,fill.month
RETURNS:
Category | Month | Rev. This Year | Rev. Last Year
Bikes 1 10 000 0
Bikes 2 12 000 11 000
Bikes 3 12 000 11 500
Bikes 4 0 15 400
Bikes 5 0 0
Bikes 6 0 0
Bikes 7 0 0
Bikes 8 0 0
Note that most reporting tools will do this crosstab or matrix functionality, and now that i think of it SQL Server 2005 has pivot syntax that will do this as well.
Here are some additional resources. CASE http://www.4guysfromrolla.com/webtech/102704-1.shtml SQL SERVER 2005 PIVOT http://msdn.microsoft.com/en-us/library/ms177410.aspx
这篇关于按月比较产品销售额的 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
