Count dates from two different Tables

[darkhat01]

I would like to count only dates where the Close Date is less than or equal to the Target Date. Here is what I have done, but it does not work. any ideas how I might get this to work?

SELECT A.[SCA_Open Issue Tbl.ID] AS [IDCount],
A.[Criticality of Risk] AS [Criticality of Risk],
A.[Database] As [Database],
A.[Risk_assumed_without_mitigation] As [Risk Assumed WO Mitigation],
A.[OTS] As [OTS],
A.[Issue Closed Date] As [Close Date],
A.[Targeted Completion Date] As [Target Date]
FROM [EOY Closed SC&A Issues] A

UNION ALL SELECT B.[RefID] AS IDCount,
B.[complexity_desc] AS [Criticality of Risk],
B.[Database] As [Database],
0 As [Risk Assumed WO Mitigation],
B.[OTS] As [OTS],
B.[EndDate] As [Close Date],
B.[TargetEndDate] As [Target Date]
FROM [EOY Closed Risk Assessments] B

WHERE [Close Date] BETWEEN Date() <= [Target Date];

 

[Dennisk]

your where condition is incorrect

WHERE [Close Date] BETWEEN Date() AND [Target Date];

 

[darkhat01]

This does not work. It does not only show the records where the Close Date is less then or equal to The Target Date. Would you happen to have any other ideas?

 

[neileg]

I'm not sure that you can use the alias [Target Date] in the WHERE condition. Try building the union query without the WHERE clause. Then create a second query that uses the union query and add the criteria there.

 

[darkhat01]

Ok I tried that and it did not work.... Here is the SQL for that...
"EOY Closed Report SC&A and Risk Assements" is the Unioin Query that I created.

When I run this it looks the same as when I run it in the Union Query....

SELECT [EOY Closed Report SC&A and Risk Assements].IDCount, [EOY Closed Report SC&A and Risk Assements].[Close Date], [EOY Closed Report SC&A and Risk Assements].[Target Date]
FROM [EOY Closed Report SC&A and Risk Assements]
WHERE ((([EOY Closed Report SC&A and Risk Assements].[Close Date]) Between Date() And [Target Date]));

 

[Guus2005]

It gets a bit messy with all the aliases since only the aliases from the first query are shown. Perhaps you could try this:

SELECT C.[SCA_Open Issue Tbl.ID] AS [IDCount],
C.[Criticality of Risk] AS [Criticality of Risk],
C.[Database] As [Database],
C.[Risk_assumed_without_mitigation] As [Risk Assumed WO Mitigation],
C.[OTS] As [OTS],
C.[Issue Closed Date] As [Close Date],
C.[Targeted Completion Date] As [Target Date]
FROM (
SELECT A.[SCA_Open Issue Tbl.ID],
A.[Criticality of Risk],
A.[Database],
A.[Risk_assumed_without_mitigation],
A.[OTS],
A.[Issue Closed Date],
A.[Targeted Completion Date]
FROM [EOY Closed SC&A Issues] A

UNION ALL 

SELECT B.[RefID],
B.[complexity_desc],
B.[Database],
0 ,
B.[OTS],
B.[EndDate],
B.[TargetEndDate]
FROM [EOY Closed Risk Assessments] B
WHERE B.[EndDate] BETWEEN Date() AND B.[TargetEndDate]) C

Depending on where you want the where clause you need to wiggle it around a bit.

HTH