Excluding part of select statement if no data is returned in results

I have a query that returns results based on information in several tables. The problem I am having is that is there are no records in the one table it doesn't return any information at all. This table may not have any information initially for the employees so I need to show results whether or not there is anything in this one table.

Here is my select statement:

SELECT employee.emp_id,DATEDIFF(mm, employee.emp_begin_accrual,GETDATE()) * employee.emp_accrual_rate - (SELECTSUM(request_duration)AS daystakenFROM request)AS daysleft, employee.emp_lname +', ' + employee.emp_fname +' ' + employee.emp_minitial +'.'AS emp_name, department.department_name, location.location_nameFROM employeeINNERJOIN requestAS request_1ON employee.emp_id = request_1.emp_idINNERJOIN departmentON employee.emp_department = department.department_idINNERJOIN locationON department.department_location = location.location_idGROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial, employee.emp_lname, department.department_name, location.location_nameORDER BY location.location_name, department.department_name, employee.emp_lname

The section below is the part that may or may not contain information:

SELECT (SELECTSUM(request_duration)AS daystakenFROM request)AS daysleft

So I need it to return results whether this sub query has results or not. Any help would be greatly appreciated!!!

TIA

BUMP... Somebody...|||

Okay, I tried adding the ISNULL to the statement, but I think the problem is because until a request has been put in there is nothing linking the employee table for the JOIN on the request table. When they put in a request it adds an entry to the request table for them. Up till that point, there will be nothing matching the two tables.

Here is my statement as it stands now. Is there anyway to get the results to show if the INNERJOIN isn't finding any results in the request table?

SELECT employee.emp_id,DATEDIFF(mm, employee.emp_begin_accrual,GETDATE()) * employee.emp_accrual_rate - (SELECTSUM(ISNULL(request_duration,'0'))AS daystakenFROM request)AS daysleft, employee.emp_lname +', ' + employee.emp_fname +' ' + employee.emp_minitial +'.'AS emp_name, department.department_name, location.location_nameFROM employeeINNERJOIN requestAS request_1ON employee.emp_id = request_1.emp_idINNERJOIN departmentON employee.emp_department = department.department_idINNERJOIN locationON department.department_location = location.location_idGROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial, employee.emp_lname, department.department_name, location.location_nameORDER BY location.location_name, department.department_name, employee.emp_lname

I know it seems I am just talking to myself at this point, but I would LOVE for someone to join my conversation. Thanks in advance for any help!!!

|||

Okay, I figured it out. Had to switch my query to a LEFT OUTER JOIN.

SELECT employee.emp_id,DATEDIFF(mm, employee.emp_begin_accrual,GETDATE()) * employee.emp_accrual_rate - (SELECTSUM(ISNULL(request_duration,'0'))AS daystakenFROM request)AS daysleft, employee.emp_lname +', ' + employee.emp_fname +' ' + employee.emp_minitial +'.'AS emp_name, department.department_name, location.location_nameFROM employeeLEFTOUTER JOIN requestAS request_1ON employee.emp_id = request_1.emp_idINNERJOIN departmentON employee.emp_department = department.department_idINNERJOIN locationON department.department_location = location.location_idGROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial, employee.emp_lname, department.department_name, location.location_nameORDER BY location.location_name, department.department_name, employee.emp_lname

excel vba to generate flat file definition

What object do I reference to use SSIS from Excel. I want to generate a flat file definition based on Excel. I have a lot of fields to import and I don't feel like creating them as flat file columns. I have a few tables and I get the source file format from the vendor in an Excel format. What I would like to do is generate a flat file connection in an empty package using VBA.

I don't think this is possible. VBA is a COM based environment whereas the SSIS API is dotnet.

I stand to be corrected. If there is a way of calling dotnet APIs from VBA then I don't know about it.

-Jamie

|||As Jamie notes, our documentation is aimed at using the SSIS API through managed code. Using the native API is unsupported. You might be able to work backwards from the managed examples, though, if you're feeling adventurous. I'd suggest you start here: http://msdn2.microsoft.com/en-us/library/ms345167.aspx

Excel Render - Print Header on Each Page

Hello Microsoft or Forum Members:

This seems like a simple Microsoft Based Reporting System 101 feature...But I can not find out how to do it (among other things that seem like it should work with Microsoft rendering to Microsoft).

How do I make the SSRS 2005 Header for the Report print on each of the pages in excel once it is rendered from SSRS?

All I want is an answer by MSFT that says "YES" it can be done and how or "No" it is not currently available.

I noticed I am not the only one with this type of simple question:

(see this unanswered post)

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1281464&SiteID=1

Thank You guys!

goodguy1974 wrote:

"No" it is not currently available.

I believe that would be the answer. It seems to me that the Excel export renders only the content of the report, not so much the layout of the page. A page rendered in an internet browser is not quite the same as a page rendered in excel.

|||

Thank you...

I wish you had a MSFT after your name so I could tell my boss that Microsoft says it can't be done with this version....

Still hoping...

Thanks Again!

|||

How many pages are your reports?

|||

The reports range from 14-200 pages...