sql query - Selecting last recorded values for each date in specified period

[SuperbrainBug]

Hello,

Can someone please help me with my problem.

I'm trying to get last recorded balance for each day for specific box (1 or 2) in specified period of days from ms access database using ADOTool.

I'm trying to get that information with SQL query but so far unsuccessfully... 😞 

My table looks like this:

Table name: TestTable

Date         Time      Location  Box  Balance
20.10.2014.  06:00:00     1       1    345
20.10.2014.  12:00:00     1       1    7356
20.10.2014.  18:45:00     1       1    5678
20.10.2014.  23:54:00     1       1    9845
20.10.2014.  06:00:02     1       2    35
20.10.2014.  12:00:04     1       2    756
20.10.2014.  18:45:06     1       2    578
20.10.2014.  23:54:10     1       2    845
21.10.2014.  06:00:00     1       1    34
21.10.2014.  12:05:03     1       1    5789
21.10.2014.  15:00:34     1       1    1237
21.10.2014.  06:00:00     1       2    374
21.10.2014.  12:05:03     1       2    54789
21.10.2014.  15:00:34     1       2    13237
22.10.2014.  06:00:00     1       1    8562
22.10.2014.  10:00:00     1       1    1234
22.10.2014.  17:03:45     1       1    3415
22.10.2014.  22:00:00     1       1    6742
22.10.2014.  06:00:05     1       2    562
22.10.2014.  10:00:16     1       2    123
22.10.2014.  17:03:50     1       2    415
22.10.2014.  22:00:10     1       2    642
23.10.2014.  06:00:00     1       1    9876
23.10.2014.  09:13:00     1       1    223
23.10.2014.  13:50:17     1       1    7768
23.10.2014.  19:47:40     1       1    3456
23.10.2014.  21:30:00     1       1    789
23.10.2014.  23:57:12     1       1    25
23.10.2014.  06:00:07     1       2    976
23.10.2014.  09:13:45     1       2    223
23.10.2014.  13:50:40     1       2    78
23.10.2014.  19:47:55     1       2    346
23.10.2014.  21:30:03     1       2    89
23.10.2014.  23:57:18     1       2    25
24.10.2014.  06:00:55     1       1    346
24.10.2014.  12:30:22     1       1    8329
24.10.2014.  23:50:19     1       1    2225
24.10.2014.  06:01:00     1       2    3546
24.10.2014.  12:30:26     1       2    89
24.10.2014.  23:51:10     1       2    25
...

Let's say the period is 21.10.2014. - 23.10.2014. and I want to get last recorded balance for box 1. for each day. The result should look like this:

Date         Time      Location  Box  Balance
21.10.2014.  15:00:34     1       1    1237
22.10.2014.  22:00:00     1       1    6742
23.10.2014.  23:57:12     1       1    25

So far I've managed to write a query that gives me balance for ONLY ONE date (date with highest time in whole table), but I need balance for EVERY date in specific period.

My incorrect code (didn't manage to implement "BETWEEN" for dates...):

SELECT TestTable.[Date], TestTable.[Time], TestTable.[Location], TestTable.[Box], TestTable.[Balance]
FROM TestTable
WHERE Time=(SELECT MAX(Time)
FROM TestTable
WHERE Location=1 AND Box=1 );

Tnx!

[Yamaeda]

I think it's enough to add Group by date.
/Y

[Ranjeet_Singh]

Simple do one thing. Say user entering T1 and T2

select T1 all record and sort by descending and read 1st record... Do this till T2

1.select * from TestTable where time = lT1' sort DESC

2. Read 1st record

3. repeat 1 and 2 till T2

[Ranjeet_Singh]

Or method 2

----------------------------------------

select top 1 * from table where date='2014-11-27 20:28:00.000' order by date desc.

This will display first record.(i.e last record) of that date....If you want 10 record then top 10 etc

[PBP]

For loop

following query keep day (here 24 in below query) Variable from ( 1 to 28-29/30/31 as per month)

SELECT TOP 1 TestTable.[Date], TestTable.[Time], TestTable.[Location], TestTable.[Box], TestTable.[Balance]

FROM Test Table.

WHERE  Time=(SELECT MAX(Time) FROM TestTable WHERE Location=1 AND Box=1 )

AND DATE = "2014-10-24";

[SuperbrainBug]

Thank you guys!

I've solved the problem.

i needed INNER JOIN for this one...

[Ranjeet_Singh]

Great !!

Then do 2 thing.

1. Explain the procedure so that we will also come to know.

2. Mark this as solution so that it will be helpful for future search.

[SuperbrainBug · Accepted solution]

NP 😉

Here's the correct query (just copy-paste it):

SELECT 
T1.Date,
T1.Time,
T1.Location,
T1.Box,
T1.Balance
FROM TestTable T1
INNER JOIN (
SELECT
MAX(Time) AS Max_Time
FROM TestTable
WHERE Location=1 AND Box=1 AND Date BETWEEN #10/27/2014# AND #11/1/2014#
GROUP BY Date) T2
ON T1.Time=T2.Max_Time;

The catch is in SELECT within INNER JOIN. That SELECT chooses max time for every date, because we want that and then from whole table we choose filelds that we want, but now we only have fields with max time.

Here's realy nice explanation of INNER JOIN if someone is interested --> INNER JOINS

Peace! 😉