how to not return record where 0 count... 1

[Neil Toulouse]

Hi!

This I am sure is simple but is eluding me!!!

I have the following select statement which works fine and returns the correct data:

SELECT 
	so.[strategic_objective_id],
	so.[name]'strategic_objective',
	so.[description] 'objective description',
	pso.[project_id],
	p.[name] 'project_name',
	session_count =( SELECT ISNULL(SUM(ISNULL(session_count,1)),0) FROM Sessions WHERE project_id = pso.project_id 		and start >= @start_date and start < @end_date + 1 ),
	ISNULL([dbo].[udf_ECM_session_attendance_dates](pso.[project_id],@start_date,@end_date),0) AS 'total_attendance'
FROM dbo.strategic_objectives so
LEFT JOIN  dbo.projects_strategic_objectives pso  ON pso.[strategic_objective_id] = so.[strategic_objective_id]
LEFT JOIN [projects] p ON pso.[project_id] = p.[project_id]
	
WHERE so.active = 1
ORDER BY so.[strategic_objective_id],pso.[project_id]

What I want to do is only bring back those records where 'session_count > 0'.

As you can see, session_count is derived from another select statement.

I cant use 'WHERE session_count > 0' as the field doesn't exist at that point.

Is this possible?

TIA
Neil

I like work. It fascinates me. I can sit and look at it for hours...

 

[djj55]

Not the best way but you can put the select statement in the where clause.


djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[Neil Toulouse]

Hi!

I tried that, but it added massively to the overhead of getting the data back, so I dismissed it, thinking there must be a better way!

Neil

I like work. It fascinates me. I can sit and look at it for hours...

 

[IanWaterman]

Would this work faster

SELECT
so.[strategic_objective_id],
so.[name]'strategic_objective',
so.[description] 'objective description',
pso.[project_id],
p.[name] 'project_name',
s.session_count,
ISNULL([dbo].[udf_ECM_session_attendance_dates](pso.[project_id],@start_date,@end_date),0) AS 'total_attendance'
FROM dbo.strategic_objectives so
LEFT JOIN dbo.projects_strategic_objectives pso ON pso.[strategic_objective_id] = so.[strategic_objective_id]
LEFT JOIN [projects] p ON pso.[project_id] = p.[project_id]
inner join
session_count =(SELECT ISNULL(SUM(ISNULL(session_count,1)),0) as session_count, project_id FROM Sessions WHERE start >= @start_date and start < @end_date + 1 ) s
on s.project_id = pso.project_id
WHERE so.active = 1
ORDER BY so.[strategic_objective_id],pso.[project_id]

Ian

 

[IanWaterman]

Sorry that should be

SELECT
so.[strategic_objective_id],
so.[name]'strategic_objective',
so.[description] 'objective description',
pso.[project_id],
p.[name] 'project_name',
s.session_count,
ISNULL([dbo].[udf_ECM_session_attendance_dates](pso.[project_id],@start_date,@end_date),0) AS 'total_attendance'
FROM dbo.strategic_objectives so
LEFT JOIN dbo.projects_strategic_objectives pso ON pso.[strategic_objective_id] = so.[strategic_objective_id]
LEFT JOIN [projects] p ON pso.[project_id] = p.[project_id]
inner join
(SELECT ISNULL(SUM(ISNULL(session_count,1)),0) as session_count, project_id FROM Sessions WHERE start >= @start_date and start < @end_date + 1 ) s
on s.project_id = pso.project_id
WHERE so.active = 1
and s.session_count>0
ORDER BY so.[strategic_objective_id],pso.[project_id]

 

[Neil Toulouse]

Hi Ian!

It balks at:

inner join session_count =(SELECT ISNULL(SUM(ISNULL(session_count,1)),0) as session_count, project_id FROM Sessions

I like work. It fascinates me. I can sit and look at it for hours...

 

[IanWaterman]

It would, I corrected that in subsequent post.

Ian

 

[Neil Toulouse]

Hi Ian!

Sorry I was too quick off the mark

I had to add 'GROUP BY sessions.project_id' in the 'sessions_count' SELECT statement to stop an error being thrown, but I now get the results I want (ie 0 session_count removed) in a quicker time

Many thanks!

I like work. It fascinates me. I can sit and look at it for hours...