Adding numbers that are not there.
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 :)