Counting a datediff result 1

[Bubbalouie]

I am attempting to perform some math on the results of a datediff operation for a helpdesk report.

In simplified terms I want my result set to look similar to this:

Dept	Category	0_Days	1-2_Days	3-4_Days	Over_4_Days
ADMIN	Hardware	2	3		1		1
ADMIN	Software	3	1		1		1
ADMIN	Internet	1	0		0		0
ADMIN	Email		2	1		0		1
FINANCE	Hardware 	1	0		3		0
FINANCE	Software	2	2		1		0
FINANCE Intranet	1	2		0		1
SALES	Email		1	0		1		0
SALES	Telephone	2	0		0		0

select p.department, p.category, datediff(d, p.start_date, p.close_date) as Days_Open from dbo.problems p
order by p.department, p.category

gets me the data I'm after, but I want to count those tickets by the Dept, Category and how many days open the ticket was. I just can't get them together. I think I need to count the datediff results using a case statement somehow. I've tried every which way to do so but the only result I get is 'syntax error...............).

Anyway, if it's not possible to do this I'll inform the boss but if it is possible and someone can show me the light I'd be most grateful!

 

[dhookom]

I wouldn't create a "case statement". Consider create a small table of ranges tblDaysRange with three fields: [MinDays]. [MaxDays], and [RangeName]. Add records for your min and max days with the RangeName being the values you want to display as column headings.

Then create a query with your above select query and add tblDaysRange. Don't join tblDaysRange but add:
Between [MinDays] and [MaxDays]
as the criteria under the Days_Open column. Then change your query to a Crosstab with Department and Category as the Row Headings, RangeName as the Column Heading, and count of department as the Value.


Duane
Hook'D on Access
MS Access MVP

 

[Bubbalouie]

I have to display the result set on a web page so I think I pretty much have to have a sql statement.

 

[dhookom]

I was assuming "for a helpdesk report" meant all Access.

When I have done this for web pages, I create a the table as suggested and build a query that includes every possible value of Department and Category combined with every RangeName. I then use vbscript on the webpage side to create the table.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

What about this ?

SELECT p.department, p.category
, SUM(IIf(DateDiff('d', p.start_date, p.close_date)=0,1,0) AS [0_Days]
, SUM(IIf(DateDiff('d', p.start_date, p.close_date) BETWEEN 1 And 2,1,0) AS [1-2_Days]
, SUM(IIf(DateDiff('d', p.start_date, p.close_date) BETWEEN 3 And 4,1,0) AS [3-4_Days]
, SUM(IIf(DateDiff('d', p.start_date, p.close_date)>4,1,0) AS [Over_4_Days]
FROM dbo.problems p
GROUP BY p.department, p.category

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Bubbalouie]

Although I think I posted this in the wrong forum (turns out it's MS SQL) I think you got me headed in the right direction!

Select d.dname,
Sum(Case When DateDiff(d, p.start_date, p.close_date) = 0 Then 1 Else 0 End) Same_Day, 
Sum(Case When DateDiff(d, p.start_date, p.close_date) = 1 Then 1 Else 0 End) Day_1,
Sum(Case When DateDiff(d, p.start_date, p.close_date) = 2 Then 1 Else 0 End) Day_2,
Sum(Case When DateDiff(d, p.start_date, p.close_date) = 3 Then 1 Else 0 End) Day_3,
Sum(Case When DateDiff(d, p.start_date, p.close_date) = 4 Then 1 Else 0 End) Day_4,
Sum(Case When DateDiff(d, p.start_date, p.close_date) = 5 Then 1 Else 0 End) Day_5,
Sum(Case When DateDiff(d, p.start_date, p.close_date) = 6 Then 1 Else 0 End) Day_6,
Sum(Case When DateDiff(d, p.start_date, p.close_date) = 7 Then 1 Else 0 End) Day_7,
Sum(Case When DateDiff(d, p.start_date, p.close_date) > 7 Then 1 Else 0 End) Over_7
FROM 
       	dbo.problems p
	inner join dbo.departments d
	on p.department = d.department_id
	inner join dbo.categories c
	on p.category=c.category_id
	inner join dbo.priority pp
	on p.priority=pp.priority_id
	inner join dbo.status s
	on p.status=s.status_ID 
group by d.dname

This seems to be giving me what I was after.

Thanks for getting me to my destination!

 

[dhookom]

Yes, that solution will work until you change the date range "buckets". Then you will need to modify the SQL.

Duane
Hook'D on Access
MS Access MVP