Complicated query (nested subqueries) for a trend graph (AC2007)

[AOB]

Hi guys,

I'm trying to perform a rather complicated query but struggling with it. Hoping somebody can fill in the gaps for me...

Some essential background first. I have a Balances table which records balances by date. I also have an Issues table where problems are logged. There is a one-to-many relationship between Balances and Issues (i.e. each Balance can have multiple Issues). I also have a Comments table where updates for each Issue are recorded. There is again a one-to-many relationship between Issues and Comments (i.e. each Issue can have multiple Comments)

There are two key date fields in the Issues table :

• FlagDate (the date an Issue was flagged by a user for investigation)
• ResolveDate (the date said investigation was brought to a conclusion)

There is also a date field in the Comments table :

• UpdatedWhen (the date any given comment was added)

So the basic flow is that an Issue gets flagged (FlagDate), then various comments are added (multiple UpdatedWhen's) and finally the Issue gets resolved (ResolveDate)

I need to incorporate a trend graph which will show the counts of

1. New (i.e. new issues flagged as of each day)
2. Cleared (i.e. issues resolved each day)
3. Updated (i.e. issues not yet resolved but updated each day)
4. Unchanged (i.e. issues not yet resolved and not updated each day)
5. Outstanding (i.e. all unresolved issues as of each day)

This is the SQL I've put together to get that table of information on which to base my chart :

SELECT [tblBalances].[BalanceDate] AS AsOfDate,
 
    (SELECT COUNT([tblIssues].[IssueID])
    FROM [tblIssues]
    WHERE [tblIssues].[Flag] = True
    AND [tblIssues].[FlagDate] = [tblBalances].[BalanceDate]) AS New,
 
    (SELECT COUNT([tblIssues].[IssueID])
    FROM [tblIssues]
    WHERE [tblIssues].[Flag] = True
    AND [tblIssues].[ResolveDate] = [tblBalances].[BalanceDate]) AS Cleared,
 
    (SELECT COUNT([tblIssues].[IssueID])
    FROM [tblIssues]
    INNER JOIN [tblComments] ON [tblIssues].[IssueID] = [tblComments].[IssueID]
    WHERE [tblIssues].[Flag] = True
    AND ([tblIssues].[FlagDate] < [tblBalances].[BalanceDate])
    AND (DateDiff("d",DateValue(Nz([tblComments].[UpdatedWhen],DateSerial(1900,1,1))), [tblBalances].[BalanceDate]) = 0)
    AND (Nz([tblIssues].[ResolveDate],DateSerial(9999,12,31)) > [tblBalances].[BalanceDate])) AS Updated,
 
    (SELECT COUNT([tblIssues].[IssueID])
    FROM [tblIssues]
    WHERE [tblIssues].[Flag] = True
    AND [tblIssues].[FlagDate] <= [tblBalances].[BalanceDate]
    AND Nz([tblIssues].[ResolveDate],DateSerial(9999,12,31)) > [tblBalances].[BalanceDate]) AS Outstanding,
 
    (Outstanding - New - Updated) AS Unchanged
 
FROM [tblBalances]
 
GROUP BY [tblBalances].[BalanceDate]
 
ORDER BY [tblBalances].[BalanceDate];

The subqueries for 'New', 'Cleared' and 'Outstanding' work perfectly; the resultant dataset gives me one record for each date in the Balance table and correctly counts the number of issues falling into each of those buckets.

The problem I have is with the 'Updated' bucket. If a flagged issue happens to be updated twice on the same day (which is perfectly acceptable), it counts this twice as well. I don't want this as I just want to know how many issues were updated on any given day - not how many updates there were.

I tried using COUNT(DISTINCT) in the 'Updated' subquery but it gives me a syntax error - on further research, I don't think it's possible to use the DISTINCT keyword in a COUNT subquery (at least not easily)

I also tried grouping by IssueID within that 'Updated' subquery but it still gives me the duplicate count within the same IssueID (and returns nulls rather than zeroes for those days where no updates occured)

I think I need to add a subquery within the subquery confused to only return the latest comment as of the date in question - something along the lines of :

(SELECT TOP 1 [tblComments].[UpdatedWhen]
FROM [tblComments]
WHERE [tblComments].[IssueID] = [tblIssues].[IssueID]
AND DateValue([tblComments].[UpdatedWhen]) <= [tblBalances].[BalanceDate]
ORDER BY [tblComments].[UpdatedWhen] DESC) AS UpdatedWhen

But haven't the faintest idea how to do this, nor if it is even feasible in Access to begin with.

Would really appreciate if anyone could assist me as have been struggling with this for days :banghead:

Thanks in advance!

AOB

 

[gblack]

Looks to me like you would be better off to use the Group By in each query (vs. grouping by everything at the end).

The issue would then be if your date field is a straight date or a DateTime... If your date field is Date only, then you can Group By the date field and it will only should give you counts for each day if it's a date time it will group by the time as well and give you one line per as you are describing.

Another possible way of dealing with this issue is to make each sub-query its own stand alone query adding a new field to each called QUERY_TYPE (e.g. "Cleared" as QUERY_TYPE, "Updated" as QUERY_TYPE etc)... once you have each sub-query running correctly, create one long UNION query then make a crosstab against the UNION and pivot on QUERY_UNION to get the values as it looks like you want to post them (above)...

Something like:

SELECT 
[tblBalances].[BalanceDate] AS AsOfDate, 
COUNT([tblIssues].[IssueID]), 
"New" as QUERY_TYPE
    FROM [tblBalances] INNER JOIN [tblIssues] ON
	[tblBalances].[BalanceDate] = [tblIssues].[FlagDate]
    WHERE [tblIssues].[Flag] = True
GROUP BY [tblBalances].[BalanceDate]

 UNION
SELECT 
[tblBalances].[BalanceDate] AS AsOfDate, 
COUNT([tblIssues].[IssueID]),
 "Cleared" as QUERY_TYPE
        FROM [tblBalances] INNER JOIN [tblIssues] ON
	[tblBalances].[BalanceDate] = [tblIssues].[ResolveDate]
    WHERE [tblIssues].[Flag] = True
GROUP BY [tblBalances].[BalanceDate]

UNION
SELECT...

 

[AOB]

Thanks gblack,

I tried both of your suggestions but it didn't get around the problem of only counting issues, with multiple comments on the same AsOfDate, once per day (and the queries were taking an age to run)

I've managed to add a third level to the 'Updated' subquery though, which seems to work - here is the new complete query, with the third level highlighted in red :

SELECT [tblBalances].[BalanceDate] AS AsOfDate,
 
    (SELECT COUNT([tblIssues].[IssueID])
     FROM [tblIssues]
     WHERE [tblIssues].[Flag] = True
     AND [tblIssues].[FlagDate] = [tblBalances].[BalanceDate]) AS New,
 
    (SELECT COUNT([tblIssues].[IssueID])
     FROM [tblIssues]
     WHERE [tblIssues].[Flag] = True
     AND [tblIssues].[ResolveDate] = [tblBalances].[BalanceDate]) AS Cleared,
 
    (SELECT COUNT([tblIssues].[IssueID])
     FROM [tblIssues]
     INNER JOIN
         [COLOR=red](SELECT [IssueID], DateValue([UpdatedWhen]) AS LastUpdatedWhen[/COLOR]
[COLOR=red]          FROM [tblComments][/COLOR]
[COLOR=red]          GROUP BY [IssueID], DateValue([UpdatedWhen])) AS tmp[/COLOR]
     ON  [tblIssues].[IssueID] =  [COLOR=red]tmp[/COLOR].[IssueID]
     WHERE [tblIssues].[Flag] = True
     AND ([tblIssues].[FlagDate] < [tblBalances].[BalanceDate])
     AND (DateDiff("d", [[COLOR=red]tmp[/COLOR]].[[COLOR=red]LastUpdatedWhen[/COLOR]],[tblBalances].[BalanceDate]) = 0)
     AND (Nz([tblIssues].[ResolveDate],DateSerial(9999,12,31)) > [tblBalances].[BalanceDate])) AS Updated,
 
    (SELECT COUNT([tblIssues].[IssueID])
     FROM [tblIssues]
     WHERE [tblIssues].[Flag] = True
     AND [tblIssues].[FlagDate] <= [tblBalances].[BalanceDate]
     AND Nz([tblIssues].[ResolveDate],DateSerial(9999,12,31)) > [tblBalances].[BalanceDate]) AS Outstanding,
 
    (Outstanding - New - Updated) AS Unchanged
 
FROM [tblBalances]
 
GROUP BY [tblBalances].[BalanceDate]
 
ORDER BY [tblBalances].[BalanceDate]

Only took a week...

But thanks for the response, I do appreciate it

AOB