明天的第一个值出现在今天行的最后一个单元格上 [英] First value of tomorrow to appear on last cell of today's row
本文介绍了明天的第一个值出现在今天行的最后一个单元格上的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
感谢Stack上每个人的支持，我已经快要解决我的问题了.但是从SQL的角度来看，我对日期和时间有疑问.
Thanks to everyone's support on Stack, I am getting close to solving my problem. But I have an issue with dates and times from a pivot in SQL.
我有以下查询:
DECLARE @_SerialNumber NVARCHAR(MAX)
DECLARE @_DateFrom DATETIME
DECLARE @_DateTo DATETIME
SET @_SerialNumber = '2209'
SET @_DateFrom = '2018-09-20 00:00'
SET @_DateTo = DATEADD(DD, 1, @_DateFrom)
SELECT [Serial],
[Channel],
[ReadingDate],
[00:15],[00:30],[00:45],[01:00],[01:15],[01:30],[01:45],[02:00],[02:15],[02:30],[02:45],[03:00],
[03:15],[03:30],[03:45],[04:00],[04:15],[04:30],[04:45],[05:00],[05:15],[05:30],[05:45],[06:00],
[06:15],[06:30],[06:45],[07:00],[07:15],[07:30],[07:45],[08:00],[08:15],[08:30],[08:45],[09:00],
[09:15],[09:30],[09:45],[10:00],[10:15],[10:30],[10:45],[11:00],[11:15],[11:30],[11:45],[12:00],
[12:15],[12:30],[12:45],[13:00],[13:15],[13:30],[13:45],[14:00],[14:15],[14:30],[14:45],[15:00],
[15:15],[15:30],[15:45],[16:00],[16:15],[16:30],[16:45],[17:00],[17:15],[17:30],[17:45],[18:00],
[18:15],[18:30],[18:45],[19:00],[19:15],[19:30],[19:45],[20:00],[20:15],[20:30],[20:45],[21:00],
[21:15],[21:30],[21:45],[22:00],[22:15],[22:30],[22:45],[23:00],[23:15],[23:30],[23:45],[00:00]
--INTO [Staging].[PivotedData]
FROM(
SELECT
SerialNumber AS [Serial],
ChannelName AS [Channel],
CAST(ReadingDate AS DATE) AS [ReadingDate],
CAST(ReadingDate AS TIME(0)) AS [ReadingTime],
ChannelValue AS [Value]
FROM [Staging].[UriData]
WHERE
ChannelName IN (SELECT ChannelName FROM Staging.ActiveChannels )
AND ReadingDate > @_DateFrom AND ReadingDate <= @_DateTo
AND SerialNumber = @_SerialNumber
AND Processed = 0
AND ChannelName = 'm1'
) AS [Raw]
PIVOT
(
MAX( [Value] ) FOR [ReadingTime] IN( [00:15],[00:30],[00:45],[01:00],[01:15],[01:30],[01:45],[02:00],
[02:15],[02:30],[02:45],[03:00],[03:15],[03:30],[03:45],[04:00],
[04:15],[04:30],[04:45],[05:00],[05:15],[05:30],[05:45],[06:00],
[06:15],[06:30],[06:45],[07:00],[07:15],[07:30],[07:45],[08:00],
[08:15],[08:30],[08:45],[09:00],[09:15],[09:30],[09:45],[10:00],
[10:15],[10:30],[10:45],[11:00],[11:15],[11:30],[11:45],[12:00],
[12:15],[12:30],[12:45],[13:00],[13:15],[13:30],[13:45],[14:00],
[14:15],[14:30],[14:45],[15:00],[15:15],[15:30],[15:45],[16:00],
[16:15],[16:30],[16:45],[17:00],[17:15],[17:30],[17:45],[18:00],
[18:15],[18:30],[18:45],[19:00],[19:15],[19:30],[19:45],[20:00],
[20:15],[20:30],[20:45],[21:00],[21:15],[21:30],[21:45],[22:00],
[22:15],[22:30],[22:45],[23:00],[23:15],[23:30],[23:45],[00:00])
) AS pvt
ORDER BY ReadingDate DESC, Channel, [Serial]
查询产生以下结果:
-----------------------------------------------------------------------------------------------------------------------------
Serial | Channel | ReadingDate | 00:15 | 00:30 | 00:45 | 01:00 | 01:15 <--- ReadingTime ---> 23:15 | 23:30 | 23:45 | 00:00
-----------------------------------------------------------------------------------------------------------------------------
2209 | m1 | 21/09/2018 | NULL | NULL | NULL | NULL | NULL <----> NULL | NULL | NULL | 20586
2209 | m1 | 20/09/2018 | 20138 | 20140 | 20143 | 20145 | 20148 <----> 20580 | 20582 | 20584 | NULL
-----------------------------------------------------------------------------------------------------------------------------
从查询中可以看到，我两次请求值.我希望第二天的午夜出现在"2018-09-20"行中，因为最后一组读数在午夜交付.我在午夜收到从23:45:00到23:59:59的值.所以我有96个读数大于'2018-09-20 00:00'并且小于或等于'2018-09-21 00:00'.
As you can see from the query, I am requesting values between two times. I want midnight for the next day to appear on the row for the '2018-09-20' as the last set of readings are delivered at midnight. I receive the values that are from 23:45:00 to 23:59:59 at midnight. So I have 96 readings that are greater than '2018-09-20 00:00' and less than or equal to '2018-09-21 00:00'.
预期结果是:
-----------------------------------------------------------------------------------------------------------------------------
Serial | Channel | ReadingDate | 00:15 | 00:30 | 00:45 | 01:00 | 01:15 <--- ReadingTime ---> 23:15 | 23:30 | 23:45 | 00:00
-----------------------------------------------------------------------------------------------------------------------------
2209 | m1 | 20/09/2018 | 20138 | 20140 | 20143 | 20145 | 20148 <----> 20580 | 20582 | 20584 | 20586
-----------------------------------------------------------------------------------------------------------------------------
和往常一样，感谢您的支持.
As usual, thanks for your support.
推荐答案
由于sql-server中存在DateTime数据类型，因此我将ReadingDate和ReadingTime合并为一列.
I would let ReadingDate and ReadingTime combine into one column because There is a DateTime datatype in sql-server.
如果您使用两列，将很难使用.
If you use two columns will be hard to use in condition.
然后您可以使用CASE WHEN设置ReadingDate列来检查数据行是今天还是明天，如果明天是行中的今天.
Then you can use CASE WHEN set ReadingDate column check the data row is today or tomorrow if is tomorrow set the today in the row.
在进行数据透视查询之前.
before your pivot query.
SELECT [Serial],
[Channel],
[ReadingDate],
[00:15],[00:30],[00:45],[01:00],[01:15],[01:30],[01:45],[02:00],[02:15],[02:30],[02:45],[03:00],
[03:15],[03:30],[03:45],[04:00],[04:15],[04:30],[04:45],[05:00],[05:15],[05:30],[05:45],[06:00],
[06:15],[06:30],[06:45],[07:00],[07:15],[07:30],[07:45],[08:00],[08:15],[08:30],[08:45],[09:00],
[09:15],[09:30],[09:45],[10:00],[10:15],[10:30],[10:45],[11:00],[11:15],[11:30],[11:45],[12:00],
[12:15],[12:30],[12:45],[13:00],[13:15],[13:30],[13:45],[14:00],[14:15],[14:30],[14:45],[15:00],
[15:15],[15:30],[15:45],[16:00],[16:15],[16:30],[16:45],[17:00],[17:15],[17:30],[17:45],[18:00],
[18:15],[18:30],[18:45],[19:00],[19:15],[19:30],[19:45],[20:00],[20:15],[20:30],[20:45],[21:00],
[21:15],[21:30],[21:45],[22:00],[22:15],[22:30],[22:45],[23:00],[23:15],[23:30],[23:45],[00:00]
FROM(
SELECT
Serial AS [Serial],
Channel AS [Channel],
(CASE WHEN CAST(ReadingDate AS DATE) > CAST(@_DateFrom AS DATE)
THEN CAST(@_DateFrom AS DATE)
ELSE CAST(ReadingDate AS DATE)
END) AS [ReadingDate],
CAST(ReadingDate AS TIME) AS [ReadingTime],
Value AS [Value]
FROM UriData
WHERE ReadingDate > @_DateFrom AND ReadingDate <= @_DateTo AND Serial = '2209'
) AS [Raw]
PIVOT
(
MAX( [Value] ) FOR [ReadingTime] IN( [00:15],[00:30],[00:45],[01:00],[01:15],[01:30],[01:45],[02:00],
[02:15],[02:30],[02:45],[03:00],[03:15],[03:30],[03:45],[04:00],
[04:15],[04:30],[04:45],[05:00],[05:15],[05:30],[05:45],[06:00],
[06:15],[06:30],[06:45],[07:00],[07:15],[07:30],[07:45],[08:00],
[08:15],[08:30],[08:45],[09:00],[09:15],[09:30],[09:45],[10:00],
[10:15],[10:30],[10:45],[11:00],[11:15],[11:30],[11:45],[12:00],
[12:15],[12:30],[12:45],[13:00],[13:15],[13:30],[13:45],[14:00],
[14:15],[14:30],[14:45],[15:00],[15:15],[15:30],[15:45],[16:00],
[16:15],[16:30],[16:45],[17:00],[17:15],[17:30],[17:45],[18:00],
[18:15],[18:30],[18:45],[19:00],[19:15],[19:30],[19:45],[20:00],
[20:15],[20:30],[20:45],[21:00],[21:15],[21:30],[21:45],[22:00],
[22:15],[22:30],[22:45],[23:00],[23:15],[23:30],[23:45],[00:00])
) AS pvt
ORDER BY ReadingDate DESC, Channel, [Serial]
这篇关于明天的第一个值出现在今天行的最后一个单元格上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
