SQL Query

[Guest]

I have following table which is stored in MS Access database

Col1 Col2 Col3
A 3.2 25/10/2000 9:30:00 AM
A 2.1 25/10/2000 10:10:02 AM
A 1.5 25/10/2000 1:00:02 PM
A 2.3 25/10/2000 2:10:05 PM
A 10 26/10/2000 9:10:10 AM
A 1.2 26/10/2000 9:10:11 AM
A 4.8 26/10/2000 1:00:08 PM

I want to use SQL query so that I can achieve the following:

Problem 1: Pick only those records (At the most one per each day) based on
max value of Col2 (Max value should be within the context of one day). So it
should give me following result set
A 3.2 25/10/2000 9:30:00 AM (Max value picked on Day 25/10/2000)

A 10 26/10/2000 9:10:10 AM (Max value picked on Day 26/10/2000)

Problem 2: Pick only those records (At the most one per each day) based on
min value of Col2 (Min value should be within the context of one day). So it
should give me following result
set
A 1.5 25/10/2000 1:00:02 PM (Min value picked on Day 25/10/2000)

A 1.2 26/10/2000 9:10:11 AM (Min value picked on Day 26/10/2000)

Problem 3: Pick only those records (At the most one per each day) based on
most recent value of Col3 (Most recent value value should be within the
context of one day). So it should give me following result
set
A 2.3 25/10/2000 2:10:05 PM (Most recent value(based on time) picked on
Day 25/10/2000)

A 4.8 26/10/2000 1:00:08 PM (Most recent value(based on time) picked on
Day 26/10/2000)

 

[W.G. Ryan eMVP]

If I understand you correct, you can just strip out the time piece and then
convert the date to datetime so you have the given date with a time
beginning at midnight. So all you need is a SELECT MAX(Column2) from
Whatever Where - now you can either use a Between Midnight today and
MIdnight tomorrow - 1 or use convert(varchar,COLUMN2,101) from
WhateverTable
This should work for the first two - you can just add a Max on the second
column for the third one.

 

[Cor Ligthert]

KDV,

What are the SQL strings that you use?

Cor

 

[Guest]

This is just the data for two days which I showed for an example.
Transactions are added every day so the Col3 will have different value. For
example it can have hundreds of transaction added just in a month. Actually I
could not understand yet how to do this. Could u please just tell me by
posting an SQL query. The idea is that I have to pick only one transaction
from every day even some of the dates may have more than one transaction in
one day. The controlling column will be Col2. If there are more than one
transaction on any day then pick max or min value of col2 from the group of
transactions on that day.

Could u please prepare an SQL query. The table name is table1. The columns
are col1,col2 and col3

Thanks in advance
KDV

 

[Guest]

I haven't got any solution yet. SQL strings??

 

[W.G. Ryan eMVP]

This would work for the first one and second one. IN the second one, I have
a group by clause - take out the where clause and stick in that and you will
get the max or min values for each date.


DECLARE @QueryDate as SMALLDATETIME
SET @QueryDate = '10/25/2000'
SELECT MAX(Col1) From
Test
WHERE Col2 = @QueryDate

DECLARE @QueryDate as SMALLDATETIME
SET @QueryDate = '10/25/2000'
SELECT MIN(Col1) From
Test
WHERE Col2 = @QueryDate
GROUP BY Col2

 

[Guest]

This way I have to loop through all the dates. I cannot get result set with
one query. Looks like this code has to go into the procedure. I have to
investigate if I can use set comparison which is possible in Oracle eg

SELECT * FROM employees
WHERE (first_name, last_name, email) IN
(('Guy', 'Himuro', 'GHIMURO'),('Karen', 'Colmenares', 'KCOLMENA'))

I tried with MS Access 2000 but it complains about commas(WHERE clause) in
the query.

Thanks for your effort
KDV

 

[W.G. Ryan eMVP]

Why do you have to loop? If you use the Group By clause, you'll have all of
the values you need. If you don't need them all you just need one day, then
you can use the query without a Group By in it.

As far as this query, you need three parts to your wehre AFAIK

WHERE first_name IN ('GUY', 'KAREN')
AND last_name IN ('Himuro', 'Colmenares')
etc

If you need them to correspond, ie Guy and Humero, then you can use a Case
statement in T-SQL or Decode in PL-SQL

 

[Guest]

I think I have found the solution. There is one more constraint which is that
there will be no duplicate value of Col2 on a particular day eg on 25/10/2000
all the values for col2 will be unique. Same is true for other dates. Since I
am not an expert in Database and also do not know PL/SQL. So what I did is
prepare a result set of joint values of col2 and col3 based on max/min of
col2 and group by col3 (without time part). This result set will be put as
subquery in the main query. This will solve all of my problems.

Thanks for your effort
KDV

 

[Guest]

Thanks for the reply. Another guy helped me. Here is the solution which
worked fine.

problem 1
Code:
select Col1
, Col2
, Col3
from yourtable as X
where Col2
= ( select max(Col2)
from yourtable
where format(Col3,'yyyymmdd')
= format(X.Col3.'yyyymmdd') )

problem 2 change max to min above

problem 3
Code:
select Col1
, Col2
, Col3
from yourtable as X
where Col3
= ( select max(Col3)
from yourtable
where format(Col3,'yyyymmdd')
= format(X.Col3,'yyyymmdd') )