Question Locked

Adding numbers that are not there.

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a table that contains the following fields "Employee", "Log_ID" , "Log_Age_Days"
I am doing a count on the Log_ID for each employee against Log_Age.
So lets say employee one had 20 logs that were close in 0 days and 10 logs closed in 3 days.
I have no problem displaying this in a table on an ASP page or Graph.
The problem is I also want to display that there were no logs close in 1 day and also 2 days. so the tahle may look like this.
0 days 1 days 2 days 3 days over 7 days
20 0 0 10 50

Now of course the database does not contain any information for 1 day or 2 days, my question is how to I display the days wilth a zero valu if they are not in the database?
I want to do this for a seven day period and anything over 7 days is to be added in one large value.

I hope that I haved explained what I am looking for Properly.

Below is the Code I havwe so far.
SELECT TOP 100 PERCENT { fn MONTHNAME(dbo.SOLUTIONIMPORTED$.create_date) } AS Monthname, COUNT(dbo.SOLUTIONIMPORTED$.log_id) AS [count],
dbo.SOLUTIONIMPORTED$.log_age_days AS age, dbo.SOLUTIONIMPORTED$.own_emp_no, dbo.SOLUTIONIMPORTED$.resp_org,
dbo.Employee_Information.Employee_Name, { fn YEAR(dbo.SOLUTIONIMPORTED$.create_date) } AS [year]
FROM dbo.SOLUTIONIMPORTED$ INNER JOIN
dbo.Employee_Information ON dbo.SOLUTIONIMPORTED$.own_emp_no = dbo.Employee_Information.Employee_Number
GROUP BY { fn MONTHNAME(dbo.SOLUTIONIMPORTED$.create_date) }, dbo.SOLUTIONIMPORTED$.log_age_days, dbo.SOLUTIONIMPORTED$.own_emp_no,
dbo.SOLUTIONIMPORTED$.resp_org, dbo.Employee_Information.Employee_Name, { fn YEAR(dbo.SOLUTIONIMPORTED$.create_date) }
HAVING ({ fn YEAR(dbo.SOLUTIONIMPORTED$.create_date) } >= 2007) AND ({ fn MONTHNAME(dbo.SOLUTIONIMPORTED$.create_date) } = '11')
ORDER BY { fn MONTHNAME(dbo.SOLUTIONIMPORTED$.create_date) } DESC, dbo.SOLUTIONIMPORTED$.log_age_days

Thanking You in advance for your help :)

3 replies

by tintoman

Cos it seems to me that setting the default value of the fields in your table to 0 will do what you want

by rheal.dugas

The Information I pull into my database comes from a Mainframe report.
Basicly its a report that displays how long it took to close a Customer Log.
Some problems (logs) are closed the same day resulting in a 0 (zero) being entered into the File "Log_Age_Days" Some of the logs are closed after 1 day and then some may be closed after 120 days.
My problem is that the employee that I am displaying the results for may wil have logs that they closed in 0 days may have some closed in 5 days, while another employee may have some closed in 3 days or 9 days.
I want to be able to create a Graph that will look the same for all employees. This graph whould show from 1 to 7 days and a total for anything over 8 days.
The way the data is now, my graph will be all over the place and not display 1 to 7 days
I hope I have explained this out properly

This discussion is closed to new replies.