查询出历史数据表? [英] Query out of historic data table?

问题描述

场景:显示状态注册信息的历史数据表.数据示例如下:

ID	State	Status	AsOfDate
2021	AZ	A	2001-05-21
2021	AZ	T	2004-12-31
2021	AZ	A	2010-01-01
2021	CA	A	2001-05-21
2021	WA	A	2001-05 -21
2023	AZ	A	2005-01-04
2023	MD	A	2003-07-01
2023	WA	A	2002-05-21
2023	WA	T	2007-05-21

ID =字段代表标识号
State =代表销售代表获得工作许可的状态
Status =表示活动或已终止的标志
AsOfDate =状态更改的日期

我需要弄清楚该查询，该查询将返回一组对于给定ID当前处于活动状态的不同状态.也就是说，2021年应返回{AZ，CA，WA}(AZ已终止，随后重新激活)，而2023年应返回{AZ，MD}(WA仍终止.)

我认为这是一个简单的查询，但我不太清楚.

The scenario: A table of historical data showing state registration information. An example of the data looks like this:

ID	State	Status	AsOfDate
2021	AZ	A	2001-05-21
2021	AZ	T	2004-12-31
2021	AZ	A	2010-01-01
2021	CA	A	2001-05-21
2021	WA	A	2001-05-21
2023	AZ	A	2005-01-04
2023	MD	A	2003-07-01
2023	WA	A	2002-05-21
2023	WA	T	2007-05-21

ID = field rep identification number
State = state where a rep is licensed to work
Status = flag indicating Active or Terminated
AsOfDate = the date when the status changed

I need to figure out the query that will return the set of distinct states that are currently active for a given ID. That is to say, 2021 should return {AZ, CA, WA} (AZ was terminated and later reactivated) and 2023 should return {AZ, MD} (WA is still terminated.)

I would think this is a simple one, but I can''t quite figure it out.

推荐答案

以下查询将为您工作:

The following query will work for you:

SELECT T.* FROM TestT T
JOIN (SELECT ID, State, MAX(AsOfDate) AsOfDate FROM TestT
GROUP BY ID, State) A ON T.Id = A.Id AND T.AsOfDate = A.AsOfDate
WHERE T.id = 2023
AND Status = 'A'

在此脚本中，TestT是包含您的数据的实际表.根据需要更改T.Id.

希望对您有帮助.

In this script TestT is the actual table which contain your data. Change the T.Id as per your need.

Hope this will help you.

这篇关于查询出历史数据表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！