SQL Query Help

[jetjash]

Hai,

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?

Jash.

[jmo]

Hai,
I tried the below mentioned query’s

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

Not sure about any intricacies of SQL SERVER 2000, but the 'between' clause uses an 'and' between the values:

...between 030001 and 030000

Also...since your time has a leading zero, I'm wondering about your data types, and how the 'between' will evaluate the field. I would think you would want to use a single timestamp field instead of a separate one for date and time.

[mail2saion]

This is a new table or an existing table in production???

[cutepinkbunnies]

Wow. Few issues I noticed with this:

1. There is no group by clause. You'll want to group by Cardno and cardeventdate when trying to aggregate.
2. Look out in your where clause!!
2A. You are passing a zero padded integer to compare to a nvarchar. Not typical programming, and I'm not sure how MSSQL will treat it, letalone with a between statement.
2B. You have the cardeventtime down, but forgot about the dates! Because your tables store dogs where cats fit, you are going to have to do some conversions and query modifications.
2C. Your where clause logic (if it worked) would include any transactions, on any day between the times of 030001 and 030000...so all transactions basically.

I'll try to write up something freehand to help you out (but I'm not promising accuracy!). The query it seemed you were attempting to build would show the cardno and the first and last transaction broken down by date (within cardno).

SQL can be tricky at times but you'll get the hang of it.

Use select cast('20090303 16:28:24' as datetime) to understand how the two columns will need to get parsed to make SQL happy.

SELECT [CARDNO], [CARDEVENTDATE], MIN(CAST([CARDEVENTDATE] + LEFT([CARDEVENTTIME],2) + ':' + RIGHT(LEFT([CARDEVENTTIME],4),2) + ':' + RIGHT([CARDEVENTTIME],2) AS DATETIME) [MINTIME], MAX(CAST([CARDEVENTDATE] + LEFT([CARDEVENTTIME],2) + ':' + RIGHT(LEFT([CARDEVENTTIME],4),2) + ':' + RIGHT([CARDEVENTTIME],2) AS DATETIME) [MAXTIME]
FROM <TABLE>
WHERE CAST([CARDEVENTDATE] + LEFT([CARDEVENTTIME],2) + ':' + RIGHT(LEFT([CARDEVENTTIME],4),2) + ':' + RIGHT([CARDEVENTTIME],2) AS DATETIME) BETWEEN DATEADD(DD,-1,CAST(CONVERT(VARCHAR(20), GETDATE(),112) + ' ' + '03:00:01' AS DATETIME)) AND CAST(CONVERT(VARCHAR(20), GETDATE(),112) + ' ' + '03:00:00' AS DATETIME)
GROUP BY  [CARDNO], [CARDEVENTDATE]
ORDER BY  [CARDNO] DESC, [CARDEVENTDATE] DESC