Prev: Help with duplicate fields
Next: Tom and Plamen...
From: --CELKO-- on 4 Mar 2010 11:35
DDL and clear specs would really help. Here is another way. Build a
calendar table of reporting periods for months of the form: (cal_date,
cal_year_month_name, year_month_start_date, year_month_end_date). SQL
is meant for joins rather than function calls. Why did you need the
current notes for a historical query?

SELECT H.people_link_id, C.cal_year_month_name
FROM Client_Activity_History AS H,
Report_Period_Calendar AS C
WHERE H.activity_date BETWEEN C.year_month_start_date
AND C.year_month_end_date
GROUP BY H.people_link_id
HAVING SUM (CASE WHEN H.activity_code = 64
THEN 1 ELSE 0 END) > 1;

From: DavidC on 4 Mar 2010 12:14
Thank you. That is good to know as the ActivityHistory table is very large.
--
David


"Plamen Ratchev" wrote:

> The predicates that I used will allow you to utilize efficiently any indexes on the ActivityDate column. If you use the
> YEAR and MONTH functions on the column you will not be able to utilize indexes to seek.
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com
> .
>
From: DavidC on 4 Mar 2010 13:38
The table named ClientCaseNotes can be deceiving as it does not contain
notes. The ActivityHistory contains notes about a case (of which there are
many). The ClientCaseNotes records need to link to the ActivityHistory (via
ActivityID) as they contain billable units which occur about 10% of the time
a record is created in ActivityHistory.
--
David


"--CELKO--" wrote:

> DDL and clear specs would really help. Here is another way. Build a
> calendar table of reporting periods for months of the form: (cal_date,
> cal_year_month_name, year_month_start_date, year_month_end_date). SQL
> is meant for joins rather than function calls. Why did you need the
> current notes for a historical query?
>
> SELECT H.people_link_id, C.cal_year_month_name
> FROM Client_Activity_History AS H,
> Report_Period_Calendar AS C
> WHERE H.activity_date BETWEEN C.year_month_start_date
> AND C.year_month_end_date
> GROUP BY H.people_link_id
> HAVING SUM (CASE WHEN H.activity_code = 64
> THEN 1 ELSE 0 END) > 1;
>
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: Help with duplicate fields
Next: Tom and Plamen...