如何根据日期在单独的列中显示月、月+ 1、月+2、月+3、月+4？

Question

到目前为止，这是我的疑问：

declare @snaps table (hotel_id int, snapdate date, month int, year 
int) 
insert into @snaps
select hotel_id, max(import_date) as maximport_date, 
month(max(import_date)), year(max(import_Date))  
from ukrmc.dbo.block_Res 
where import_date between '2017-12-01' and '2018-12-31'  
group by month(import_date), year(import_Date), hotel_id--, market_seg 
--order by hotel_id, maximport_Date 

select     otb1.hotel_id
          ,otb1.snapdate
          ,otb1.month
          ,otb1.year 
from
       (select * from @snaps) otb1

下面是这个查询为一个hotel_id提供的示例：

hotel_id  snapdate    month  year
3         2017-12-31  12     2017
3         2018-01-31  1      2018
3         2018-02-28  2      2018
3         2018-03-30  3      2018
3         2018-04-30  4      2018
3         2018-05-31  5      2018
3         2018-06-30  6      2018
3         2018-07-31  7      2018
3         2018-08-31  8      2018
3         2018-09-30  9      2018
3         2018-10-06  10     2018

50家酒店的...and等等。

现在我想要一个列来显示快照的month+year和接下来的4个月中的每一个。例如，对于hotel_id 3和快照“2017-12-31”和“2018-01-31”(举个例子)，应该如下所示：

hotel_id  snapdate    month  year    Month_TY
3         2017-12-31  12     2017    December 2017
3         2017-12-31  12     2017    January 2018
3         2017-12-31  12     2017    February 2018
3         2017-12-31  12     2017    March 2018
3         2017-12-31  12     2017    April 2018 
3         2018-01-31  1      2018    January 2018
3         2018-01-31  1      2018    February 2018
3         2018-01-31  1      2018    March 2018
3         2018-01-31  1      2018    April 2018
3         2018-01-31  1      2018    May 2018

我不知道我会怎么做这种事？我需要在select条款中做些什么吗？

Answer 1

您可以使用交叉应用来计算最后SELECT语句中的5个日期，并使用DATENAME计算月份字符串：

SELECT hotel_id, snapdate, month, year,
  DATENAME(MM, snapdateex) + N' ' + DATENAME(YYYY, snapdateex) AS Month_TY
FROM @snaps
CROSS APPLY (VALUES
    (snapdate),
    (DATEADD(month, 1, snapdate)),
    (DATEADD(month, 2, snapdate)),
    (DATEADD(month, 3, snapdate)),
    (DATEADD(month, 4, snapdate))) AS hlp(snapdateex)

Answer 2

我能想到的最简单的方法是联合，以及dateadd：

select otb1.hotel_id ,otb1.snapdate ,MONTH(otb1.snapdate) ,YEAR(otb1.snapdate) from (select * from @snaps) otb1

UNION ALL

select otb1.hotel_id ,otb1.snapdate ,MONTH(DATEADD(MONTH, 1 , otb1.snapdate)) ,YEAR(DATEADD(MONTH, 1 , otb1.snapdate)) from (select * from @snaps) otb1

UNION ALL 

select otb1.hotel_id ,otb1.snapdate ,MONTH(DATEADD(MONTH, 2 , otb1.snapdate)) ,YEAR(DATEADD(MONTH, 2, otb1.snapdate)) from (select * from @snaps) otb1

UNION ALL 

select otb1.hotel_id ,otb1.snapdate ,MONTH(DATEADD(MONTH, 3 , otb1.snapdate)) ,YEAR(DATEADD(MONTH, 3, otb1.snapdate)) from (select * from @snaps) otb1