时间戳

首页 > 解决方案 > 连接日期的月份和年份以对其进行排序

sql - 连接日期的月份和年份以对其进行排序

问题描述

我有一个问题，我认为我想多了。我有一个导入到 PowerBI 的 SQL 视图，我想用它来订购我正在构建的图形。我订购并遇到问题的部分是日期部分列：

select
    Ticket_ClientName, count(Ticket_ClientName) as DisplayNameCount,
    concat( datepart(month, cast(t.ticket_opendate as date)),
            datepart(year, cast(t.ticket_opendate as date))) as OpenDate,
    datename(month, cast(t.ticket_opendate as date)) as Month_Name
from 
    dbo.Ticket t
where 
    CAST(t.ticket_opendate as date) >= '02/01/2021'
    and t.Ticket_ClientName is not null
    and Ticket_DisplayId not like 'EH%'
group by
    ticket_ClientName,
    concat( datepart(month, cast(t.ticket_opendate as date)),
            datepart(year, cast(t.ticket_opendate as date))),
    datename(month, cast(t.ticket_opendate as date))

我将面临的问题是当一年过去时，因为 10 月到 12 月将始终保持为最新的月份，因为它们是最高的数字，有没有人有办法解决这个问题？如果我想太多了，我提前道歉。

标签： sqlsql-server

解决方案

您可以按 分组和排序EOMONTH，它返回该月的最后一天。

select
    Ticket_ClientName,
    count(Ticket_ClientName) as DisplayNameCount,
    FORMAT(EOMONTH(t.ticket_opendate), 'MMyyyy') as OpenDate,
    datename(month, EOMONTH(t.ticket_opendate)) as Month_Name
from 
    dbo.Ticket t
where 
    t.ticket_opendate >= '20210102'
    and t.Ticket_ClientName is not null
    and Ticket_DisplayId not like 'EH%'
group by
    ticket_ClientName,
    EOMONTH(t.ticket_opendate)
order by
    EOMONTH(t.ticket_opendate);

推荐阅读