blocks|key|1819395|text|糟糕的数据结构。要做的第一件事就是撤销它。然后，您将得到一个以年月点数作为列的表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1819396|从这里，你可以选择最近的12个月。事实上，你甚至不用担心客户什么时候离开，因为从那以后他们大概就没有收集过积分了。|1819397|下面是SQL中的一个示例：|1819398|with+points+as+(
++++select+123+as+account,+2012+as+year,
+++++++++++10+as+m01,+0+as+m02,+0+as+m03,+0+as+m04,+10+as+m05,+0+as+m06,
+++++++++++10+as+m07,+0+as+m08,+0+as+m09,+0+as+m10,+0+as+m11,+10+as+m12
+++),
+++points_ym+as+(
++++select+account,+YEAR,+mon,+cast(right(mon,+2)+as+int)+as+monnum,+points
++++from+points
++++unpivot+(points+for+mon+in+(m01,+m02,+m03,+m04,+m05,+m06,+m07,+m08,+m09,+m10,+m11,+m12)
++++)+as+unpvt
+++)
select+account,+SUM(points)
from+points_ym
where+year*12%2Bmonnum+>=+year(getdate())*12%2BMONTH(getdate())+-+12
group+by+account|code-block|syntax|javascript|1819399|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|O|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|P|8|@]|9|@]|A|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Lousy data structure. The first thing to do is to unpivot it. Then you get a table with year-month-points as the columns.

From here, you can just select the most recent 12 months. In fact, you don't even have to worry about when a customer left, since presumably they have not collected points since then.

Here is an example in SQL:

<pre><code>with points as (
 select 123 as account, 2012 as year,
 10 as m01, 0 as m02, 0 as m03, 0 as m04, 10 as m05, 0 as m06,
 10 as m07, 0 as m08, 0 as m09, 0 as m10, 0 as m11, 10 as m12
 ),
 points_ym as (
 select account, YEAR, mon, cast(right(mon, 2) as int) as monnum, points
 from points
 unpivot (points for mon in (m01, m02, m03, m04, m05, m06, m07, m08, m09, m10, m11, m12)
 ) as unpvt
 )
select account, SUM(points)
from points_ym
where year*12+monnum &gt;= year(getdate())*12+MONTH(getdate()) - 12
group by account
</code></pre>

blocks|key|1819420|text|不幸的是，使用points的表结构，您将不得不取消数据的枢轴。不枢轴将多列中的数据转换为行。一旦数据在行中，加入、过滤数据和汇总每个帐户的积分就容易多了。解除数据枢轴的代码将类似于以下内容：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1819421|select+account,
++cast(cast(year+as+varchar(4))%2B'-'%2Breplace(month_col,+'M',+'')%2B'-01'+as+date)+full_date,
++pts
from+points
unpivot
(
++pts
++for+month_col+in+([M01],+[M02],+[M03],+[M04],+[M05],+[M06],+[M07],+[M08],+[M09],+[M10],+[M11],+[M12])
)+unpiv|code-block|syntax|javascript|1819422|见与Demo。查询提供的结果类似于以下内容：|1819423|%7C+ACCOUNT+%7C++FULL_DATE+%7C+PTS+%7C
------------------------------
%7C+++++123+%7C+2011-01-01+%7C++10+%7C
%7C+++++123+%7C+2011-02-01+%7C+++0+%7C
%7C+++++123+%7C+2011-03-01+%7C+++0+%7C
%7C+++++123+%7C+2011-04-01+%7C+++0+%7C
%7C+++++123+%7C+2011-05-01+%7C++10+%7C|1819424|一旦数据采用这种格式，您就可以加入Customers表来获取每个account的总点数，因此代码将类似于以下内容：|1819425|select+
++c.account,+sum(pts)+TotalPoints
from+customers+c
inner+join+
(
++select+account,
++++++cast(cast(year+as+varchar(4))%2B'-'%2Breplace(month_col,+'M',+'')%2B'-01'+as+date)+full_date,
++++pts
++from+points
++unpivot
++(
++++pts
++++for+month_col+in+([M01],+[M02],+[M03],+[M04],+[M05],+[M06],+[M07],+[M08],+[M09],+[M10],+[M11],+[M12])
++)+unpiv
)+p
++on+c.account+=+p.account
where+
(
++c.enddate+=+'9999-12-31'
++and+full_date+>=+dateadd(year,+-1,+getdate())+
++and+full_date+<=+getdate()++
)
or
(
++c.enddate+<>+'9999-12-31'
++and+dateadd(year,+-1,+[enddate])+<=+full_date
++and+full_date+<=+[enddate]
)
group+by+c.account|1819426|请参阅与Demo|1819427|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlfiddle.com/#!3/1dda7/9|1|http://sqlfiddle.com/#!3/e2431/3^0|7|6|0|0|1|5|0|0|0|H|9|W|7|0|0|3|5|1|0^^$0|@$1|2|3|4|5|6|7|14|8|@$9|15|A|16|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|17|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|18|8|@]|D|@$9|19|A|1A|1|1B]]|E|$]]|$1|M|3|N|5|H|7|1C|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|1D|8|@$9|1E|A|1F|B|C]|$9|1G|A|1H|B|C]]|D|@]|E|$]]|$1|Q|3|R|5|H|7|1I|8|@]|D|@]|E|$I|J]]|$1|S|3|T|5|6|7|1J|8|@]|D|@$9|1K|A|1L|1|1M]]|E|$]]|$1|U|3|-4|5|6|7|1N|8|@]|D|@]|E|$]]]|V|$W|$5|X|Y|Z|E|$10|11]]|12|$5|X|Y|Z|E|$10|13]]]]

Unfortunately with your table structure of <code>points</code> you will have to unpivot the data. An unpivot takes the data from the multiple columns into rows. Once the data is in the rows, it will be much easier to join, filter the data and total the points for each account. The code to unpivot the data will be similar to this:

<pre><code>select account,
 cast(cast(year as varchar(4))+'-'+replace(month_col, 'M', '')+'-01' as date) full_date,
 pts
from points
unpivot
(
 pts
 for month_col in ([M01], [M02], [M03], [M04], [M05], [M06], [M07], [M08], [M09], [M10], [M11], [M12])
) unpiv
</code></pre>

See <a href="http://sqlfiddle.com/#!3/1dda7/9" rel="nofollow">SQL Fiddle with Demo</a>. The query gives a result similar to this:

<pre><code>| ACCOUNT | FULL_DATE | PTS |
------------------------------
| 123 | 2011-01-01 | 10 |
| 123 | 2011-02-01 | 0 |
| 123 | 2011-03-01 | 0 |
| 123 | 2011-04-01 | 0 |
| 123 | 2011-05-01 | 10 |
</code></pre>

Once the data is in this format, you can join the <code>Customers</code> table to get the total points for each <code>account</code>, so the code will be similar to the following:

<pre><code>select 
 c.account, sum(pts) TotalPoints
from customers c
inner join 
(
 select account,
 cast(cast(year as varchar(4))+'-'+replace(month_col, 'M', '')+'-01' as date) full_date,
 pts
 from points
 unpivot
 (
 pts
 for month_col in ([M01], [M02], [M03], [M04], [M05], [M06], [M07], [M08], [M09], [M10], [M11], [M12])
 ) unpiv
) p
 on c.account = p.account
where 
(
 c.enddate = '9999-12-31'
 and full_date &gt;= dateadd(year, -1, getdate()) 
 and full_date &lt;= getdate() 
)
or
(
 c.enddate &lt;&gt; '9999-12-31'
 and dateadd(year, -1, [enddate]) &lt;= full_date
 and full_date &lt;= [enddate]
)
group by c.account
</code></pre>

See <a href="http://sqlfiddle.com/#!3/e2431/3" rel="nofollow">SQL Fiddle with Demo</a>

I have two tables that I'm working with. Let's call them "Customers" and "Points". 

The Points table looks like this:

<pre><code>Account Year M01 M02 M03 M04 M05 M06 M07 M08 M09 M10 M11 M12
123 2011 10 0 0 0 10 0 10 0 0 0 0 10
123 2012 0 0 0 0 10 0 0 10 10 10 10 20
123 2013 5 0 0 0 0 0 0 0 0 0 0 0
</code></pre>

But these points work on a rolling 12 months. Calculating a current customer's points is simple enough, but the challenge is for customers who are no longer active. Say Customer 123 became inactive on Jan 2013, we would only want to calculate Feb'12-Jan'13. This is where the other table, Customers, comes in, let's simplify and say it looks just like this:

<pre><code>Account End Date
123 20130105
</code></pre>

Now, what I want to do is create a query that calculates the amount of points that each customer has. (Current 12 months for active customers, last 12 months they were active for customers who are no longer active.)

Here's some more information:

<ul>
<li>I'm running SQL Server 2008. </li>
<li>These tables have been supplied to me like this, I can't modify them.</li>
<li>An active customer is one who has an end date of 99991231 (Dec 31 9999)</li>
<li>The points table only populates for years that the customer is an active customer. Aka, someone becomes an active customer Feb 2009, they have an entry for the year 2009, if they became inactive in July 2009, their points is only calculating Feb-July 2009, there is no row for 2008 because they weren't a customer back then. Jan &amp; Aug-Dec 2009 will show 0's.</li>
<li>Additionally, the record is only created if the customer gains any points that year. If a customer gets 0 points in a year, there will be no record of it.</li>
<li>For border cases, if you get into the first day of a month, then that month is counted. Example, let's say today is April 1st, 2013, that means we sum up May'12-April'13.</li>
</ul>

This is a pretty complex question. If there's anything I can explain better please let me know. Thank you!

SQL Rolling Total up to a certain date

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有两张桌子在工作。让我们称他们为“顾客”和“点”。Point表如下所示：Account Year M01 M02 M03 M04 M05 M06 M07 M08 M09 M10 M11 M12123     2011  10   0   0   0  10   0  10   0   0   0   0  10123...

问SQL滚动总计到某一特定日期
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL滚动总计到某一特定日期EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL滚动总计到某一特定日期
EN