SQL Query Help

Question

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)

Answer 1

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 )

Answer 2

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.

Answer 3

couldn't you just call:

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

Answer 4

WHERE(Cardeventtime between 030001 and 120000) - BUT HOW IT WILL TAKE A PREVIOUS DAY DATA'S AND (cardeventtime between 000000 and 030000) - IS TODAY OK

Answer 5

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]

Answer 6

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

Answer 7

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

Answer 8

Hai,

Am Using SQL SERVER - 2000

Table Structure

CARDEVENTDATE CARDEVENTTIME CARDNO 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?

Jash.

Answer 9

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.

Answer 10

Two Tables

T_Person – Table 1

CARDNO

168 471 488 247 519 518 331 240 518 386 441 331

T_Cardevent – Table 2

CARDEVENTDATE CARDEVENTTIME

20090225 163932
20090225 164630
20090225 165027
20090225 165137
20090225 165147
20090225 165715
20090225 165749
20090303 162059
20090303 162723
20090303 155029
20090303 155707
20090303 162824

CARDEVENTTIME VALUE IS 6 NUMBERS NOT A 5 NUMBERS, SO NO NEED TO ADD ANYTHING.

I WANT A DATA FROM PARTICULAR DATE TO PARTICULAR DATE FOR THAT CARDNO’s

Query

SELECT T_PERSON.CARDNO, T_CARDEVENT.CARDEVENTDATE, MIN(T_CARDEVENT.CARDEVENTTIME), T_CARDEVENT.CARDEVENTDATE, CASE WHEN MIN (cardeventtime) = MAX(cardeventtime) THEN 'Nodata' ELSE MAX(cardeventtime) END AS OUTTIME
FROM T_PERSON LEFT OUTER JOIN T_CARDEVENT ON T_PERSON.CARDNO = T_CARDEVENT.CARDNO WHERE T_CARDEVENT.CARDEVENTDATE BETWEEN '" & sdate & "' AND '" & edate & "' AND T_CARDEVENT.CARDEVENTTIME BETWEEN 030001 AND 030000 GROUP BY T_PERSON.CARDNO, T_CARDEVENT.CARDEVENTDATE ORDER BY CARDNO, CARDEVENTDATE

'" & sdate & "' – From date '" & edate & "' – To date

T_CARDEVENT.CARDEVENTTIME BETWEEN 030001 AND 030000 – This is problem to me because it is taking today 03:00:01 to 03:00:00

I need in time and out time of the particular cardno from this date to this date.

But this time should take 03:00:01 to 03:00:00 means (yesterday 03 am to today 03 am) The Time should change 03:00:01 to 03:00:00 instead of 00:00:01 to 23:59:59

I need the sql query for the above condition. Help me?

Answer 11

van,

Card No, IN Date, In Time, Out date,Out Time 0001, 20090502, 030001, 20090502, 030000 0002, 20090502, 031000, 20090502, 025050 .......... –

From the above table

((1000000*CAST(CardEventDate AS BIGINT) + CAST(CardEventTime AS BIGINT))-30000)/1000000 AS CardEvenDateAdjusted - For this query date is displaying correctly according to that time 03:00:01 to 03:00:00, how can i get min(time) and Max(time)

Need query Help urgent