sql-server - 趋势线 30 天间隔 - 标签中重复的月份

Question

使用以下查询检查趋势线的 30 天间隔。但问题是它导致重复的几个月任何解决方案

USE [Database];
DECLARE @toDate NVARCHAR(30)  = '2020-05-29';
DECLARE @T table(ID int, toDate date, fromDate date, xVal NVARCHAR(100), indexVal NVARCHAR(100))
    insert into @T values(1, Cast((select (Cast(@toDate as datetime))) as date), Cast((select (Cast(@toDate as datetime))-30) as date),'',0)
    insert into @T values(2, Cast((select (Cast(@toDate as datetime))-30) as date), Cast((select (Cast(@toDate as datetime))-60) as date),'',0)
    insert into @T values(3, Cast((select (Cast(@toDate as datetime))-60) as date), Cast((select (Cast(@toDate as datetime))-90) as date),'',0)
    insert into @T values(4, Cast((select (Cast(@toDate as datetime))-90) as date), Cast((select (Cast(@toDate as datetime))-120) as date),'',0)
    insert into @T values(5, Cast((select (Cast(@toDate as datetime))-120) as date), Cast((select (Cast(@toDate as datetime))-150) as date),'',0)
    insert into @T values(6, Cast((select (Cast(@toDate as datetime))-150) as date), Cast((select (Cast(@toDate as datetime))-180) as date),'',0)
    insert into @T values(7, Cast((select (Cast(@toDate as datetime))-180) as date), Cast((select (Cast(@toDate as datetime))-210) as date),'',0)
    insert into @T values(8, Cast((select (Cast(@toDate as datetime))-210) as date), Cast((select (Cast(@toDate as datetime))-240) as date),'',0)
    insert into @T values(9, Cast((select (Cast(@toDate as datetime))-240) as date), Cast((select (Cast(@toDate as datetime))-270) as date),'',0)
    insert into @T values(10, Cast((select (Cast(@toDate as datetime))-270) as date), Cast((select (Cast(@toDate as datetime))-300) as date),'',0)
    insert into @T values(11, Cast((select (Cast(@toDate as datetime))-300) as date), Cast((select (Cast(@toDate as datetime))-330) as date),'',0)
    insert into @T values(12, Cast((select (Cast(@toDate as datetime))-330) as date), Cast((select (Cast(@toDate as datetime))-360) as date),'',0)

    UPDATE @T SET 
        xVal = CONCAT(FORMAT(toDate , 'MMM'), '-', YEAR(toDate))
    select * from @T as T

标签： sql-serversql-functiontrendline

Answer 1

查看以下内容，但它也有缺陷，指定错误月份的错误日期

USE [database];
DECLARE @toDate NVARCHAR(30)  = '2020-03-01';
DECLARE @T table(ID int, toDate date, fromDate date, xVal NVARCHAR(100), indexVal NVARCHAR(100))
    insert into @T values(1, Cast((select (Cast(@toDate as datetime))) as date), Cast((select (Cast(@toDate as datetime))-30) as date),'',0)
    insert into @T values(2, Cast((select (Cast(@toDate as datetime))-30) as date), Cast((select (Cast(@toDate as datetime))-60) as date),'',0)
    insert into @T values(3, Cast((select (Cast(@toDate as datetime))-60) as date), Cast((select (Cast(@toDate as datetime))-90) as date),'',0)
    insert into @T values(4, Cast((select (Cast(@toDate as datetime))-90) as date), Cast((select (Cast(@toDate as datetime))-120) as date),'',0)
    insert into @T values(5, Cast((select (Cast(@toDate as datetime))-120) as date), Cast((select (Cast(@toDate as datetime))-150) as date),'',0)
    insert into @T values(6, Cast((select (Cast(@toDate as datetime))-150) as date), Cast((select (Cast(@toDate as datetime))-180) as date),'',0)
    insert into @T values(7, Cast((select (Cast(@toDate as datetime))-180) as date), Cast((select (Cast(@toDate as datetime))-210) as date),'',0)
    insert into @T values(8, Cast((select (Cast(@toDate as datetime))-210) as date), Cast((select (Cast(@toDate as datetime))-240) as date),'',0)
    insert into @T values(9, Cast((select (Cast(@toDate as datetime))-240) as date), Cast((select (Cast(@toDate as datetime))-270) as date),'',0)
    insert into @T values(10, Cast((select (Cast(@toDate as datetime))-270) as date), Cast((select (Cast(@toDate as datetime))-300) as date),'',0)
    insert into @T values(11, Cast((select (Cast(@toDate as datetime))-300) as date), Cast((select (Cast(@toDate as datetime))-330) as date),'',0)
    insert into @T values(12, Cast((select (Cast(@toDate as datetime))-330) as date), Cast((select (Cast(@toDate as datetime))-360) as date),'',0)
    UPDATE @T SET 
        xVal = CONCAT(FORMAT(DateAdd(month,-(ID-1),@todate) , 'MMM'), '-', YEAR(DateAdd(month,-(ID-1),@todate)))
    select * from @T as T