当前位置： 文江博客话题详情

SQL 查询帮助

发布于 2024-07-22 07:38:32 字数 1577 浏览 5 评论 0原文

使用 SQL SERVER - 2000

表结构

CARDEVENTDATE       CARDEVENTTIME   CARDNO
20090224            92007           485
20090224            92345           321
20090225            163932          168
20090225            164630          471
20090225            165027          488
20090225            165137          247
20090225            165147          519
20090225            165715          518
20090225            165749          331
20090303            162059          240
20090303            162723          518
20090303            155029          386
20090303            155707          441
20090303    162824  331

Cardeventdate 和 Cardeventtime - nvarchar 数据类型 日期和时间是单独的列

我想获取

昨天 03:00:01 AM 到今天 03:00:00 AM 之间的数据 前天 03:00:01 AM 至 昨天 03:00:00 AM 所以……

我尝试了下面提到的查询，

Select Cardno, cardeventdate, min(cardeventtime), max(cardeventtime) 
from table 
where cardeventtime between 030001 to 030000

Select Cardno, Cardeventdate, Min(cardeventtime), max(cardeventtime) 
from table 
where Cardeventtime >030001 and  Cardeventtime < 030000

结果中没有显示任何内容，因为它需要今天的时间从凌晨 03.00 到凌晨 03.01

Select Cardno, Cardeventdate, min (cardeventtime), max (cardeventtime) 
from table 
where cardeventtime < 030000 and cardeventtime > previous day time – query help

正是我需要昨天上午 03.00.01 到今天凌晨 03.00.00 的数据，前天 03.00。上午 01 点到昨天凌晨 03 点 00 分数据………………。 所以

我需要上述条件的sql查询。 谁能帮我？

（编辑：我需要从昨天早上到今天早上的日期，截至凌晨 03 点）

原文

Am Using SQL SERVER - 2000

Table Structure

CARDEVENTDATE       CARDEVENTTIME   CARDNO
20090224            92007           485
20090224            92345           321
20090225            163932          168
20090225            164630          471
20090225            165027          488
20090225            165137          247
20090225            165147          519
20090225            165715          518
20090225            165749          331
20090303            162059          240
20090303            162723          518
20090303            155029          386
20090303            155707          441
20090303    162824  331

Cardeventdate and Cardeventtime - nvarchar data type
Date and Time is separate column

I want to get a data between

Yesterday 03:00:01 AM to today 03:00:00 AM
Day before yesterday 03:00:01 AM to yesterday 03:00:00 AM
So On……..

I tried the below mentioned query’s

Select Cardno, cardeventdate, min(cardeventtime), max(cardeventtime) 
from table 
where cardeventtime between 030001 to 030000

Select Cardno, Cardeventdate, Min(cardeventtime), max(cardeventtime) 
from table 
where Cardeventtime >030001 and  Cardeventtime < 030000

Nothing displayed in the result because it is taking today time from 03.00 am to 03.01am

Select Cardno, Cardeventdate, min (cardeventtime), max (cardeventtime) 
from table 
where cardeventtime < 030000 and cardeventtime > previous day time – query help

Exactly I need yesterday 03.00.01 am to today 03.00.00 am data’s, Day before yesterday 03.00.01 am to yesterday 03.00.00 am data’s …………………. So on

I need the sql query for the above condition. Can any one help me?

(edit: I need the date from yesterday morning to this morning, up to 03.00am)

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（7）

毁我热情 2024-07-29 07:38:33

该脚本是 SQLServer2005，但使用#Temp 表，您应该能够将其转换为可用的 SQLServer2000 脚本。

如果我理解正确的话，以下脚本将按 dd 03:00:00 - dd+1 对记录进行分组
03:00:00

DECLARE @Table TABLE (
  CARDEVENTDATE INTEGER
  , CARDEVENTTIME INTEGER
  , CARDNO INTEGER)

DECLARE @TableDateTime TABLE (
  CARDEVENTDATETIME DATETIME
)

INSERT INTO @Table VALUES (20090224,92007,485)
INSERT INTO @Table VALUES (20090224,92345,321)
INSERT INTO @Table VALUES (20090225,163932,168)
INSERT INTO @Table VALUES (20090225,164630,471)
INSERT INTO @Table VALUES (20090225,165027,488)
INSERT INTO @Table VALUES (20090225,165137,247)
INSERT INTO @Table VALUES (20090225,165147,519)
INSERT INTO @Table VALUES (20090225,165715,518)
INSERT INTO @Table VALUES (20090225,165749,331)
INSERT INTO @Table VALUES (20090303,162059,240)
INSERT INTO @Table VALUES (20090303,162723,518)
INSERT INTO @Table VALUES (20090303,155029,386)
INSERT INTO @Table VALUES (20090303,155707,441)
INSERT INTO @Table VALUES (20090303,162824,331)

INSERT INTO @TableDateTime
SELECT 
  [DATETIME-3] = 
    DATEADD(MONTH, ((CARDEVENTDATE/10000-1900)*12)+(CARDEVENTDATE/100)%100-1, CARDEVENTDATE%100-1) 
    + DATEADD(SS, (((CARDEVENTTIME/10000)-3)*3600)+(((CARDEVENTTIME/100)%100)*60)+CARDEVENTTIME%100, 0)
FROM @Table

SELECT CAST(CAST(tdt1.CARDEVENTDATETIME-1 AS INTEGER) AS DATETIME), COUNT(*)
FROM @TableDateTime tdt1
GROUP BY CAST(tdt1.CARDEVENTDATETIME-1 AS INTEGER)

/*
  What do all the casts, div's and mods mean
*/
SELECT
  /* Split */
  [YEAR] = CARDEVENTDATE / 10000
  , [MONTH] = (CARDEVENTDATE / 100) % 100
  , [DAY] = CARDEVENTDATE % 100
  , [HOUR] = CARDEVENTTIME / 10000  
  , [MINUTE] = (CARDEVENTTIME / 100) % 100
  , [SECOND] = CARDEVENTTIME % 100  
  /* Date & Time */
  , [DATE] = DATEADD(MONTH, ((CARDEVENTDATE/10000-1900)*12)+(CARDEVENTDATE/100)%100-1, CARDEVENTDATE%100-1)
  , [TIME] = DATEADD(SS, (CARDEVENTTIME/10000*3600)+(((CARDEVENTTIME/100)%100)*60)+CARDEVENTTIME%100, 0)
  /* DateTime */
  , [DATETIME] = 
      DATEADD(MONTH, ((CARDEVENTDATE/10000-1900)*12)+(CARDEVENTDATE/100)%100-1, CARDEVENTDATE%100-1)
      + DATEADD(SS, (CARDEVENTTIME/10000*3600)+(((CARDEVENTTIME/100)%100)*60)+CARDEVENTTIME%100, 0)
  , [DATETIME-3] = 
      DATEADD(MONTH, ((CARDEVENTDATE/10000-1900)*12)+(CARDEVENTDATE/100)%100-1, CARDEVENTDATE%100-1) 
      + DATEADD(SS, (((CARDEVENTTIME/10000)-3)*3600)+(((CARDEVENTTIME/100)%100)*60)+CARDEVENTTIME%100, 0)
FROM @Table
ORDER BY [DATETIME]

The script is SQLServer2005 but using #Temp tables, you should be able to convert it to a usable SQLServer2000 script.

If I understand you correctly, following script groups the records by dd 03:00:00 - dd+1
03:00:00

DECLARE @Table TABLE (
  CARDEVENTDATE INTEGER
  , CARDEVENTTIME INTEGER
  , CARDNO INTEGER)

DECLARE @TableDateTime TABLE (
  CARDEVENTDATETIME DATETIME
)

INSERT INTO @Table VALUES (20090224,92007,485)
INSERT INTO @Table VALUES (20090224,92345,321)
INSERT INTO @Table VALUES (20090225,163932,168)
INSERT INTO @Table VALUES (20090225,164630,471)
INSERT INTO @Table VALUES (20090225,165027,488)
INSERT INTO @Table VALUES (20090225,165137,247)
INSERT INTO @Table VALUES (20090225,165147,519)
INSERT INTO @Table VALUES (20090225,165715,518)
INSERT INTO @Table VALUES (20090225,165749,331)
INSERT INTO @Table VALUES (20090303,162059,240)
INSERT INTO @Table VALUES (20090303,162723,518)
INSERT INTO @Table VALUES (20090303,155029,386)
INSERT INTO @Table VALUES (20090303,155707,441)
INSERT INTO @Table VALUES (20090303,162824,331)

INSERT INTO @TableDateTime
SELECT 
  [DATETIME-3] = 
    DATEADD(MONTH, ((CARDEVENTDATE/10000-1900)*12)+(CARDEVENTDATE/100)%100-1, CARDEVENTDATE%100-1) 
    + DATEADD(SS, (((CARDEVENTTIME/10000)-3)*3600)+(((CARDEVENTTIME/100)%100)*60)+CARDEVENTTIME%100, 0)
FROM @Table

SELECT CAST(CAST(tdt1.CARDEVENTDATETIME-1 AS INTEGER) AS DATETIME), COUNT(*)
FROM @TableDateTime tdt1
GROUP BY CAST(tdt1.CARDEVENTDATETIME-1 AS INTEGER)

/*
  What do all the casts, div's and mods mean
*/
SELECT
  /* Split */
  [YEAR] = CARDEVENTDATE / 10000
  , [MONTH] = (CARDEVENTDATE / 100) % 100
  , [DAY] = CARDEVENTDATE % 100
  , [HOUR] = CARDEVENTTIME / 10000  
  , [MINUTE] = (CARDEVENTTIME / 100) % 100
  , [SECOND] = CARDEVENTTIME % 100  
  /* Date & Time */
  , [DATE] = DATEADD(MONTH, ((CARDEVENTDATE/10000-1900)*12)+(CARDEVENTDATE/100)%100-1, CARDEVENTDATE%100-1)
  , [TIME] = DATEADD(SS, (CARDEVENTTIME/10000*3600)+(((CARDEVENTTIME/100)%100)*60)+CARDEVENTTIME%100, 0)
  /* DateTime */
  , [DATETIME] = 
      DATEADD(MONTH, ((CARDEVENTDATE/10000-1900)*12)+(CARDEVENTDATE/100)%100-1, CARDEVENTDATE%100-1)
      + DATEADD(SS, (CARDEVENTTIME/10000*3600)+(((CARDEVENTTIME/100)%100)*60)+CARDEVENTTIME%100, 0)
  , [DATETIME-3] = 
      DATEADD(MONTH, ((CARDEVENTDATE/10000-1900)*12)+(CARDEVENTDATE/100)%100-1, CARDEVENTDATE%100-1) 
      + DATEADD(SS, (((CARDEVENTTIME/10000)-3)*3600)+(((CARDEVENTTIME/100)%100)*60)+CARDEVENTTIME%100, 0)
FROM @Table
ORDER BY [DATETIME]
回复 原文
尹雨沫 2024-07-29 07:38:33

第 1 步

首先将包含日期和时间的两列放在 datetime 类型的一列中（即 carddate）。

第 2 步
如果您想根据一天中的某些分割时间进行分组，请执行以下操作（示例中的分割时间为凌晨 3 点）：

select min(carddate) as Minimum, max(carddate) as Maximum
from sampleTable
group by
    year(dateadd(hour, -3, carddate)),
    month(dateadd(hour, -3, carddate)),
    day(dateadd(hour, -3, carddate))

但是您将无法像这样简单地获取组内的卡号。 您必须使用用户功能来实现这种功能

Step 1

First put both columns with date and time together in one column (i.e. carddate) of type datetime.

Step 2
If you want to do grouping based on some split time during the day do something like this (split time in example 3 AM):

select min(carddate) as Minimum, max(carddate) as Maximum
from sampleTable
group by
    year(dateadd(hour, -3, carddate)),
    month(dateadd(hour, -3, carddate)),
    day(dateadd(hour, -3, carddate))

But you won't be able to get card numbers within groups as simple as this. You'll have to use user function for that kind of functionality

回复 原文
花开半夏魅人心 2024-07-29 07:38:33

仅使用查询的解决方案：

SELECT  CardEventDate, CardEventTime, CardNo
        ,(1000000*CAST(CardEventDate AS BIGINT) + CAST(CardEventTime AS BIGINT)) AS CardEventDateTimeINT
        ,((1000000*CAST(CardEventDate AS BIGINT) + CAST(CardEventTime AS BIGINT))-150000)/1000000 AS StartDate
FROM    "table"
WHERE   ((1000000*CAST(CardEventDate AS BIGINT) + CAST(CardEventTime AS BIGINT))-150000)/1000000 = '20090223'

参数为：

  • '20090223' - 24 小时周期（您的过滤器）的开始日期
  • WHERE 子句中的 150000 表示开始时间 15:00:00。 更改它会给你不同的 24 小时范围。 如果确切时间应该转到另一个日期，您可能希望它为 150001。
    • 删除过滤器（WHERE 子句）将为您提供所有结果，如果您取消注释“StartDate”列，您可以稍后进一步过滤这些结果

它的实现方式是根据您的日期和时间组成 DATETIME，但使用 not一个 DATETIME 数据类型，但只是一个 BIGINT，因此 20090224092007 的值将是 20090224 的日期和 092007 的时间。最重要的是它仍然是可排序的，并且只需使用 INTEGER 除法即可轻松切断/转移购买。 这就是我买的东西减去150000（时间15:00:00）。

结果（使用上面查询中的过滤器）是：

CardEventDate CardEventTime CardNo CardEventDateTimeINT StartDate
------------- ------------- ------ -------------------- --------------------
20090224      92007         485    20090224092007       20090223
20090224      92345         321    20090224092345       20090223

下面是没有任何过滤器的结果，以便您稍后可以按 StartDate 进行过滤：

CardEventDate CardEventTime CardNo CardEventDateTimeINT StartDate
------------- ------------- ------ -------------------- --------------------
20090224      92007         485    20090224092007       20090223
20090224      92345         321    20090224092345       20090223
20090225      163932        168    20090225163932       20090225
20090225      164630        471    20090225164630       20090225
20090225      165027        488    20090225165027       20090225
20090225      165137        247    20090225165137       20090225
20090225      165147        519    20090225165147       20090225
20090225      165715        518    20090225165715       20090225
20090225      165749        331    20090225165749       20090225
20090303      162059        240    20090303162059       20090303
20090303      162723        518    20090303162723       20090303
20090303      155029        386    20090303155029       20090303
20090303      155707        441    20090303155707       20090303
20090303      162824        331    20090303162824       20090303

这是带有 GROUP BY、MIN(..) 和 MAX(..) 的版本：

SELECT  CardNo, 
        ((1000000*CAST(CardEventDate AS BIGINT) + CAST(CardEventTime AS BIGINT))-30000)/1000000 AS CardEvenDateAdjusted, 
        MIN(CardEventTime) AS MinTime, 
        MAX(CardEventTime) AS MaxTime
        --,COUNT(*) AS NUM
FROM    "table"
GROUP BY CardNo, ((1000000*CAST(CardEventDate AS BIGINT) + CAST(CardEventTime AS BIGINT))-30000)/1000000

注意有几点：

  • CardEvenDateAdjusted 对于第二天的 00:00:00 和 03:00:00 之间的
  • MinTime 来说并不完全正确，小于 030000 意味着它实际上是第二天，所以事实上它不是确实是 MIN...
  • 如果您的 CardEventTime 是字符串（不是整数），那么您必须确保它有前导零（020000，而不是 20000），否则 MIN、MAX 将无法正确排序，因此 20000 高于 150000

再次强调：除非您重新提出一个好问题，否则您将不会得到您正在寻找的答案。

Solution using just a query:

SELECT  CardEventDate, CardEventTime, CardNo
        ,(1000000*CAST(CardEventDate AS BIGINT) + CAST(CardEventTime AS BIGINT)) AS CardEventDateTimeINT
        ,((1000000*CAST(CardEventDate AS BIGINT) + CAST(CardEventTime AS BIGINT))-150000)/1000000 AS StartDate
FROM    "table"
WHERE   ((1000000*CAST(CardEventDate AS BIGINT) + CAST(CardEventTime AS BIGINT))-150000)/1000000 = '20090223'

The parameters are:

  • '20090223' - the start date for your 24h period (your filter)
  • 150000 in the WHERE clause is representing start time of 15:00:00. Changing it will give you different 24h ranges. You may want it to be 150001, if exact time should go to another date.
    • Removing the filter (WHERE clause) will give you all results, which you can further filter later if you uncomment a column 'StartDate'

The way it is implemented is by composing a DATETIME from your date and time, but using not a DATETIME data type, but just a BIGINT, so that value of 20090224092007 would be date of 20090224 and time of 092007. Most important thing is that it is still sortable and it is easy to cut off/shift buy just using the INTEGER division. That is what I do buy substracting 150000 (time 15:00:00).

The result (with filter as in query above) is:

CardEventDate CardEventTime CardNo CardEventDateTimeINT StartDate
------------- ------------- ------ -------------------- --------------------
20090224      92007         485    20090224092007       20090223
20090224      92345         321    20090224092345       20090223

The result without any filter is below, so that you can filter by StartDate later:

CardEventDate CardEventTime CardNo CardEventDateTimeINT StartDate
------------- ------------- ------ -------------------- --------------------
20090224      92007         485    20090224092007       20090223
20090224      92345         321    20090224092345       20090223
20090225      163932        168    20090225163932       20090225
20090225      164630        471    20090225164630       20090225
20090225      165027        488    20090225165027       20090225
20090225      165137        247    20090225165137       20090225
20090225      165147        519    20090225165147       20090225
20090225      165715        518    20090225165715       20090225
20090225      165749        331    20090225165749       20090225
20090303      162059        240    20090303162059       20090303
20090303      162723        518    20090303162723       20090303
20090303      155029        386    20090303155029       20090303
20090303      155707        441    20090303155707       20090303
20090303      162824        331    20090303162824       20090303

This is the version with GROUP BY, MIN(..) and MAX(..):

SELECT  CardNo, 
        ((1000000*CAST(CardEventDate AS BIGINT) + CAST(CardEventTime AS BIGINT))-30000)/1000000 AS CardEvenDateAdjusted, 
        MIN(CardEventTime) AS MinTime, 
        MAX(CardEventTime) AS MaxTime
        --,COUNT(*) AS NUM
FROM    "table"
GROUP BY CardNo, ((1000000*CAST(CardEventDate AS BIGINT) + CAST(CardEventTime AS BIGINT))-30000)/1000000

Note few things:

  • CardEvenDateAdjusted will not be exactly correct for those between 00:00:00 and 03:00:00 of the next day
  • MinTime which is less then 030000 will mean that it is actually of the next day, so in fact it is not really a MIN...
  • If your CardEventTime is sting (not integer), then you must ensure that it has leading zeros (020000, not 20000), otherwise the MIN, MAX will not sort it properly, so that 20000 is higher then 150000.

Again: unless you re-ask a GOOD question, you will NOT get an answer you are looking for.

回复 原文
橘虞初梦 2024-07-29 07:38:32

以下是 SQL Server 2005 Express 上的测试。 如果 SQL Server 2000 没有公用表表达式 (CTE)，则可以使用视图来生成与“cteTbl”相同的结果。 我确实希望 SQL Server 2000 支持 CASE-WHEN-END。

我在此使用的想法是延长一天的时间，从 24 小时到 27 小时 (+3)，但仅当 [CardEventTime] 小于或等于 03:00:00 (24 小时) 时，我将 24 小时添加到 [ CardEventDate] 减去 1d。

CREATE TABLE tbl (
  CardEventDate INTEGER
  ,CardEventTime INTEGER
  ,CardNo INTEGER
)

INSERT INTO tbl VALUES (20090224,92007,485)
INSERT INTO tbl VALUES (20090224,92345,321)
INSERT INTO tbl VALUES (20090225,163932,168)
INSERT INTO tbl VALUES (20090225,164630,471)
INSERT INTO tbl VALUES (20090225,165027,488)
INSERT INTO tbl VALUES (20090225,165137,247)
INSERT INTO tbl VALUES (20090225,165147,519)
INSERT INTO tbl VALUES (20090225,165715,518)
INSERT INTO tbl VALUES (20090225,165749,331)
INSERT INTO tbl VALUES (20090303,162059,240)
INSERT INTO tbl VALUES (20090303,162723,518)
INSERT INTO tbl VALUES (20090303,155029,386)
INSERT INTO tbl VALUES (20090303,155707,441)
INSERT INTO tbl VALUES (20090303,162824,331)

-- Some boundary test values, for only one cardno.
INSERT INTO tbl VALUES (20090330,235959,331)
INSERT INTO tbl VALUES (20090331,000000,331)
INSERT INTO tbl VALUES (20090331,025959,331)
INSERT INTO tbl VALUES (20090331,030000,331)
INSERT INTO tbl VALUES (20090331,030001,331)
INSERT INTO tbl VALUES (20090331,235959,331)
INSERT INTO tbl VALUES (20090401,000000,331)
INSERT INTO tbl VALUES (20090401,025959,331)
INSERT INTO tbl VALUES (20090401,030000,331)
INSERT INTO tbl VALUES (20090401,030001,331)
go

WITH 
cteTbl AS (
  SELECT
    CardEventDate,
    CardEventTime,
    CardNo,
    CASE 
      WHEN CardEventTime <= 30000 THEN dateadd(dd, -1, cast(CardEventDate AS VARCHAR))
      WHEN CardEventTime > 30000 THEN cast(cast(CardEventDate AS VARCHAR) AS DATETIME)
    END AS ShiftedCardEventDate,
    CASE 
      WHEN CardEventTime <= 30000 THEN CardEventTime+240000
      WHEN CardEventTime > 30000 THEN CardEventTime
    END AS ShiftedCardEventTime
  FROM tbl
)
SELECT
  CardNo, 
  ShiftedCardEventDate,
  --min(shiftedCardEventTime) as [MinCardEventTime], 
  --max(shiftedCardEventTime) as [MaxCardEventTime],
  right('000000'+cast((min(shiftedCardEventTime) % 240000) AS VARCHAR), 6) AS [NormalizedMinTime],  
  right('000000'+cast((max(shiftedCardEventTime) % 240000) AS VARCHAR), 6) AS [NormalizedMaxTime]
FROM cteTbl
GROUP BY 
  CardNo, 
  ShiftedCardEventDate

The below is testet on SQL Server 2005 Express. If SQL Server 2000 does not have Common Table Expressions (CTE), a view could be used instead to produce the same as 'cteTbl' would. And I do hope that SQL Server 2000 supports the CASE-WHEN-END.

The idea I use in this, is to extend the hours a day have, from 24h to 27h (+3), but only where [CardEventTime] is less or equal to 03:00:00 (24h) I add 24h and to [CardEventDate] subtract 1d.

CREATE TABLE tbl (
  CardEventDate INTEGER
  ,CardEventTime INTEGER
  ,CardNo INTEGER
)

INSERT INTO tbl VALUES (20090224,92007,485)
INSERT INTO tbl VALUES (20090224,92345,321)
INSERT INTO tbl VALUES (20090225,163932,168)
INSERT INTO tbl VALUES (20090225,164630,471)
INSERT INTO tbl VALUES (20090225,165027,488)
INSERT INTO tbl VALUES (20090225,165137,247)
INSERT INTO tbl VALUES (20090225,165147,519)
INSERT INTO tbl VALUES (20090225,165715,518)
INSERT INTO tbl VALUES (20090225,165749,331)
INSERT INTO tbl VALUES (20090303,162059,240)
INSERT INTO tbl VALUES (20090303,162723,518)
INSERT INTO tbl VALUES (20090303,155029,386)
INSERT INTO tbl VALUES (20090303,155707,441)
INSERT INTO tbl VALUES (20090303,162824,331)

-- Some boundary test values, for only one cardno.
INSERT INTO tbl VALUES (20090330,235959,331)
INSERT INTO tbl VALUES (20090331,000000,331)
INSERT INTO tbl VALUES (20090331,025959,331)
INSERT INTO tbl VALUES (20090331,030000,331)
INSERT INTO tbl VALUES (20090331,030001,331)
INSERT INTO tbl VALUES (20090331,235959,331)
INSERT INTO tbl VALUES (20090401,000000,331)
INSERT INTO tbl VALUES (20090401,025959,331)
INSERT INTO tbl VALUES (20090401,030000,331)
INSERT INTO tbl VALUES (20090401,030001,331)
go

WITH 
cteTbl AS (
  SELECT
    CardEventDate,
    CardEventTime,
    CardNo,
    CASE 
      WHEN CardEventTime <= 30000 THEN dateadd(dd, -1, cast(CardEventDate AS VARCHAR))
      WHEN CardEventTime > 30000 THEN cast(cast(CardEventDate AS VARCHAR) AS DATETIME)
    END AS ShiftedCardEventDate,
    CASE 
      WHEN CardEventTime <= 30000 THEN CardEventTime+240000
      WHEN CardEventTime > 30000 THEN CardEventTime
    END AS ShiftedCardEventTime
  FROM tbl
)
SELECT
  CardNo, 
  ShiftedCardEventDate,
  --min(shiftedCardEventTime) as [MinCardEventTime], 
  --max(shiftedCardEventTime) as [MaxCardEventTime],
  right('000000'+cast((min(shiftedCardEventTime) % 240000) AS VARCHAR), 6) AS [NormalizedMinTime],  
  right('000000'+cast((max(shiftedCardEventTime) % 240000) AS VARCHAR), 6) AS [NormalizedMaxTime]
FROM cteTbl
GROUP BY 
  CardNo, 
  ShiftedCardEventDate
回复 原文
孤独患者 2024-07-29 07:38:32

你不能直接打电话：

select *
from someTable
where cardeventtime not between 30000 and 30001
order by cardeventdate, cardeventtime

couldn't you just call:

select *
from someTable
where cardeventtime not between 30000 and 30001
order by cardeventdate, cardeventtime
回复 原文
从此见与不见 2024-07-29 07:38:32

查询中的日期格式似乎是特定于实现的。

对 SQLServer2000，您希望查询使用日期作为日期 - 而不是数字。 您确实应该有一个“日期时间”类型的字段，而不是两个单独的字段。 但如果您无法控制这一点，那么您将需要进行一些日期时间添加。 我举个例子。

尝试这样的事情：

SELECT * FROM table
WHERE
  (DATEDIFF(dd, GetDate(), CARDEVENTDATE) = 1  AND  CARDEVENTTIME > 030001 )  OR
  (DATEDIFF(dd, GetDate(), CARDEVENTDATE) = 0  AND  CARDEVENTTIME < 030000 )

Date formatting in a query seems to be implementation specific.

Right SQLServer2000, You want to make your query to use the dates as dates - not as numbers. You really should have a single field that is a 'datetime' type, and not two separate fields. But if you don't have control over this, then you'll need to do some datetime addition. I'll pull up an example.

Try something like this:

SELECT * FROM table
WHERE
  (DATEDIFF(dd, GetDate(), CARDEVENTDATE) = 1  AND  CARDEVENTTIME > 030001 )  OR
  (DATEDIFF(dd, GetDate(), CARDEVENTDATE) = 0  AND  CARDEVENTTIME < 030000 )
回复 原文
热风软妹 2024-07-29 07:38:32

这是不正确的，因为条件始终为假：（您将日期存储为整数而不是日期时间，并且整数无法知道它应该在午夜环绕）

Select Cardno, Cardeventdate, Min(cardeventtime), 
  max(cardeventtime) from table where Cardeventtime >030001 
      and Cardeventtime < 030000

查询应该这样写：

Select Cardno, Cardeventdate, Min(cardeventtime), 
  max(cardeventtime) from table where (Cardeventtime between 030001 and 120000)
      OR (cardeventtime between 000000 and 030000)

一旦将其纳入其他查询中，您应该能够找到解决方案。

编辑：抱歉，查询中的意思是 OR 而不是 AND。

This would be incorrect because the condition will always be false: (you're storing your date as an integer instead of a datetime, and an integer has no way of knowing it should wrap around at midnight)

Select Cardno, Cardeventdate, Min(cardeventtime), 
  max(cardeventtime) from table where Cardeventtime >030001 
      and Cardeventtime < 030000

The query should be written like this instead:

Select Cardno, Cardeventdate, Min(cardeventtime), 
  max(cardeventtime) from table where (Cardeventtime between 030001 and 120000)
      OR (cardeventtime between 000000 and 030000)

Once you factor this into your other queries, you should be able to come up with a solution.

Edit: sorry, meant OR instead of AND in the query.

回复 原文
~没有更多了~

关于作者

ヤ经典坏疍

暂无简介

0 文章
0 评论
23 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

淡笑忘祈一世凡恋

文章 0 评论 0

我们的影子

文章 0 评论 0

素年丶

文章 0 评论 0

南笙

文章 0 评论 0

18215568913

文章 0 评论 0

qq_xk7Ean

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文