Ok, I think I see what you are saying. I'll play with that idea Tuesday when I am back at work and see if I can get this off my plate. I appreciate the help.

BOb


>I'm not sure that you'd have to. If you used your query from below to create a derived table or a table variable with the following structure:
>
>employeeid
>taxRuleID
>PTD
>MTD
>QTD
>YTD
>
>Then you could do your counts against this, ignoring the employees that have a zero total.
>
>-Mike
>
>>Yea, I think I see what you are getting too.
>>
>>But, I wanted to avoid 4 passes through the same records for each derived table. Isn't duplicating the FROM part of the query 4 times with different date ranges mean that SQL has to make 4 passes at the data or is it smart enough to do it all in one pass?
>>
>>Can you do a small example?
>>
>>BOb
>>
>>
>>>What about calculating the wages per rule per period and then aggregating them into counts? You could do it with either a view or derived table.
>>>
>>>-Mike
>>>
>>>
>>>>If it does, I don't see it.
>>>>
>>>>>Bob,
>>>>>
>>>>>I don't 100% understand what you're asking (might not be your wording, I'm really tired) but does the NULLIF() function help you here?
>>>>>
>>>>>-Mike
>>>>>
>>>>>>Ok Folks,
>>>>>>
>>>>>>Here a problem I need help with, I am sure you will all rise to the occasion as you usually do.
>>>>>>
>>>>>>I am trying to create a query that summerizes employee tax data by taxing entity. Each taxing entity has its own tax_rule_id. I am getting the data for our pay histories table, which has a master table py_ehmast and a child table of py_ehtax with all the tax records. Each mast record is a payroll transaction/check.
>>>>>>
>>>>>>I need to not only summarize the data for each tax_rule_id but I need the summary for an entered date range, then month-to date, quarter-to-date and year-to-date. The user enteres the date range and I determine the dates from that. Not a problem.
>>>>>>
>>>>>>I also need the count of employees that had taxable wages for the period specified. So, I have all this working, here is a simplified version of the query...
>>>>>>
>>>>>>

>>>>>>SELECT
>>>>>>tax_rule_id
>>>>>>,count(distinct case when chkdate between @pdbegin and @pdend then ee_id else null end) count_pd
>>>>>>,count(distinct case when chkdate between @mbegin and @mend then ee_id else null end) count_mtd
>>>>>>,count(distinct case when chkdate between @qbegin and @qend then ee_id else null end) count_qtd
>>>>>>,count(distinct ee_id) count_ytd
>>>>>>FROM py_ehtax t
>>>>>>JOIN py_ehmast m ON t.ehmast_id = m.ehmast_id
>>>>>>WHERE m.chkdate BETWEEN @ybegin AND @yend
>>>>>>

>>>>>>
>>>>>>Ok, all that works great. The problem is. We allow the user to do adjustments. What that does is put records with negative values into the py_eh???? files. So, the sum will come out to the correct amount.
>>>>>>
>>>>>>So, it should be possible that for an ee_id for a specic tax_rule_id the sum for any of the for time ranges could be zero. In that case, I don't want the ee_id included in the count.
>>>>>>
>>>>>>If you understand this, do you have any ideas how I could do this.
>>>>>>
>>>>>>I thought to add to the WHERE something like
>>>>>>
>>>>>>

>>>>>>ee_id IN (SELECT ee_id, tax_rule_id, sum(tax_cur)
>>>>>>FROM py_ehtax t1
>>>>>>join py_ehmast m1 on t1.ehmast_id = m1.ehmast_id
>>>>>>WHERE ee_id = m.ee_id
>>>>>>     AND tax_rule_id = t.tax_rule_id
>>>>>>GROUP by ee_id, tax_rule_id
>>>>>>HAVING sum(tax_cur) > 0)
>>>>>>

>>>>>>
>>>>>>But, that would only apply to the YTD part...
>>>>>>
>>>>>>Thanks,
>>>>>>BOb