时间戳

首页 > 解决方案 > 将日期时间转换为 mm-dd-yyyy 格式的日期，以便在 SQL 中与 between 子句一起使用

sql - 将日期时间转换为 mm-dd-yyyy 格式的日期，以便在 SQL 中与 between 子句一起使用

问题描述

表输出

我有上面的表格内容，想得到下面的输出。

最终输出

我写了下面的代码：

SELECT  GROUP_NAME,CONVERT(date,creation_date,103),
SUM(case when Status='status 1' then 1 else 0 end) AS 'status 1',
SUM(case when Status='status 2' then 1 else 0 end) AS 'status 2',
SUM(case when Status='status 3' then 1 else 0 end) AS 'status 3',
SUM(case when Status='status 4' then 1 else 0 end) AS 'status 4',
SUM(case when Status='status 5' then 1 else 0 end) AS 'status 5',
COUNT(ticketnumber) AS GRAND_TOTAL 

FROM tablename WHERE CONVERT(date,creation_date,103) BETWEEN @from AND @to
GROUP BY GROUP_NAME,CONVERT(date,creation_date,103)

我正在创建将映射到 iservice 工具的视图。@from 和 @to 将是用户选择日期的选项。

在这里，我无法根据用户选择的日期获得输出。

例如，如果原始数据有 A 组的 4 张票，但输出显示 3 张票。

需要帮助纠正代码或编写上述代码的任何简单方法。

我正在使用 SQL 2012

标签： sqlsql-serversql-server-2012

解决方案

您可以通过 on 删除您的分组来执行此操作Creation_date- 当您按此分组时，您将获取所有彼此不同的日期。

注意：where 子句也只是向您展示了如何放置它。我还通过删除)之后更正了代码convert(date)

declare @table table (groupName nvarchar(50),create_date datetime,statusname nvarchar(50),ticket_number nvarchar(50))

insert into @table
Values

('Group A','2018-09-15','Status 1','A1'),
('Group B','2018-09-05','Status 3','A2'),
('Group C','2018-08-26','Status 5','A6'),
('Group C','2018-09-01','Status 4','A8'),
('Group B','2018-08-22','Status 2','A10'),
('Group C','2018-08-12','Status 3','A11'),
('Group A','2018-08-18','Status 5','A17'),
('Group B','2018-08-04','Status 5','A19'),
('Group C','2018-08-10','Status 4','A21'),
('Group A','2018-07-31','Status 2','A25'),
('Group B','2018-08-02','Status 1','A28'),
('Group C','2018-07-27','Status 3','A34'),
('Group A','2018-07-17','Status 2','A39'),
('Group B','2018-07-23','Status 4','A46'),
('Group C','2018-07-09','Status 2','A50'),
('Group C','2018-07-15','Status 3','A62'),
('Group B','2018-07-15','Status 5','A71'),
('Group C','2018-07-07','Status 5','A72'),
('Group A','2018-06-27','Status 4','B1'),
('Group A','2018-07-03','Status 2','B6')


SELECT
  GROUPNAME,
  sum(case when Statusname='status 1' then 1 else 0 end) as 'status 1',
  sum(case when Statusname='status 2' then 1 else 0 end) as 'status 2',
  sum(case when Statusname='status 3' then 1 else 0 end) as 'status 3',
  sum(case when Statusname='status 4' then 1 else 0 end) as 'status 4',
  sum(case when Statusname='status 5' then 1 else 0 end) as 'status 5',
  count(ticket_number) as GRAND_TOTAL 
FROM @table 
WHERE convert(date,create_date,103) between '2018-07-07' and '2018-08-01'
GROUP BY GROUPNAME

推荐阅读