August 23, 2005 at 10:25 am
I have a table with two fields, startDate and endDate. I want to find out how many entries took more than a variable number of years i.e. the following condition:
datediff(yy, startDate, endDate) > @numYears
I also need a case statement to filter out records that don't have both fields filled in. Due to human error, a field is occasionally left blank. To leave those out of the count, I used a case statement to make them -1.
How do I accomplish this count? I tried and failed to make the CASE statement in the WHERE clause work.
Thanks in advance!
August 23, 2005 at 10:48 am
Can you please post some example Create table statements, Some sample data, your code, and what you want the results to look like.
In looking at your question, It does not appear that your Start date, or end date columns in the table are a datetime data type, to be left Blank are they strings?
What does your current where clause look like.
This should be okay
Where (StartDate <> '' and End Date <> '')
August 23, 2005 at 10:54 am
where
startDate IS NOT NULL and
endDate IS NOT NULL and
datediff(yy, startDate, endDate) > @numYears
Vasc
August 23, 2005 at 10:58 am
Right.... stupid me forgot the easy way to do things.
August 23, 2005 at 11:26 am
Make sure you have a covering index on those 2 columns (assuming it's not already included in the clustered index). That'll allow the server to use an index instead of scanning the whole table.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply