SQL获得不同的税率，形成税务表

Question

我很乐观一些聪明的SQL作者可以调整我的查询以给出我想要的结果。我必须提取州的当前税率和不同的税率(PST/QST/GST/HST(，通常每个州都有PST/GST或HST。查看下面的数据表，我需要获得"短名"，"名称"one_answers"有效日期"的独特价值的税收率。

所需的结果是此表：

EffectiveDate  Rate ShortName   name
2016-01-01  0.050000    AB  GST
2013-04-01  0.050000    BC  GST
2013-04-01  0.070000    BC  PST
2013-07-01  0.050000    MB  GST
2013-07-01  0.080000    MB  PST
2016-07-01  0.150000    NB  HST
2016-07-01  0.150000    NL  HST
2016-01-01  0.150000    NS  HST
2016-01-01  0.050000    NT  GST
2016-01-01  0.050000    NU  GST
2015-04-01  0.130000    ON  HST
2016-10-01  0.150000    PE  HST
2013-01-01  0.050000    QC  GST
2017-03-01  0.099750    QC  QST
2016-01-01  0.050000    SK  GST
2017-03-01  0.060000    SK  PST
2009-02-01  0.050000    YT  GST

到目前为止我的查询：

    SELECT --TaxRates.ID, --TaxRates.StateId, TaxRateDetails.id, 
TaxRateDetails.EffectiveDate,  TaxRateDetails.Rate,--TaxRateDetails.TaxRateId,
States.ShortName,   TaxImpositionTypes.name -- DISTINCT Name,states.ShortName, TaxRateDetails.Id,TaxImpositionTypeDetailId 
FROM dbo.TaxRates
join dbo.TaxRateDetails ON TaxRateDetails.TaxRateId = TaxRates.Id
INNER JOIN States ON dbo.TaxRates.StateId = States.Id  AND States.IsActive =1
INNER JOIN TaxImpositionTypeDetails ON TaxRateDetails.TaxImpositionTypeDetailId = TaxImpositionTypeDetails.Id 
INNER JOIN TaxImpositionTypes ON dbo.TaxImpositionTypeDetails.TaxImpositionTypeId = TaxImpositionTypes.Id
WHERE  EffectiveDate < CURRENT_TIMESTAMP
order BY ShortName

此查询创建的此源表如下：

EffectiveDate  Rate    ShortName    name
2016-01-01  0.050000    AB  GST
2009-02-01  0.050000    AB  GST
2013-04-01  0.050000    BC  GST
2013-04-01  0.070000    BC  PST
2013-04-01  0.050000    BC  GST
2013-04-01  0.070000    BC  PST
2013-07-01  0.050000    MB  GST
2013-07-01  0.080000    MB  PST
2010-07-01  0.130000    NB  HST
2016-07-01  0.150000    NB  HST
2010-07-01  0.130000    NB  HST
2009-02-01  13.000000   NL  HST
2016-07-01  0.150000    NL  HST
2010-07-01  0.130000    NL  HST
2016-01-01  0.150000    NS  HST
2010-07-01  0.150000    NS  HST
2016-01-01  0.050000    NT  GST
2009-02-01  0.050000    NT  GST
2016-01-01  0.050000    NU  GST
2009-02-01  0.050000    NU  GST
2010-07-01  0.130000    ON  HST
2009-02-01  0.050000    ON  HST
2009-02-01  0.080000    ON  PST
2010-03-01  0.130000    ON  HST
2015-04-01  0.130000    ON  HST
2015-04-01  0.000100    ON  PST
2009-02-01  13.000000   PE  HST
2013-04-01  0.140000    PE  HST
2016-10-01  0.150000    PE  HST
2013-01-01  0.050000    QC  GST
2013-01-01  0.010000    QC  QST
2012-01-01  0.050000    QC  GST
2012-01-01  0.095000    QC  QST
2017-03-01  0.099750    QC  QST
2016-01-01  0.050000    SK  GST
2016-01-01  0.050000    SK  PST
2009-02-01  0.050000    SK  GST
2009-02-01  0.050000    SK  PST
2017-03-01  0.060000    SK  PST
2009-02-01  0.050000    YT  GST

Answer 1

如果我正确理解了问题，我们可以为每个唯一的短名和名称顺序分配一个行号，该命名顺序是通过有效日期下降的配对，因此首先是最高日期。然后，我们仅显示每个唯一的简单和名称配对的第1行。

WITH CTE AS (
   SELECT TaxRateDetails.EffectiveDate
        , TaxRateDetails.Rate
        , States.ShortName
        , TaxImpositionTypes.name 
        , Row_number() over (partition by States.ShortName,TaxImpositionTypes.name ORDER BY TaxRateDetails.EffectiveDate DESC) RN
FROM dbo.TaxRates
INNER JOIN dbo.TaxRateDetails 
   ON TaxRateDetails.TaxRateId = TaxRates.Id
INNER JOIN States 
   ON dbo.TaxRates.StateId = States.Id  
  AND States.IsActive =1
INNER JOIN TaxImpositionTypeDetails 
   ON TaxRateDetails.TaxImpositionTypeDetailId = TaxImpositionTypeDetails.Id 
INNER JOIN TaxImpositionTypes 
   ON dbo.TaxImpositionTypeDetails.TaxImpositionTypeId = TaxImpositionTypes.Id
WHERE EffectiveDate < CURRENT_TIMESTAMP)
SELECT * FROM CTE where RN = 1