sql-server - 检索过去 11 个月的图表

Question

我需要 11 月和 12 月的最后 11 个月以及图表的当前月份，但在没有数据的情况下看不到月份。它仍然被称为不应该的最后几年。

declare @TempTable TABLE
(
[Order] int,
[Month] nvarchar(3),
[Booked] int,
[Cancelled] int
)

declare @CurrentYear int = cast(substring(convert(varchar, getdate(), 23), 0,5) as int)
declare @CurrentMonth int = cast(substring(convert(varchar, getdate(), 1), 0,3) as int)

insert into @TempTable 
select
(@CurrentYear - substring(convert(varchar, A.CreatedAt, 23), 0,5)) * 11 + 
@CurrentMonth - cast(substring(convert(varchar,A.CreatedAt, 1),0,3) as int)
as 'Order',
substring(convert(nvarchar, A.CreatedAt, 107), 0, 4) as 'Month',
Count(*) as 'Booked',
SUM(cast(A.IsCancelled as int)) AS 'Cancelled'
FROM ReservationTbl A
GROUP BY substring(convert(nvarchar, A.CreatedAt, 107), 0, 4),
(@CurrentYear - substring(convert(varchar, A.CreatedAt, 23), 0,5)) * 11 +
@CurrentMonth - cast(substring(convert(varchar,A.CreatedAt, 1),0,3) as int)



select * from @TempTable WHERE [Order] <> (SELECT MAX([Order]) FROM @TempTable) Order By [Order] DESC

标签： sql-server