日期 - 复杂（奇怪）的需要 [英] Dates - complex (strange) need

问题描述

大家好 - 我有一个非常奇怪的需求。我需要从表表中获取数据并根据以下逻辑推断数据。作为示例，我提供以下代码以生成一个月的日期

hi all - i have a very strange need. I need to take data from my table table and extrapolate data based on the following logic. As a sample, I am providing the code below to generate a month worth of dates

我需要写一个查询：

1。检索月中的所有日期以及名为RetrospectiveMonths的相应列，其值为1

1. Retrieve all the dates in the month and a corresponding column called RetrospectiveMonths whose values will be 1

2。除＃1外，结果应包括当月+上个月的所有日期（在本例中为2017年12月），并且RetrospectiveMonths的值为2

2. In addition to #1, the result should include all the dates in current month + previous month (in this case Dec 2017) and the value of RetrospectiveMonths would be 2

2。除了＃2之外，结果应该包括当月+上个月的所有日期（在本例中为2017年12月）+上个月之前的月份（在本例中为2017年11月），并且RetrospectiveMonths的值将为3

2. In addition to #2, the result should include all the dates in current month + previous month (in this case Dec 2017) + month before previous month (in this case Nov 2017) and the value of RetrospectiveMonths would be 3

依此类推..直到&rbsp; RetrospectiveMonths = 12

and so on.. until RetrospectiveMonths = 12

换句话说，列  RetrospectiveMonths 的值将指示要返回的月数并检索（包括月份）。例如，在结果数据集中，如果我过滤了RetrospectiveMonths  = 4，那么我应该
获得2018年1月，2017年2017年11月，2017年10月。

In other words, the value of column RetrospectiveMonths  will indicate the number of months to go back and retrieve (including the month). For example, in the resulting data set, if i filter for RetrospectiveMonths  = 4, then i should get Jan 2018, Dev 2017, Nov 2017, Oct 2017.

提前感谢您的帮助！

DECLARE @Dates TABLE (Date DATE)
INSERT INTO @Dates VALUES  ('1/1/2018')
INSERT INTO @Dates VALUES  ('1/2/2018')
INSERT INTO @Dates VALUES  ('1/3/2018')
INSERT INTO @Dates VALUES  ('1/4/2018')
INSERT INTO @Dates VALUES  ('1/5/2018')
INSERT INTO @Dates VALUES  ('1/6/2018')
INSERT INTO @Dates VALUES  ('1/7/2018')
INSERT INTO @Dates VALUES  ('1/8/2018')
INSERT INTO @Dates VALUES  ('1/9/2018')
INSERT INTO @Dates VALUES  ('1/10/2018')
INSERT INTO @Dates VALUES  ('1/11/2018')
INSERT INTO @Dates VALUES  ('1/12/2018')
INSERT INTO @Dates VALUES  ('1/13/2018')
INSERT INTO @Dates VALUES  ('1/14/2018')
INSERT INTO @Dates VALUES  ('1/15/2018')
INSERT INTO @Dates VALUES  ('1/16/2018')
INSERT INTO @Dates VALUES  ('1/17/2018')
INSERT INTO @Dates VALUES  ('1/18/2018')
INSERT INTO @Dates VALUES  ('1/19/2018')
INSERT INTO @Dates VALUES  ('1/20/2018')
INSERT INTO @Dates VALUES  ('1/21/2018')
INSERT INTO @Dates VALUES  ('1/22/2018')
INSERT INTO @Dates VALUES  ('1/23/2018')
INSERT INTO @Dates VALUES  ('1/24/2018')
INSERT INTO @Dates VALUES  ('1/25/2018')
INSERT INTO @Dates VALUES  ('1/26/2018')
INSERT INTO @Dates VALUES  ('1/27/2018')
INSERT INTO @Dates VALUES  ('1/28/2018')
INSERT INTO @Dates VALUES  ('1/29/2018')
INSERT INTO @Dates VALUES  ('1/30/2018')
INSERT INTO @Dates VALUES  ('1/31/2018')

SELECT * FROM @Dates

推荐答案

您可以在数据中构建辅助日历表以便进行此类查询。

You can build an auxiliary calendar table in your database to facilitate this kind of query.

这篇关于日期 - 复杂（奇怪）的需要的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！