过去12个月的sql server，包括开始日期和结束日期

xxls0lw8 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(472)

我很难显示过去12个月的每一张唱片，有人能帮我吗？现在我只能为每条记录显示一个月。

DECLARE @DateEnd as DATETIME = DATEADD(month,((YEAR(getdate())-1900)*12) + MONTH(getdate())-1,-1)
--             SET @DateEnd =  '20191130'

DECLARE @Frequency_List table (FREQUENCY_ID char(3)); INSERT into @Frequency_List values ('118')    --                ('111'),('118'),('110')        --             'MTD','QTD','YTD'

DECLARE @Entity_List table (ENTITY_NAME char(50)); INSERT into @Entity_List values 
('F1000'),('R2202'),('R528'),('R810'),('R567'),('R402I'),('R508'),('F1000'),('A950A'),('R557'),('R559'),('R560'),('TBNOBL'),('ALTACORP'),('R590RVME'),('Z490'),('R5070'),('R591'),('R710')

select    P.PORTF_CODE, F.EXT_NAME, REPLACE(CONVERT(VARCHAR(10), AKRE.BEGIN_DATE, 102), '.', '-') as 'BEGIN_DATE', REPLACE(CONVERT(VARCHAR(10), AKRE.END_DATE, 102), '.', '-') as 'END_DATE', 
                                ISNULL(AKRE.PTF_END_OAD,0) 'PTF End Duration', ISNULL(AKRE.BMK_END_OAD,0) 'BMK End Duration', (ISNULL(AKRE.PTF_END_OAD,0)-ISNULL(AKRE.BMK_END_OAD,0)) 'Diff End Duration',
                                ISNULL(AKRE.PTF_END_OAS,0)*10000 'PTF End Spread', ISNULL(AKRE.BMK_END_OAS,0)*10000 'BMK End Spread', (ISNULL(AKRE.PTF_END_OAS,0)-ISNULL(AKRE.BMK_END_OAS,0))*10000 'Diff End Spread',
                                ((ISNULL(AKRE.PTF_END_OAD,0)*(ISNULL(AKRE.PTF_END_OAS,0)*10000))-(ISNULL(AKRE.BMK_END_OAD,0)*(ISNULL(AKRE.BMK_END_OAS,0)*10000))) 'DIF_END_DTS'

from BISAMDW..ATTX_KEY_RATES_EFFECTS AKRE 
left join BISAMDW..PORTFOLIO P on P.PORTF_ID = AKRE.PORTF_ID
left join BISAMDW..ATTR_INSTRUMENT AI on AI.ATINS_ID = AKRE.ATINS_ID
left join [BISAMDW].[dbo].[UD_GROUP] GRP on AKRE.USER_DEFINED_GROUP_ID=GRP.USER_DEFINED_GROUP_ID
left join BISAMDW..T_FREQUENCY F on F.FREQUENCY_ID = AKRE.FREQUENCY_ID

where AKRE.END_DATE = @DateEnd and P.PORTF_NAME in ( select ENTITY_NAME from @Entity_List) 
                and AKRE.PORTF_CONFIG_ID in ( 1 )
                and AKRE.FREQUENCY_ID in (select FREQUENCY_ID from @Frequency_List)
                and AKRE.PTF_RETURN is NOT null
                and GRP.EXT_CODE in ('BARCLAYS','MASTER_2016')         

order by 1,2

示例结果中“开始月份”和“结束月份”列的结果应如下所示：

MonthStartDate  MonthEndDate
2011-04-01  2011-04-30
2011-05-01  2011-05-31
2011-06-01  2011-06-30
2011-07-01  2011-07-31
2011-08-01  2011-08-31
2011-09-01  2011-09-30
2011-10-01  2011-10-31
2011-11-01  2011-11-30
2011-12-01  2011-12-31
....

谢谢您！

sql sql-server Date tsql

来源：https://stackoverflow.com/questions/62022891/sql-server-for-last-12-months-with-begin-date-and-end-date

2条答案

按热度按时间

0vvn1miw1#

如果要生成过去12个月（加上当前月份）的月份开始和结束列表，则一个选项是使用递归查询：

with cte as (
    select 
        dateadd(
            year, 
            -1, 
            datefromparts(year(getdate()), month(getdate()), 1)
        ) monthStartDate
    union all
    select dateadd(month, 1, monthStartDate)
    where monthStartDate < dateadd(month, -1, getdate())
)
select monthStartDate, eomonth(monthStartDate) monthEndDate from cte

如果要多次使用此数据，则应将此查询的结果存储在单独的表中，然后可以直接在查询中使用。这称为日历表。

赞(0）回复(0）举报 2021-07-26

dwthyt8l2#

就我个人而言，如果您只需要每个月的开始和结束日期，我建议您创建一个日历表（有100个关于如何创建这些的示例）。那么从该表中获取开始日期和结束日期就很简单了：

SELECT MIN(C.CalendarDate) AS MonthStart,
       MAX(C.CalendarDate) AS MonthEnd
FROM dbo.Calendar C
WHERE C.CalendarDate >= '20110101'
  AND C.CalendarDate < '20200601'
GROUP BY C.CalendarYear,
         C.CalendarMonth;

或者，您可以使用计数动态生成这些：

DECLARE @StartDate date = '20110101',
        @EndDate date = '20200601';

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT TOP (SELECT DATEDIFF(MONTH, @StartDate, @EndDate)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS I
    FROM N N1, N N2, N N3, N N4, N N5)
SELECT DATEADD(MONTH, T.I, @StartDate) AS MonthStart,
       EOMONTH(DATEADD(MONTH, T.I, @StartDate)) AS MonthEnd
FROM Tally T;

赞(0）回复(0）举报 2021-07-26

我来回答

过去12个月的sql server，包括开始日期和结束日期

2条答案

相关问题

热门标签

最新问答