Showing posts with label syntax. Show all posts
Showing posts with label syntax. Show all posts

Wednesday, March 28, 2012

Optimizing a query

Hi
First of all the first script throws a syntax error. Is that mysql syntax?
SELECT Col1 FROM
(
SELECT table1.Col1 FROM table1 JOIN table2 ON table1.Col1 =
table2.Col1 WHERE exp1 AND ....
) AS Der
--Or
SELECT * FROM
(
SELECT table1.Col1 FROM table1 WHERE exp1
UNION ALL
SELECT table1.Col1 FROM table2 WHERE exp2
) AS Der
"jeeji" <jihad_dk@.yahoo.com> wrote in message
news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...
> Hi
> I have a query which I would like to optimize and thought I had found a
> solution.
> The query is a join of several selects from several different tables.
> So we have in general the following:
> SELECT Col1 FROM
> (
> SELECT table1.Col1 FROM table1 WHERE exp1
> JOIN
> SELECT table1.Col1 FROM table2 WHERE exp2
> ON table1.Col1 = table2.Col1
> )
> or something like that. Of course my query was a lot larger than this,
> but it is just to illustrate the problem.
> The above was too slow, and I made an attempt to optimize it and
> thought it did work. The following is what I did:
> Instead of the SELECT's I could create temporary tables and select into
> those so I get the following:
> SELECT table1.Col1
> INTO #temp_table1
> FROM table1 WHERE exp1
> SELECT table1.Col1
> INTO #temp_table2
> FROM table2 WHERE exp2
> And then join those two tables:
> SELECT Col1 FROM
> (
> #temp_table1
> JOIN
> #temp_table2
> ON #temp_table1.Col1 = #temp_table2.Col1
> )
> and at the end drop the temporary tables.
> DROP Table #temp_table1
> DROP Table #temp_table2
> This gives the same result as the original statement.
> I profiled a bit and saw that if I created the temp tables, and ran the
> join statement several times after each other, the first time it is run
> it take a lot of time compared to the following. Can anyone explain to
> me why this is the case? I thought that the temporary tables were
> created and loaded into the cache, and therefore all calls to the big
> JOIN statement should take equally long time.
> The thing is that I cannot reuse the temporary tables, so I get no
> benefit from all the above.
> Thank you very much in advance
> Jeeji
>Uri,
I think that is just a small fragment of the query. (At least I hope it's
just a fragment...)
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"jeeji" <jihad_dk@.yahoo.com> wrote in message
news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...
> Hi
> I have a query which I would like to optimize and thought I had found a
> solution.
> The query is a join of several selects from several different tables.
> So we have in general the following:
> SELECT Col1 FROM
> (
> SELECT table1.Col1 FROM table1 WHERE exp1
> JOIN
> SELECT table1.Col1 FROM table2 WHERE exp2
> ON table1.Col1 = table2.Col1
> )
> or something like that. Of course my query was a lot larger than this,
> but it is just to illustrate the problem.
> The above was too slow, and I made an attempt to optimize it and
> thought it did work. The following is what I did:
> Instead of the SELECT's I could create temporary tables and select into
> those so I get the following:
> SELECT table1.Col1
> INTO #temp_table1
> FROM table1 WHERE exp1
> SELECT table1.Col1
> INTO #temp_table2
> FROM table2 WHERE exp2
> And then join those two tables:
> SELECT Col1 FROM
> (
> #temp_table1
> JOIN
> #temp_table2
> ON #temp_table1.Col1 = #temp_table2.Col1
> )
> and at the end drop the temporary tables.
> DROP Table #temp_table1
> DROP Table #temp_table2
> This gives the same result as the original statement.
> I profiled a bit and saw that if I created the temp tables, and ran the
> join statement several times after each other, the first time it is run
> it take a lot of time compared to the following. Can anyone explain to
> me why this is the case? I thought that the temporary tables were
> created and loaded into the cache, and therefore all calls to the big
> JOIN statement should take equally long time.
> The thing is that I cannot reuse the temporary tables, so I get no
> benefit from all the above.
> Thank you very much in advance
> Jeeji
>|||Hi
I have a query which I would like to optimize and thought I had found a
solution.
The query is a join of several selects from several different tables.
So we have in general the following:
SELECT Col1 FROM
(
SELECT table1.Col1 FROM table1 WHERE exp1
JOIN
SELECT table1.Col1 FROM table2 WHERE exp2
ON table1.Col1 = table2.Col1
)
or something like that. Of course my query was a lot larger than this,
but it is just to illustrate the problem.
The above was too slow, and I made an attempt to optimize it and
thought it did work. The following is what I did:
Instead of the SELECT's I could create temporary tables and select into
those so I get the following:
SELECT table1.Col1
INTO #temp_table1
FROM table1 WHERE exp1
SELECT table1.Col1
INTO #temp_table2
FROM table2 WHERE exp2
And then join those two tables:
SELECT Col1 FROM
(
#temp_table1
JOIN
#temp_table2
ON #temp_table1.Col1 = #temp_table2.Col1
)
and at the end drop the temporary tables.
DROP Table #temp_table1
DROP Table #temp_table2
This gives the same result as the original statement.
I profiled a bit and saw that if I created the temp tables, and ran the
join statement several times after each other, the first time it is run
it take a lot of time compared to the following. Can anyone explain to
me why this is the case? I thought that the temporary tables were
created and loaded into the cache, and therefore all calls to the big
JOIN statement should take equally long time.
The thing is that I cannot reuse the temporary tables, so I get no
benefit from all the above.
Thank you very much in advance
Jeeji|||Hi
First of all the first script throws a syntax error. Is that mysql syntax?
SELECT Col1 FROM
(
SELECT table1.Col1 FROM table1 JOIN table2 ON table1.Col1 =
table2.Col1 WHERE exp1 AND ....
) AS Der
--Or
SELECT * FROM
(
SELECT table1.Col1 FROM table1 WHERE exp1
UNION ALL
SELECT table1.Col1 FROM table2 WHERE exp2
) AS Der
"jeeji" <jihad_dk@.yahoo.com> wrote in message
news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...
> Hi
> I have a query which I would like to optimize and thought I had found a
> solution.
> The query is a join of several selects from several different tables.
> So we have in general the following:
> SELECT Col1 FROM
> (
> SELECT table1.Col1 FROM table1 WHERE exp1
> JOIN
> SELECT table1.Col1 FROM table2 WHERE exp2
> ON table1.Col1 = table2.Col1
> )
> or something like that. Of course my query was a lot larger than this,
> but it is just to illustrate the problem.
> The above was too slow, and I made an attempt to optimize it and
> thought it did work. The following is what I did:
> Instead of the SELECT's I could create temporary tables and select into
> those so I get the following:
> SELECT table1.Col1
> INTO #temp_table1
> FROM table1 WHERE exp1
> SELECT table1.Col1
> INTO #temp_table2
> FROM table2 WHERE exp2
> And then join those two tables:
> SELECT Col1 FROM
> (
> #temp_table1
> JOIN
> #temp_table2
> ON #temp_table1.Col1 = #temp_table2.Col1
> )
> and at the end drop the temporary tables.
> DROP Table #temp_table1
> DROP Table #temp_table2
> This gives the same result as the original statement.
> I profiled a bit and saw that if I created the temp tables, and ran the
> join statement several times after each other, the first time it is run
> it take a lot of time compared to the following. Can anyone explain to
> me why this is the case? I thought that the temporary tables were
> created and loaded into the cache, and therefore all calls to the big
> JOIN statement should take equally long time.
> The thing is that I cannot reuse the temporary tables, so I get no
> benefit from all the above.
> Thank you very much in advance
> Jeeji
>|||Uri,
I think that is just a small fragment of the query. (At least I hope it's
just a fragment...)
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"jeeji" <jihad_dk@.yahoo.com> wrote in message
news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...
> Hi
> I have a query which I would like to optimize and thought I had found a
> solution.
> The query is a join of several selects from several different tables.
> So we have in general the following:
> SELECT Col1 FROM
> (
> SELECT table1.Col1 FROM table1 WHERE exp1
> JOIN
> SELECT table1.Col1 FROM table2 WHERE exp2
> ON table1.Col1 = table2.Col1
> )
> or something like that. Of course my query was a lot larger than this,
> but it is just to illustrate the problem.
> The above was too slow, and I made an attempt to optimize it and
> thought it did work. The following is what I did:
> Instead of the SELECT's I could create temporary tables and select into
> those so I get the following:
> SELECT table1.Col1
> INTO #temp_table1
> FROM table1 WHERE exp1
> SELECT table1.Col1
> INTO #temp_table2
> FROM table2 WHERE exp2
> And then join those two tables:
> SELECT Col1 FROM
> (
> #temp_table1
> JOIN
> #temp_table2
> ON #temp_table1.Col1 = #temp_table2.Col1
> )
> and at the end drop the temporary tables.
> DROP Table #temp_table1
> DROP Table #temp_table2
> This gives the same result as the original statement.
> I profiled a bit and saw that if I created the temp tables, and ran the
> join statement several times after each other, the first time it is run
> it take a lot of time compared to the following. Can anyone explain to
> me why this is the case? I thought that the temporary tables were
> created and loaded into the cache, and therefore all calls to the big
> JOIN statement should take equally long time.
> The thing is that I cannot reuse the temporary tables, so I get no
> benefit from all the above.
> Thank you very much in advance
> Jeeji
>|||Hi.
I thought I made it clear that it was just a fragment of the code. But
now something new came to my attention.
I can see that if I clear the SQL server cache and call the first SQL
statement (the one without the temporary table), the SQL profiler shows
that this call takes longer (around twice) than it takes if I clear the
server cache and run the temporary table version of the call.
However if I do the same calls from a C# application, the opposite
occurs.
To clear the SQL server cache I use:
DBCC FreeSystemCache('All')
And for the C# application I am using .Net 2.0, using the
System.Data.SQLClient calls.
Any advice would just be great.
Arnie Rowland wrote:[vbcol=seagreen]
> Uri,
> I think that is just a small fragment of the query. (At least I hope it's
> just a fragment...)
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "jeeji" <jihad_dk@.yahoo.com> wrote in message
> news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...|||Sorry for the distrubance guys. I figured it out myself.
The problem is that I did not clear the cache from the C# application.
If I did, the same results show.
Jeeji
jeeji skrev:
[vbcol=seagreen]
> Hi.
> I thought I made it clear that it was just a fragment of the code. But
> now something new came to my attention.
> I can see that if I clear the SQL server cache and call the first SQL
> statement (the one without the temporary table), the SQL profiler shows
> that this call takes longer (around twice) than it takes if I clear the
> server cache and run the temporary table version of the call.
> However if I do the same calls from a C# application, the opposite
> occurs.
> To clear the SQL server cache I use:
> DBCC FreeSystemCache('All')
> And for the C# application I am using .Net 2.0, using the
> System.Data.SQLClient calls.
> Any advice would just be great.
> Arnie Rowland wrote:|||Hi.
I thought I made it clear that it was just a fragment of the code. But
now something new came to my attention.
I can see that if I clear the SQL server cache and call the first SQL
statement (the one without the temporary table), the SQL profiler shows
that this call takes longer (around twice) than it takes if I clear the
server cache and run the temporary table version of the call.
However if I do the same calls from a C# application, the opposite
occurs.
To clear the SQL server cache I use:
DBCC FreeSystemCache('All')
And for the C# application I am using .Net 2.0, using the
System.Data.SQLClient calls.
Any advice would just be great.
Arnie Rowland wrote:[vbcol=seagreen]
> Uri,
> I think that is just a small fragment of the query. (At least I hope it's
> just a fragment...)
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "jeeji" <jihad_dk@.yahoo.com> wrote in message
> news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...|||Sorry for the distrubance guys. I figured it out myself.
The problem is that I did not clear the cache from the C# application.
If I did, the same results show.
Jeeji
jeeji skrev:
[vbcol=seagreen]
> Hi.
> I thought I made it clear that it was just a fragment of the code. But
> now something new came to my attention.
> I can see that if I clear the SQL server cache and call the first SQL
> statement (the one without the temporary table), the SQL profiler shows
> that this call takes longer (around twice) than it takes if I clear the
> server cache and run the temporary table version of the call.
> However if I do the same calls from a C# application, the opposite
> occurs.
> To clear the SQL server cache I use:
> DBCC FreeSystemCache('All')
> And for the C# application I am using .Net 2.0, using the
> System.Data.SQLClient calls.
> Any advice would just be great.
> Arnie Rowland wrote:

Friday, March 9, 2012

Opinion on SQL Stored Procedure Syntax

I'm trying to improve the quality of life of this SP
CREATE Procedure p_generatedata @.StartDate smalldatetime, @.EndDate
smalldatetime
as
Set Nocount on
delete ttemp_data
Insert into ttemp_data
SELECT appeal_codes, appeal_type, appeal_code,
SUM(total_yes) AS total_yes, SUM(Pledge_Amount)
AS total_yes_amount, SUM(total_cc) total_cc,
SUM(total_cc_amount) AS total_cc_amount, SUM(last_gift)
AS last_gift_total, SUM(total_wc) AS total_wc, SUM(total_no)
AS total_no, SUM(call_resolution) AS total_resolves, leads
FROM (SELECT o.appeal_codes, o.appeal_code, o.appeal_type,
CASE WHEN o.call_resolution LIKE 1 THEN COUNT
(o.call_resolution)
END AS 'total_yes', SUM(o.Pledge_Amount)
AS Pledge_Amount,
CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2 THEN
COUNT(o.if_yes_pledge_or_credit_card)
END AS 'total_cc',
CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2 THEN
o.pledge_amount
END AS 'total_cc_amount', SUM(o.last_gift) AS last_gift,
CASE WHEN o.call_resolution LIKE 3 THEN
COUNT(o.call_resolution)
END AS 'total_wc',
CASE WHEN o.call_resolution LIKE 2 THEN
COUNT(o.call_resolution)
END AS 'total_no', COUNT(o.call_resolution)
AS call_resolution, c.lead as 'leads'
FROM tlead_sheets AS o INNER JOIN tappeal_codes AS c ON
o.appeal_codes = c.appeal_code
GROUP BY o.appeal_codes, o.appeal_code, o.call_resolution,
o.if_yes_pledge_or_credit_card, o.pledge_amount,
o.date_fld, o.last_gift, o.appeal_type, c.lead
HAVING o.date_fld BETWEEN @.StartDate AND @.EndDate)
AS tbl
GROUP BY appeal_codes, appeal_code, appeal_type, leads
Thanks for the helpWhat does "quality of life" mean? Do you want it to be happier? Look
prettier? Make more money? Be more efficient? All of the above? None of
the above?
<vncntj@.hotmail.com> wrote in message
news:1148417269.627636.34530@.u72g2000cwu.googlegroups.com...
> I'm trying to improve the quality of life of this SP
> CREATE Procedure p_generatedata @.StartDate smalldatetime, @.EndDate
> smalldatetime
> as
> Set Nocount on
> delete ttemp_data
> Insert into ttemp_data
> SELECT appeal_codes, appeal_type, appeal_code,
> SUM(total_yes) AS total_yes, SUM(Pledge_Amount)
> AS total_yes_amount, SUM(total_cc) total_cc,
> SUM(total_cc_amount) AS total_cc_amount, SUM(last_gift)
> AS last_gift_total, SUM(total_wc) AS total_wc, SUM(total_no)
> AS total_no, SUM(call_resolution) AS total_resolves, leads
> FROM (SELECT o.appeal_codes, o.appeal_code, o.appeal_type,
> CASE WHEN o.call_resolution LIKE 1 THEN COUNT
> (o.call_resolution)
> END AS 'total_yes', SUM(o.Pledge_Amount)
> AS Pledge_Amount,
> CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2 THEN
> COUNT(o.if_yes_pledge_or_credit_card)
> END AS 'total_cc',
> CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2 THEN
> o.pledge_amount
> END AS 'total_cc_amount', SUM(o.last_gift) AS last_gift,
> CASE WHEN o.call_resolution LIKE 3 THEN
> COUNT(o.call_resolution)
> END AS 'total_wc',
> CASE WHEN o.call_resolution LIKE 2 THEN
> COUNT(o.call_resolution)
> END AS 'total_no', COUNT(o.call_resolution)
> AS call_resolution, c.lead as 'leads'
> FROM tlead_sheets AS o INNER JOIN tappeal_codes AS c ON
> o.appeal_codes = c.appeal_code
> GROUP BY o.appeal_codes, o.appeal_code, o.call_resolution,
> o.if_yes_pledge_or_credit_card, o.pledge_amount,
> o.date_fld, o.last_gift, o.appeal_type, c.lead
> HAVING o.date_fld BETWEEN @.StartDate AND @.EndDate)
> AS tbl
> GROUP BY appeal_codes, appeal_code, appeal_type, leads
> Thanks for the help
>|||The assignment of colum aliases:
AS 'total_cc'
uses single quotes. Why use quotes? total_cc is a perfectly valid
column name, and anyway if a column name is in quotes it should be
"double quotes".
Looking at the construct:
CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
THEN COUNT(o.if_yes_pledge_or_credit_card)
END AS 'total_cc',
I am not sure that will work. The more common way to code this -
assuming I understand what it is supposed to return - is:
SUM(CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
THEN 1 ELSE 0
END) AS 'total_cc',
Looking at the test:
CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
I forsee problems with the LIKE. LIKE is for strings, not numbers.
The HAVING test in the inner query looks like it should be a WHERE
test.
I suspect that the use of a derived table is avoidable. Take the
expression from the inner query that is SUMed in the outer:
CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
THEN o.pledge_amount
END AS 'total_cc_amount',
Consider
SUM(CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
THEN o.pledge_amount
ELSE 0
END AS total_cc_amount,
I reworked the whole thing into a single query that I THINK would do
what you are trying to do. I did not try to fix the LIKE tests and
such. It will be easier to read using a fixed pitch font.
SELECT o.appeal_codes,
o.appeal_type,
o.appeal_code,
SUM(CASE WHEN o.call_resolution LIKE 1
THEN COUNT (o.call_resolution)
ELSE 0
END) AS total_yes,
SUM(o.Pledge_Amount) AS total_yes_amount,
SUM(CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
THEN 1
ELSE 0
END) as total_cc,
SUM(CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
THEN o.pledge_amount
ELSE 0
END) AS total_cc_amount,
SUM(o.last_gift) AS last_gift_total,
SUM(CASE WHEN o.call_resolution LIKE 3
THEN 1
ELSE 0
END) AS total_wc,
SUM(CASE WHEN o.call_resolution LIKE 2
THEN 1
ELSE 0
END) AS total_no,
COUNT(o.call_resolution) AS total_resolves,
C.leads
FROM tlead_sheets AS o
JOIN tappeal_codes AS c
ON o.appeal_codes = c.appeal_code
WHERE o.date_fld BETWEEN @.StartDate AND @.EndDate
GROUP BY o.appeal_codes, o.appeal_code, o.appeal_type, c.leads
Hope that helps some.
Roy Harvey
Beacon Falls, CT
On 23 May 2006 13:47:49 -0700, vncntj@.hotmail.com wrote:

>I'm trying to improve the quality of life of this SP
>CREATE Procedure p_generatedata @.StartDate smalldatetime, @.EndDate
>smalldatetime
>as
>Set Nocount on
>delete ttemp_data
>Insert into ttemp_data
>SELECT appeal_codes, appeal_type, appeal_code,
> SUM(total_yes) AS total_yes, SUM(Pledge_Amount)
> AS total_yes_amount, SUM(total_cc) total_cc,
> SUM(total_cc_amount) AS total_cc_amount, SUM(last_gift)
> AS last_gift_total, SUM(total_wc) AS total_wc, SUM(total_no)
> AS total_no, SUM(call_resolution) AS total_resolves, leads
>FROM (SELECT o.appeal_codes, o.appeal_code, o.appeal_type,
> CASE WHEN o.call_resolution LIKE 1 THEN COUNT
>(o.call_resolution)
> END AS 'total_yes', SUM(o.Pledge_Amount)
> AS Pledge_Amount,
> CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2 THEN
>COUNT(o.if_yes_pledge_or_credit_card)
> END AS 'total_cc',
> CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2 THEN
>o.pledge_amount
> END AS 'total_cc_amount', SUM(o.last_gift) AS last_gift,
> CASE WHEN o.call_resolution LIKE 3 THEN
>COUNT(o.call_resolution)
> END AS 'total_wc',
> CASE WHEN o.call_resolution LIKE 2 THEN
>COUNT(o.call_resolution)
> END AS 'total_no', COUNT(o.call_resolution)
> AS call_resolution, c.lead as 'leads'
> FROM tlead_sheets AS o INNER JOIN tappeal_codes AS c ON
>o.appeal_codes = c.appeal_code
> GROUP BY o.appeal_codes, o.appeal_code, o.call_resolution,
>o.if_yes_pledge_or_credit_card, o.pledge_amount,
> o.date_fld, o.last_gift, o.appeal_type, c.lead
> HAVING o.date_fld BETWEEN @.StartDate AND @.EndDate)
> AS tbl
>GROUP BY appeal_codes, appeal_code, appeal_type, leads
>Thanks for the help|||Thanks..
Roy Harvey wrote:
> The assignment of colum aliases:
> AS 'total_cc'
> uses single quotes. Why use quotes? total_cc is a perfectly valid
> column name, and anyway if a column name is in quotes it should be
> "double quotes".
> Looking at the construct:
> CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
> THEN COUNT(o.if_yes_pledge_or_credit_card)
> END AS 'total_cc',
> I am not sure that will work. The more common way to code this -
> assuming I understand what it is supposed to return - is:
> SUM(CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
> THEN 1 ELSE 0
> END) AS 'total_cc',
> Looking at the test:
> CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
> I forsee problems with the LIKE. LIKE is for strings, not numbers.
> The HAVING test in the inner query looks like it should be a WHERE
> test.
> I suspect that the use of a derived table is avoidable. Take the
> expression from the inner query that is SUMed in the outer:
> CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
> THEN o.pledge_amount
> END AS 'total_cc_amount',
> Consider
> SUM(CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
> THEN o.pledge_amount
> ELSE 0
> END AS total_cc_amount,
> I reworked the whole thing into a single query that I THINK would do
> what you are trying to do. I did not try to fix the LIKE tests and
> such. It will be easier to read using a fixed pitch font.
> SELECT o.appeal_codes,
> o.appeal_type,
> o.appeal_code,
> SUM(CASE WHEN o.call_resolution LIKE 1
> THEN COUNT (o.call_resolution)
> ELSE 0
> END) AS total_yes,
> SUM(o.Pledge_Amount) AS total_yes_amount,
> SUM(CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
> THEN 1
> ELSE 0
> END) as total_cc,
> SUM(CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
> THEN o.pledge_amount
> ELSE 0
> END) AS total_cc_amount,
> SUM(o.last_gift) AS last_gift_total,
> SUM(CASE WHEN o.call_resolution LIKE 3
> THEN 1
> ELSE 0
> END) AS total_wc,
> SUM(CASE WHEN o.call_resolution LIKE 2
> THEN 1
> ELSE 0
> END) AS total_no,
> COUNT(o.call_resolution) AS total_resolves,
> C.leads
> FROM tlead_sheets AS o
> JOIN tappeal_codes AS c
> ON o.appeal_codes = c.appeal_code
> WHERE o.date_fld BETWEEN @.StartDate AND @.EndDate
> GROUP BY o.appeal_codes, o.appeal_code, o.appeal_type, c.leads
> Hope that helps some.
> Roy Harvey
> Beacon Falls, CT
>
> On 23 May 2006 13:47:49 -0700, vncntj@.hotmail.com wrote:
>