Returning Zero Values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Returning Zero Values

Author

Topic

bielen
Yak Posting Veteran

97 Posts

Posted - 2009-01-19 : 02:36:44

I have a simple script where I would like to return all tasks and have total_hours listed as zero if no hours are associated to the task.

SELECT task_name, SUM(hours) AS total_hours, MONTH(work_date) AS workMonth, YEAR(work_date) AS workYear
FROM PROJECT_HOURS
WHERE (project_number IN (9998, 9997, 9994, 9996, 9995, 9999))
GROUP BY task_name, MONTH(work_date), YEAR(work_date)
ORDER BY workYear, workMonth, task_name

I have tried isNull with the hours field, but it is ignored. Any suggestions on how to display all values even if the sum is zero?

bielen
Yak Posting Veteran

97 Posts

Posted - 2009-01-19 : 02:59:03

I've also tried this with no luck:

SELECT ph.task_name, COUNT(t.task_name) AS total, SUM(ph.hours) AS total_hours, MONTH(ph.work_date) AS workMonth, YEAR(ph.work_date)
AS workYear
FROM PROJECT_HOURS ph RIGHT OUTER JOIN
(SELECT DISTINCT task_name
FROM PROJECT_HOURS) t ON ph.task_name = t.task_name
WHERE (ph.project_number IN (9998, 9997, 9994, 9996, 9995, 9999))
GROUP BY ph.task_name, MONTH(ph.work_date), YEAR(ph.work_date)
ORDER BY workYear, workMonth, ph.task_name

Anybody?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-19 : 03:05:56

[code]SELECT t.task_name,
COUNT(*) AS total,
SUM(COALESCE(ph.hours, 0)) AS total_hours,
MONTH(ph.work_date) AS workMonth,
YEAR(ph.work_date) AS workYear
FROM (
SELECT task_name
FROM PROJECT_HOURS
GROUP BY task_name
) AS t
LEFT JOIN PROJECT_HOURS AS ph ON ph.task_name = t.task_name
AND ph.project_number IN (9998, 9997, 9994, 9996, 9995, 9999)
GROUP BY t.task_name,
MONTH(ph.work_date),
YEAR(ph.work_date)
ORDER BY YEAR(ph.work_date),
MONTH(ph.work_date),
t.task_name[/code]

E 12°55'05.63"
N 56°04'39.26"

bielen
Yak Posting Veteran

97 Posts

Posted - 2009-01-19 : 03:20:03

Peso:

Thanks for your reply. I tested the script, but it seems to display all tasks in the database with zero and not just the set of used tasks. In addition, my the records that do have tasks used display the results like my original scripts. I only want to display zero values if one of tasks has hours. Any other suggestions are welcome.

quote:

Originally posted by Peso

SELECT		t.task_name,
		COUNT(*) AS total,
		SUM(COALESCE(ph.hours, 0)) AS total_hours,
		MONTH(ph.work_date) AS workMonth,
		YEAR(ph.work_date) AS workYear
FROM		(
			SELECT		task_name
			FROM		PROJECT_HOURS
			GROUP BY	task_name
		) AS t
LEFT JOIN	PROJECT_HOURS AS ph ON ph.task_name = t.task_name
			AND ph.project_number IN (9998, 9997, 9994, 9996, 9995, 9999)
GROUP BY	t.task_name,
		MONTH(ph.work_date),
		YEAR(ph.work_date)
ORDER BY	YEAR(ph.work_date),
		MONTH(ph.work_date),
		t.task_name

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-19 : 03:26:43

do you meant tis?


SELECT		t.task_name,
		COUNT(*) AS total,
		SUM(COALESCE(ph.hours, 0)) AS total_hours,
		MONTH(ph.work_date) AS workMonth,
		YEAR(ph.work_date) AS workYear
FROM		(
			SELECT		task_name
			FROM		PROJECT_HOURS
			GROUP BY	task_name
                        HAVING COUNT(ph.hours)>0
		) AS t
LEFT JOIN	PROJECT_HOURS AS ph ON ph.task_name = t.task_name
			AND ph.project_number IN (9998, 9997, 9994, 9996, 9995, 9999)
GROUP BY	t.task_name,
		MONTH(ph.work_date),
		YEAR(ph.work_date)
ORDER BY	YEAR(ph.work_date),
		MONTH(ph.work_date),
		t.task_name

bielen
Yak Posting Veteran

97 Posts

Posted - 2009-01-19 : 03:39:29

I am not receiving the expected results.

For example, I would have the following tasks in the database in the field task_name:

task1
task2
task3
task4
task5
task6
task7
task8

When I run the report, I would see:

task_name hours work_month work_year
task1 50 1 2009
task2 100 1 2009
task3 0 1 2009
task7 0 1 2009
task8 25 1 2009
task1 50 1 2009
task2 50 2 2009
task3 100 2 2009
task7 150 2 2009
task8 0 2 2009
task1 550 3 2009
task2 100 3 2009
task3 250 3 2009

task4, task5 and task6 would never appear in the results because no hours were ever charged to these tasks during these months. The current script displays all results for all tasks/hours and does not display zeros for tasks that are used in some months.

Thanks for your help. Any info is appreciated.

quote:

Originally posted by visakh16

do you meant tis?


SELECT		t.task_name,
		COUNT(*) AS total,
		SUM(COALESCE(ph.hours, 0)) AS total_hours,
		MONTH(ph.work_date) AS workMonth,
		YEAR(ph.work_date) AS workYear
FROM		(
			SELECT		task_name
			FROM		PROJECT_HOURS
			GROUP BY	task_name
                        HAVING COUNT(ph.hours)>0
		) AS t
LEFT JOIN	PROJECT_HOURS AS ph ON ph.task_name = t.task_name
			AND ph.project_number IN (9998, 9997, 9994, 9996, 9995, 9999)
GROUP BY	t.task_name,
		MONTH(ph.work_date),
		YEAR(ph.work_date)
ORDER BY	YEAR(ph.work_date),
		MONTH(ph.work_date),
		t.task_name

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-19 : 03:47:29

[code]
SELECT t.task_name,
COUNT(*) AS total,
SUM(COALESCE(ph.hours, 0)) AS total_hours,
t.month AS workMonth,
t.year AS workYear
FROM (
SELECT task_name,
MONTH(work_date) as month,
YEAR(work_date) AS year
FROM PROJECT_HOURS
GROUP BY task_name,MONTH(work_date),
YEAR(work_date)
HAVING COUNT(ph.hours)>0
) AS t
LEFT JOIN PROJECT_HOURS AS ph ON ph.task_name = t.task_name
AND ph.project_number IN (9998, 9997, 9994, 9996, 9995, 9999)
AND t.month=MONTH(ph.work_date)
AND t.year=YEAR(ph.work_date)
GROUP BY t.task_name,
t.month,t.year
ORDER BY t.year,t.month,
t.task_name
[/code]

bielen
Yak Posting Veteran

97 Posts

Posted - 2009-01-19 : 03:58:32

Viskah:

I really appreciate your help, but I don't even know what to suggest at this point. The results are still showing ALL tasks in the table even if unused and not showing zero values when a task is used within one month, but not another month. If you have any follow-up ideas or questions, let me know, but I'm not sure what other info I could provide.

quote:

Originally posted by visakh16


SELECT		t.task_name,
		COUNT(*) AS total,
		SUM(COALESCE(ph.hours, 0)) AS total_hours,
		t.month AS workMonth,
		t.year AS workYear
FROM		(
			SELECT		task_name,
MONTH(work_date) as month,
YEAR(work_date) AS year
			FROM		PROJECT_HOURS
			GROUP BY	task_name,MONTH(work_date),
YEAR(work_date)
                        HAVING COUNT(ph.hours)>0
		) AS t
LEFT JOIN	PROJECT_HOURS AS ph ON ph.task_name = t.task_name
			AND ph.project_number IN (9998, 9997, 9994, 9996, 9995, 9999)
AND t.month=MONTH(ph.work_date)
AND t.year=YEAR(ph.work_date)
GROUP BY	t.task_name,
		t.month,t.year
ORDER BY	t.year,t.month,
		t.task_name

bielen
Yak Posting Veteran

97 Posts

Posted - 2009-01-19 : 05:27:38

Let me try to provide all the details in a single message that will hopefully clear things up. I'm stuck at this point and need any direction that someone can provide.

SELECT task_name, SUM(hours) AS total_hours, MONTH(work_date) AS workMonth, YEAR(work_date) AS workYear
FROM PROJECT_HOURS
WHERE (project_number IN (9998, 9997, 9994, 9996, 9995, 9999))
GROUP BY task_name, MONTH(work_date), YEAR(work_date)
ORDER BY workYear, workMonth, task_name

My current results when I run the query and sum up the records are as follows:

task_name hours workMonth workYear
Task1 22.75 1 2009
Task3 332.1 1 2009
Task4 8 1 2009
Task5 456.5 1 2009
Task6 148.5 1 2009
Task7 47.5 1 2009
Task8 220.25 1 2009
Task1 1.5 2 2009
Task2 11.25 2 2009
Task3 11.5 2 2009
Task4 11 2 2009
Task5 22 2 2009

I want my results to be as follows:

task_name hours workMonth workYear
Task1 22.75 1 2009
Task2 0 1 2009
Task3 332.1 1 2009
Task4 8 1 2009
Task5 456.5 1 2009
Task6 148.5 1 2009
Task7 47.5 1 2009
Task8 220.25 1 2009
Task1 1.5 2 2009
Task2 11.25 2 2009
Task3 11.5 2 2009
Task4 11 2 2009
Task5 22 2 2009
Task6 0 1 2009
Task7 0 1 2009
Task8 0 1 2009

Any records with a zero value should display. There are many other tasks which are used by other projects in the database which I do not want to display. For this example, I have tasks 1-8. I may have 50 other tasks that are not used in this example and should not display as either zero count records or NULL records. This is a log file. All data is in a single table. I am not joining any other tables at this time.

Hopefully this can help you provide me some direction.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-19 : 08:57:33

[code]
SELECT t.task_name,
COALESCE(f.total,0) AS total,
COALESCE(f.total_hours,0) AS total_hours,
t.month AS workMonth,
t.year AS workYear
FROM
(
SELECT task_name,Month,Year
FROM
(
SELECT task_name
FROM PROJECT_HOURS
WHERE project_number IN (9998, 9997, 9994, 9996, 9995, 9999)
GROUP BY task_name
HAVING COUNT(ph.hours)>0
) AS t
CROSS JOIN (SELECT DISTINCT MONTH(work_date) AS Month,
YEAR(work_date) AS year
FROM PROJECT_HOURS
WHERE project_number IN (9998, 9997, 9994, 9996, 9995, 9999)
)mnth
)m
LEFT JOIN
(SELECT task_name,
COUNT(*) AS total,
SUM(COALESCE(ph.hours, 0)) AS total_hours,
MONTH(work_date) AS workMonth,
YEAR(work_date) AS workYear
FROM PROJECT_HOURS
WHERE project_number IN (9998, 9997, 9994, 9996, 9995, 9999)
GROUP BY task_name,
MONTH(work_date),YEAR(work_date)
)f
ON f.task_name = m.task_name
AND m.month=f.workMonth
AND m.year=f.workYear
ORDER BY m.year,m.month,
m.task_name
[/code]

bielen
Yak Posting Veteran

97 Posts

Posted - 2009-01-19 : 09:55:40

Viskah:

Thank you for your help. It is greatly appreciated.

I'm trying to get the script to work. I receive a emssage that the column prefix 't' does not match with a table name or alias used in the query. I see 't' defined before the crossjoin, but still have to determine what is cauisn the error.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-19 : 09:58:27

quote:
Originally posted by bielen

Viskah:

Thank you for your help. It is greatly appreciated.

I'm trying to get the script to work. I receive a emssage that the column prefix 't' does not match with a table name or alias used in the query. I see 't' defined before the crossjoin, but still have to determine what is cauisn the error.

ah it should be m


SELECT		m.task_name,
		COALESCE(f.total,0) AS total,
		COALESCE(f.total_hours,0) AS total_hours,
		m.month AS workMonth,
		m.year AS workYear
FROM
(		
 SELECT task_name,Month,Year
 FROM
              (
			SELECT		task_name
			FROM		PROJECT_HOURS
                        WHERE project_number IN (9998, 9997, 9994, 9996, 9995, 9999)
			GROUP BY	task_name
                        HAVING COUNT(ph.hours)>0
		) AS t
 CROSS JOIN (SELECT DISTINCT MONTH(work_date) AS Month,
             YEAR(work_date) AS year 
             FROM PROJECT_HOURS
             WHERE project_number IN (9998, 9997, 9994, 9996, 9995, 9999)
             )mnth
)m
LEFT JOIN	
(SELECT        task_name,
		COUNT(*) AS total,
		SUM(COALESCE(ph.hours, 0)) AS total_hours,
		 MONTH(work_date) AS workMonth,
		 YEAR(work_date) AS workYear
FROM PROJECT_HOURS  
WHERE project_number IN (9998, 9997, 9994, 9996, 9995, 9999)
GROUP BY	task_name,
		MONTH(work_date),YEAR(work_date)
)f
 ON f.task_name = m.task_name
AND m.month=f.workMonth
AND m.year=f.workYear
ORDER BY	m.year,m.month,
		m.task_name