Multiple Aggregation??

[x911gt2]

Hi there...I am having an issue with using aggregation...

Basically, I am working on a website for the helpdesk...on the main page of the site, it lists all the technicians and the number of tickets each has open. We want to expand this to break the tickets down into categories: current (less than 15 days old), old (15-30 days) and older (> 30 days). I need to make this happen all in one statement, so I can just loop through the recordset and output the data.

I am counting the tickets by using the DateDiff function and grouping by the name of the technician. I have tried to use joins, but I can't get it to work completely because access doesn't support full outer joins...

Any ideas???

Thanks in advance.

 

[traingamer]

You should be able to use your DateDiff as an additional grouping selection. If that's not enough to get you started, post your sql and someone will be able to help.


Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[x911gt2]

Ok, well I just realized I misworded something...I'm not counting the tickets using DateDiff...I'm just using it to determine which tickets fit into what category...the sql I was attempting to use is a bit confusing...but here it is...

SELECT Assign.Assign_nm, Results.Current, Results.Old, Results.Older FROM  Assign 
LEFT JOIN  
(SELECT Temp.Assign, Temp.Current, Temp2.Old, Temp3.Older   FROM  ((SELECT S.Assign, Count (S.Reqst_num) AS Current FROM Service_reqst S WHERE S.status <> 'Completed' AND DateDiff('d',S.Time_stamp,Now()) < 15 GROUP BY S.Assign) AS Temp  
LEFT JOIN     
((SELECT S.Assign, Count (S.Reqst_num) AS Old FROM Service_reqst S WHERE S.status <> 'Completed' AND (DateDiff('d',S.Time_stamp,Now()) >= 15 AND DateDiff('d',S.Time_stamp,Now()) <= 30) GROUP BY S.Assign) AS Temp2    
LEFT JOIN  
(SELECT S.Assign, Count (S.Reqst_num) AS Older FROM Service_reqst S WHERE S.status <> 'Completed' AND DateDiff('d',S.Time_stamp,Now()) > 30 GROUP BY S.Assign) AS Temp3    
ON Temp2.Assign = Temp3.Assign)     
ON Temp.Assign = Temp2.Assign)) As Results  
ON Assign.Assign_nm = Results.Assign
ORDER BY Assign.Assign_nm ASC

Ok, basically what I was trying to do is this...since you can't have multiple aggregate functions running on the same query, I attempted to do a subquery for each ticket category (current, old, older)...each subquery would then return the number of tickets and the technician...then i would join all three queries based on who the technician was, essentially getting a result that would include the name of the tech and the number of current, old and older tickets for each...However, if the tech does not have any tickets in that particular category of tickets, i still want to keep his name in the list so it would just display 0 under that category...my approach may be flawed...but it's the only thing i could think of...the current sql works to an extent, but sometimes omits tickets...this is because of the left join...i thought maybe a full outer join would cure this, but access doesnt support it...ANY Help would be GREATLY appreciated...thanks!

 

[traingamer]

Just change your joins into an iif like (not tested)

(SELECT S.Assign, [COLOR=red]iif(DateDiff('d',S.Time_stamp,Now())>30, "Older",iif(DateDiff('d',S.Time_stamp,Now())<15, "Newer", "Old"))  ,[/color]Count (S.Reqst_num) AS Current 
FROM Service_reqst S WHERE S.status <> 'Completed' 
GROUP BY S.Assign, [COLOR=blue]2[/color])

Where 2 is the second field in the Select

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill