Select query records between dates

[Kay]

Hi

I have vreated 2 queries that help me count the amount of records from
3 tables assigned to each driver. The first query is as follows:

SELECT [Driver ID], COUNT(*) AS Current, 0 AS Bookings, 0 AS Contracts
FROM [Current Jobs]
GROUP BY [Driver ID]
UNION ALL
SELECT [Driver ID], 0 AS Current, COUNT(*) AS Bookings, 0 AS Contracts
FROM Bookings
GROUP BY [Driver ID]
UNION ALL SELECT [Driver ID], 0 AS Current, 0 AS Bookings, COUNT(*) AS
Contracts
FROM [Contract Jobs]
GROUP BY [Driver ID];

and second query:

SELECT [Q1].[Driver ID],
Sum([Q1].[Current]+[Q1].[Bookings]+[Q1].[Contracts]) AS Jobs
FROM Q1
GROUP BY [Q1].[Driver ID]
ORDER BY [Q1].[Driver ID];

The second query uses the first to get the following results:
Driver ID Jobs
P01 1
P04 3
P05 1
P06 2
P07 1
P08 2
P10 3

The problem Ive got is I need to select records between 2 dates. Iv
tried the sql statement 'between [start date] and [to date] but cannot
get it to work.

Can any genius help please

 

[Tom Ellison]

Dear Kay:

Sounds like a new wrinkle on something familiar, eh?

Now, is there a date column in each of the 3 table in the UNION? If so, add
it to each of the 3 SELECT queries, like this:

SELECT [Driver ID], MAX(MyDate) AS XDate, 0 AS YDate, 0 AS ZDate,
COUNT(*) AS Current, 0 AS Bookings, 0 AS Contracts
FROM [Current Jobs]
GROUP BY [Driver ID]
UNION ALL
SELECT [Driver ID], 0 AS XDate, MAX(MyDate) AS YDate,, 0 AS ZDate,
0 AS Current, COUNT(*) AS Bookings, 0 AS Contracts
FROM Bookings
GROUP BY [Driver ID]
UNION ALL
SELECT [Driver ID], 0 AS XDate, 0 AS YDate, MAX(MyDate) AS ZDate,
0 AS Current, 0 AS Bookings, COUNT(*) AS Contracts
FROM [Contract Jobs]
GROUP BY [Driver ID];

Now, there may be more than one row in each of the 3 tables for each [Driver
ID], and I have chosen to show the most recent date for each. If there are
several such rows, which date do you want?

Next, add this XDate to the final query:

SELECT [Q1].[Driver ID],
MAX(XDate) AS x_Date, MAX(YDate) AS y_Date, MAX(ZDate) AS z_Date,
Sum([Q1].[Current]+[Q1].[Bookings]+[Q1].[Contracts]) AS Jobs
FROM Q1
GROUP BY [Q1].[Driver ID]
ORDER BY [Q1].[Driver ID];

Does this help? Is it at least a step in the right direction? If not, give
some specifics, please.

Tom Ellison

 

[Kay]

Youve got it Tom, It is familiar to you cause Ive been trying to get it
to work all day.
Just one lastthing if you dont mind. How would I use the between
statement because it doesnt recognise the date field when I use the
following
[MyDate] Between [Start Date] AND [End Date]

 

[Tom Ellison]

Dear Kay:

I was gently teasing about this being familiar. I recognized it quite well!

The column I called MyDate was just an example of a column name. I didn't
know where, or even whether you have such a date. Do you? What is the name
of the column where you have the date? Substitute that column name for my
"guess" of MyDate.

Remember, I cannot see any of your details. I only know what you tell me.

Tom Ellison

 

[Kay]

Hi Tom, You have been very help and it is much appreciated

I have used a field called date and I did substitute it for my date.
The result was that 3 date columns were displayed.
What Im trying to do is use the statement 'date between [from date] and
[to date]' so that the user enters 2 dates and the results picked fall
between
the 2 dates

Any help is like a goldmine for me at this time as I cannot go to bed
until I complete this, otherwise I wont be able to sleep

 

[Tom Ellison]

Dear Kay:

I cannot guarantee I will be staying up late with you. However, let's give
it a try.

Now, my work to this point has put the dates into three columns, and have
used the maximum date for each [Driver ID]. If you wish, you could filter
based on these 3 dates, but I'm not so sure that would be what you want.
Perhaps you want to filter on each of the 3 dates independently. I'm going
to make that assumption for now.

In that case, the filtering needs to be done at the earlier stage, in the
UNION query. Here's what I suggest:

SELECT [Driver ID], MAX(MyDate) AS XDate, 0 AS YDate, 0 AS ZDate,
COUNT(*) AS Current, 0 AS Bookings, 0 AS Contracts
FROM [Current Jobs]
WHERE MyDate BETWEEN [Begin Date: ] AND [End Date: ]
GROUP BY [Driver ID]
UNION ALL
SELECT [Driver ID], 0 AS XDate, MAX(MyDate) AS YDate,, 0 AS ZDate,
0 AS Current, COUNT(*) AS Bookings, 0 AS Contracts
FROM Bookings
WHERE MyDate BETWEEN [Begin Date: ] AND [End Date: ]
GROUP BY [Driver ID]
UNION ALL
SELECT [Driver ID], 0 AS XDate, 0 AS YDate, MAX(MyDate) AS ZDate,
0 AS Current, 0 AS Bookings, COUNT(*) AS Contracts
FROM [Contract Jobs]
WHERE MyDate BETWEEN [Begin Date: ] AND [End Date: ]
GROUP BY [Driver ID];

This has some implications. If a set of rows for the same [Driver ID] has
now rows where the date is between the chosen dates, then that [Driver ID]
would have no Current, or no Bookings, or no Contract Jobs entries in the
UNION query, and therefore not in the final query. If it is missing all
three, then it would disappear entirely. This may or may not be desirable.
Please look for this in the results, and see what it is doing. There are
several alternatives here we could consider if you do not like what you see.

It also changes the maximum of the date to be the maximum of only those
dates within the range of dates, as well as the sum to be that of only those
rows between those dates. Hopefully, this is just what you wanted. Right?

The final query would be unchanged.

Please let me know if this helped, and if you are likely to be able to sleep
soon! It's one of those "long Winter's nights" even though it's not
technkcally still Winter. I know, because I've been out scooping snow again
and again the last 2 days, and tomorrow will probably not be better. Now,
how about YOU help ME with that!


Tom Ellison

Hi Tom, You have been very help and it is much appreciated

I have used a field called date and I did substitute it for my date.
The result was that 3 date columns were displayed.
What Im trying to do is use the statement 'date between [from date] and
[to date]' so that the user enters 2 dates and the results picked fall
between
the 2 dates

Any help is like a goldmine for me at this time as I cannot go to bed
until I complete this, otherwise I wont be able to sleep

 

[Kay]

Hi Tom

Youre first assupmtion was correct, I was trying to filter based on the
3 dates, not on the 3 dates independantly. Still its very helpful and
Im guessing to filter it based on the three dates, I need the 'mydaye
between [start] and [end]' statement at the end of the union query but
im not sure how to put the date at the start of the statement.

Only if you were my neigbour, You could have had me sweeping the snow
off your driveway

 

[Tom Ellison]

Dear Kay:

Did you bother to try what I provided? I am expecting it to do just what I
understand you want.

Tom Ellison

 

[Kay]

Hi Tom
Sorry to be annoying but all thanks to you Tom it is now working.
Wish I could send you a box of roses. Thankyou very very much Tom

 

[Tom Ellison]

Dear Kay:

I'd really prefer chocolates. <grin>

So, if you don't mind, was the solution at all related to what I provided?

Tom Ellison

 

[Kay]

Dear Tom

Oh id does sound like flowers but I meant Roses as in a box of Roses
Yes the solution was what I was looking for, I wanted to be able to
type one from and to date and the result to be a count of records from
3 tables that fall between those dates and the drivers who were
assigned to those records. Thats exactly what I got.

Thankyou ever so much I would have been on this mabey forever without
you're help

Thanks Again

Kay