Query on Dates

M

MTB20598

I have a 2 table with a common fiels (Filenumber). One table has start and
stop dates associated with it. The other table has a different start date.
What I want to do is find the items in table 2 that are associated with items
in Table 1 that are in the Start and Stop date range.

In table 2 there are usually more than 1 record that could meet this
criteria for the record in table 1.

Any suggestions would be appreciated.

Thanks
 
J

John W. Vinson

I have a 2 table with a common fiels (Filenumber). One table has start and
stop dates associated with it. The other table has a different start date.
What I want to do is find the items in table 2 that are associated with items
in Table 1 that are in the Start and Stop date range.

In table 2 there are usually more than 1 record that could meet this
criteria for the record in table 1.

Any suggestions would be appreciated.

Thanks
Click to expand...

A "Non Equi Join" will work here. Start by joining the start date from table1
to the startdate for table2; then open the query in SQL view and edit the line

ON table2.[start date] = table1.[start date] to

ON table2.[start date] >= table1.[start date] AND table2.[start date] <=
table1.[stop date]


John W. Vinson [MVP]
 
M

MTB20598

Thanks for the information. However I get syntax error when I do that. I am
enclosing the whole SQL stream for review since maybe something else is
getting in the way??

SELECT [Siebal 2].ID, [Siebal 2].[Policy Number-Full], [Siebal 2].[Pol Maint
Received Dt], [Siebal 2].[Pol Maint Complete Dt], XTAQ.CASE_ID_SINGLE,
XTAQ.TASK_START_DATE, XTAQ.TASK_TIME, [Siebal 2].[Cal Days Proc-on Pol Maint
Rec], [Siebal 2].[Bus Days Proc-on Pol Maint Rec], XTAQ.[NUMBER OF TOUCHES-1]
FROM [Siebal 2] INNER JOIN XTAQ ON [Siebal 2].[Policy Number-Full] =
XTAQ.CASE_ID_SINGLE
GROUP BY [Siebal 2].ID, [Siebal 2].[Policy Number-Full], [Siebal 2].[Pol
Maint Received Dt], [Siebal 2].[Pol Maint Complete Dt], XTAQ.CASE_ID_SINGLE,
XTAQ.TASK_START_DATE, XTAQ.TASK_TIME, [Siebal 2].[Cal Days Proc-on Pol Maint
Rec], [Siebal 2].[Bus Days Proc-on Pol Maint Rec], XTAQ.[NUMBER OF TOUCHES-1]
HAVING (((XTAQ.[NUMBER OF TOUCHES-1])<>"ALREADY DONE - Req Already Processed
[70]"));



John W. Vinson said:
I have a 2 table with a common fiels (Filenumber). One table has start and
stop dates associated with it. The other table has a different start date.
What I want to do is find the items in table 2 that are associated with items
in Table 1 that are in the Start and Stop date range.

In table 2 there are usually more than 1 record that could meet this
criteria for the record in table 1.

Any suggestions would be appreciated.

Thanks
Click to expand...

A "Non Equi Join" will work here. Start by joining the start date from table1
to the startdate for table2; then open the query in SQL view and edit the line

ON table2.[start date] = table1.[start date] to

ON table2.[start date] >= table1.[start date] AND table2.[start date] <=
table1.[stop date]


John W. Vinson [MVP]
Click to expand...
 
J

John W. Vinson

Thanks for the information. However I get syntax error when I do that. I am
enclosing the whole SQL stream for review since maybe something else is
getting in the way??

SELECT [Siebal 2].ID, [Siebal 2].[Policy Number-Full], [Siebal 2].[Pol Maint
Received Dt], [Siebal 2].[Pol Maint Complete Dt], XTAQ.CASE_ID_SINGLE,
XTAQ.TASK_START_DATE, XTAQ.TASK_TIME, [Siebal 2].[Cal Days Proc-on Pol Maint
Rec], [Siebal 2].[Bus Days Proc-on Pol Maint Rec], XTAQ.[NUMBER OF TOUCHES-1]
FROM [Siebal 2] INNER JOIN XTAQ ON [Siebal 2].[Policy Number-Full] =
XTAQ.CASE_ID_SINGLE
GROUP BY [Siebal 2].ID, [Siebal 2].[Policy Number-Full], [Siebal 2].[Pol
Maint Received Dt], [Siebal 2].[Pol Maint Complete Dt], XTAQ.CASE_ID_SINGLE,
XTAQ.TASK_START_DATE, XTAQ.TASK_TIME, [Siebal 2].[Cal Days Proc-on Pol Maint
Rec], [Siebal 2].[Bus Days Proc-on Pol Maint Rec], XTAQ.[NUMBER OF TOUCHES-1]
HAVING (((XTAQ.[NUMBER OF TOUCHES-1])<>"ALREADY DONE - Req Already Processed
[70]"));
Click to expand...

This bears absolutely no similarity to my suggestion, as far as I can see.
You're not using a non equi join and you're not joining on any date field, nor
(as best as I can tell) do you even reference any date fields in your query
criteria.

There's the XTAQ.TASK_START_DATE field and the [Siebal 2].[Pol Maint Complete
Dt] and [Pol Maint Received Dt] fields. Is there a TASK_END_DATE as well? Do
you want records where [Pol Maint Complete Dt] (or Received Dt?) is between
TASK_START_DATE and TASK_END_DATE, as well as matching on Policy Number-Full?
If so, try this (cleaning up some other issues such as moving the criterion
from HAVING to WHERE):

SELECT [Siebal 2].ID, [Siebal 2].[Policy Number-Full],
[Siebal 2].[Pol Maint Received Dt], [Siebal 2].[Pol Maint Complete Dt],
XTAQ.CASE_ID_SINGLE, XTAQ.TASK_START_DATE, XTAQ.TASK_TIME,
[Siebal 2].[Cal Days Proc-on Pol Maint Rec],
[Siebal 2].[Bus Days Proc-on Pol Maint Rec], XTAQ.[NUMBER OF TOUCHES-1]
FROM [Siebal 2] INNER JOIN XTAQ
ON [Siebal 2].[Policy Number-Full] = XTAQ.CASE_ID_SINGLE
AND [Siebal 2].[Pol Maint Received Dt] >= XTAQ.TASK_START_DATE
AND [Siebal 2].[Pol Maint Received Dt] <= XTAQ.TASK_STOP_DATE
GROUP BY [Siebal 2].ID, [Siebal 2].[Policy Number-Full], [Siebal 2].[Pol
Maint Received Dt], [Siebal 2].[Pol Maint Complete Dt], XTAQ.CASE_ID_SINGLE,
XTAQ.TASK_START_DATE, XTAQ.TASK_TIME, [Siebal 2].[Cal Days Proc-on Pol Maint
Rec], [Siebal 2].[Bus Days Proc-on Pol Maint Rec], XTAQ.[NUMBER OF TOUCHES-1]
WHERE (((XTAQ.[NUMBER OF TOUCHES-1])<>"ALREADY DONE - Req Already Processed
[70]"));

It's not clear why you have the TOTALS query (the Group By clause) implemented
since you're not summing, counting or calculating anything; this query as
written would get the same results more simply:

SELECT DISTINCT [Siebal 2].ID, [Siebal 2].[Policy Number-Full],
[Siebal 2].[Pol Maint Received Dt], [Siebal 2].[Pol Maint Complete Dt],
XTAQ.CASE_ID_SINGLE, XTAQ.TASK_START_DATE, XTAQ.TASK_TIME,
[Siebal 2].[Cal Days Proc-on Pol Maint Rec],
[Siebal 2].[Bus Days Proc-on Pol Maint Rec], XTAQ.[NUMBER OF TOUCHES-1]
FROM [Siebal 2] INNER JOIN XTAQ
ON [Siebal 2].[Policy Number-Full] = XTAQ.CASE_ID_SINGLE
AND [Siebal 2].[Pol Maint Received Dt] >= XTAQ.TASK_START_DATE
AND [Siebal 2].[Pol Maint Received Dt] <= XTAQ.TASK_STOP_DATE
WHERE (((XTAQ.[NUMBER OF TOUCHES-1])<>"ALREADY DONE - Req Already Processed
[70]"));


John W. Vinson [MVP]
 
Top