SQL查询按小时细分销售额(SQL query breakdown the sales by each hour)

我有一个查询如下，它将返回今天的每个远程服务器销售结果如下

datebooking NameOfDay ORC CWP TC TotalBooking EmailSent EmailNotSent 20150524 Sunday 447 144 272 1441 1441 0

实际上，我将日期调整为8个数字

我的问题是，我可以按小时返回每个远程销售服务器故障吗？想象一下，有一天将是24行细节，而不是每天一行这意味着在预订日期我们将采用这种格式20150524上午12点到凌晨1点销售，凌晨2点到凌晨3点等等？我应该如何根据当前查询完成此查询？

SELECT Convert(char(8), OrderH_dtmInitiated, 112)as datebooking, Datename (weekday, OrderH_dtmInitiated) As NameOfDay, --count(distinct OrderH_strCinemaId)as Cinemasite, SUM(case when OrderH_strCinemaId like '1101' then 1 else 0 end)as ORC, SUM(case when OrderH_strCinemaId like '1102' then 1 else 0 end)as CWP, SUM(case when OrderH_strCinemaId like '1104' then 1 else 0 end)as TC, count(distinct OrderH_intID)as TotalBooking, SUM(case when OrderH_strEmailConfirmationSent like 'Y' then 1 else 0 end) as EmailSent, SUM(case when OrderH_strEmailConfirmationSent is NULL then 1 else 0 end) as EmailNotSent FROM [VISTAIT].[dbo].[tblOrderHistory] WHERE OrderH_dtmInitiated >= (SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE() ))) GROUP BY Convert(char(8), OrderH_dtmInitiated, 112), Datename (weekday, OrderH_dtmInitiated ) ORDER by Convert(char(8), OrderH_dtmInitiated, 112)

I have a query as below which will return each Remote server sales for today Result as per below

datebooking NameOfDay ORC CWP TC TotalBooking EmailSent EmailNotSent 20150524 Sunday 447 144 272 1441 1441 0

The datebooking actually i have it trim down to 8 numeric

My question is could I return each remote sales server breakdown by per hour? Imagine one day will be 24 lines of details instead of one line per day That mean under booking date will we in this format 20150524 12am to 1am sales, 2am to 3am so on and so forth? How should I accomplish this query based on my current query?

SELECT Convert(char(8), OrderH_dtmInitiated, 112)as datebooking, Datename (weekday, OrderH_dtmInitiated) As NameOfDay, --count(distinct OrderH_strCinemaId)as Cinemasite, SUM(case when OrderH_strCinemaId like '1101' then 1 else 0 end)as ORC, SUM(case when OrderH_strCinemaId like '1102' then 1 else 0 end)as CWP, SUM(case when OrderH_strCinemaId like '1104' then 1 else 0 end)as TC, count(distinct OrderH_intID)as TotalBooking, SUM(case when OrderH_strEmailConfirmationSent like 'Y' then 1 else 0 end) as EmailSent, SUM(case when OrderH_strEmailConfirmationSent is NULL then 1 else 0 end) as EmailNotSent FROM [VISTAIT].[dbo].[tblOrderHistory] WHERE OrderH_dtmInitiated >= (SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE() ))) GROUP BY Convert(char(8), OrderH_dtmInitiated, 112), Datename (weekday, OrderH_dtmInitiated ) ORDER by Convert(char(8), OrderH_dtmInitiated, 112)

最满意答案

如果我理解正确，您只需将小时添加到select和group by ：

SELECT Convert(char(8), OrderH_dtmInitiated, 112) as datebooking, datename(hour, OrderH_dtmInitiated) as hourbooking, . . . FROM . . . GRUOP BY Convert(char(8), OrderH_dtmInitiated, 112), datename(hour, OrderH_dtmInitiated)

这假设OrderH_dtmInitiated实际上有时间信息。

另一种方法是使用包含小时的日期/时间格式，因此很容易将其放入一个字段：

SELECT Convert(char(13), OrderH_dtmInitiated, 121) as datebooking, . . . FROM . . . GROUP BY Convert(char(13), OrderH_dtmInitiated, 121)

格式为“YYYY-MM-DD HH”。

编辑：

如果你想要一小时的上午/下午，可能最简单的方法是一个明确的case ：

SELECT Convert(char(8), OrderH_dtmInitiated, 112) as datebooking, (CASE WHEN datename(hour, OrderH_dtmInitiated) < '12' THEN datename(hour, OrderH_dtmInitiated) + ' AM' ELSE datename(hour, OrderH_dtmInitiated) + ' PM' END) as hourbooking, . . . FROM . . . GRUOP BY Convert(char(8), OrderH_dtmInitiated, 112), datename(hour, OrderH_dtmInitiated)

注意：我故意使用datename()编写逻辑。这样，整个case语句不需要在group by子句中。

If I understand correctly, you would just add the hour to the select and group by:

SELECT Convert(char(8), OrderH_dtmInitiated, 112) as datebooking, datename(hour, OrderH_dtmInitiated) as hourbooking, . . . FROM . . . GRUOP BY Convert(char(8), OrderH_dtmInitiated, 112), datename(hour, OrderH_dtmInitiated)

This assumes that OrderH_dtmInitiated actually has the time information.

And alternative method would be to use a date/time format that includes the hour, so it is easy to put into one field:

SELECT Convert(char(13), OrderH_dtmInitiated, 121) as datebooking, . . . FROM . . . GROUP BY Convert(char(13), OrderH_dtmInitiated, 121)

The format would then be "YYYY-MM-DD HH".

EDIT:

If you want am/pm for the hour, probably the easiest way is an explicit case:

SELECT Convert(char(8), OrderH_dtmInitiated, 112) as datebooking, (CASE WHEN datename(hour, OrderH_dtmInitiated) < '12' THEN datename(hour, OrderH_dtmInitiated) + ' AM' ELSE datename(hour, OrderH_dtmInitiated) + ' PM' END) as hourbooking, . . . FROM . . . GRUOP BY Convert(char(8), OrderH_dtmInitiated, 112), datename(hour, OrderH_dtmInitiated)

Note: I wrote the logic using only datename() quite intentionally. That way, the whole case statement doesn't need to be in the group by clause.

更多推荐