Parameter in Pivot Chart with SQL source

Question

 

Hello,

 

I have a SQL view that includes the following payroll data:

 

Employee#

PeriodEndDate

HireDate

Amount

 

What I'm trying to do is filter the data by choosing the desired period end dates and then totalling the Amount and dividing that amount to get an average daily rate. To do this I'm pulling the data from the SQL 2000 source view into Excel 2007 and creating a pivot chart.

 

In that pivot chart I'm able to select the desired period end dates and create a calculated field for my daily rate.

 

My problem is that I want to exclude employee's that have a Hire Date that is within 90 days of the latest selected period end date. For example, if the Period End dates selected are 3/15/2008 and 3/30/2008, then any employees with a Hire Date greater than 12/31/2007 (3/30 less 90 days) should NOT be included in the pivot chart.

 

Is there an easy way for me to do this, can I pass parameters to SQL via Excel?

 

Thanks for any help

Leo

Answer 1

Hello

Add this into the WHERE clause:

WHERE DATEDIFF(d,[Hire Date],[End Date]) <= 90

Answer 2

use DATEDIFF(d,HireDate,PeriodEndDate)>90 in where clause

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 3

if you mix both above answers then you got right or use this


in where clause

Where  HireDate < =  dateadd (dd, -90 , PeriodEndDate )

this will return 90 days old date and check data up to that date.

---

Shamas Saeed MCITP-DBD 2005 http://sqlservercoollinks.blogspot.com