I have a table with data which looks like this -
<<Hist_Date>> <Area> <Jobs>
11/10/16 15:53.... AA....... 12
11/10/16 15:53.... AB....... 15
11/10/16 15:53.... AC....... 20
12/10/16 11:22.... AA....... 10
12/10/16 11:22.... AB....... 12
12/10/16 11:22.... AC....... 14
12/10/16 14:32.... AA....... 20
12/10/16 14:32.... AB....... 16
12/10/16 14:32.... AC....... 10
13/10/16 10:32.... AA....... 10
13/10/16 10:32.... AB....... 18
13/10/16 10:32.... AC....... 14
I've created a crosstab query that sums the jobs by DATE (the row) and by AREAs combined (the column) which works fine except I need to always use the latest time for each day. i.e, above there are 6 records for the 12/10/16, but I only want to use the 14:32 timestamp ones (latest data) and exclude the records with the 11:22 timestamp...
So the crosstab results should be -
<<Date>> <AllAreas>
11/10/16........ 47
12/10/16........ 46 *
13/10/16........ 42
* Currently for the 12/10/16 I get 82 for the 12/10/16 because I haven't found a way to filter the latest timestamp for each day.
Any ideas? All help gratefully received!!
<<Hist_Date>> <Area> <Jobs>
11/10/16 15:53.... AA....... 12
11/10/16 15:53.... AB....... 15
11/10/16 15:53.... AC....... 20
12/10/16 11:22.... AA....... 10
12/10/16 11:22.... AB....... 12
12/10/16 11:22.... AC....... 14
12/10/16 14:32.... AA....... 20
12/10/16 14:32.... AB....... 16
12/10/16 14:32.... AC....... 10
13/10/16 10:32.... AA....... 10
13/10/16 10:32.... AB....... 18
13/10/16 10:32.... AC....... 14
I've created a crosstab query that sums the jobs by DATE (the row) and by AREAs combined (the column) which works fine except I need to always use the latest time for each day. i.e, above there are 6 records for the 12/10/16, but I only want to use the 14:32 timestamp ones (latest data) and exclude the records with the 11:22 timestamp...
So the crosstab results should be -
<<Date>> <AllAreas>
11/10/16........ 47
12/10/16........ 46 *
13/10/16........ 42
* Currently for the 12/10/16 I get 82 for the 12/10/16 because I haven't found a way to filter the latest timestamp for each day.
Any ideas? All help gratefully received!!
Last edited: