Optimizing Query - Product Tips

Hello everyone!
I've got a problem with a real slow query, I would be very happy if somebody has any idea to improve the speed of it...
The idea is to get the top 2 products, a customer hasn't bought wich are in his interest...

query (simplificated)
---------------
SELECT TOP 2 prodID, Title, Price FROM bestSold7Days WHERE
prodID NOT IN (SELECT prodID FROM orders INNER JOIN orderProducts ON orders.orderID = orderProducts.orderID WHERE (orders.custID=394))
AND
(prodType = COALESCE((SELECT TOP 1 products.prodID FROM orders INNER JOIN orderProducts ON order.orderID = orderProducts.orderID INNER JOIN products ON orderProducts.prodID = products.prodID WHERE (orders.custID=394) GROUP BY products.prodType ORDER BY SUM(orderProducts.PCS) DESC), 2))
---------------
end query

(COALESCE is for replacing if the customer hasnt ordered anything, or hasnt ordered anything of this type)...

Thanks for any time spent!DDL and sample data would help alot...

But I'll give it a go...

DDL:

CREATE TABLE...

Sample Data

INSERT INTO myTable(Cols...
SELECT 'data','more data',1,...UNION ALL
SELECT 'data','more data',1,...UNION ALL
SELECT 'data','more data',1,...UNION ALL
SELECT 'data','more data',1,...UNION ALL

Should help us get an answer quicker...|||Or maybe not...I think I hurt myself...

Let me point out a coupld of things...

SELECT TOP 2
prodID
, Title
, Price
FROM bestSold7Days
WHERE prodID NOT IN (SELECT prodID
FROM orders
INNER JOIN orderProducts
ON orders.orderID = orderProducts.orderID
WHERE orders.custID=394)
AND (prodType = COALESCE((SELECT TOP 1
products.prodID
FROM orders
INNER JOIN orderProducts ON order.orderID = orderProducts.orderID
INNER JOIN products ON orderProducts.prodID = products.prodID
WHERE orders.custID=394
GROUP BY products.prodType
ORDER BY SUM(orderProducts.PCS) DESC), 2))

Does this even run?

Does ProdType = ProdId?
a GROUP BY qith no SCALAR in the SELECT?
OREDER BY SUM...what for?
Why the COALESCE? IF it's NULL (what ever it is) is won't be evaluated

I guess what I'm saying is..post the ddl sample data AND expected results, and tell us what the business req is...

it'll be a lot faster that way...|||I don't have access to the sql server right now, so I cant give you the result and the table structure 100% - maybe i mistyped something on the query itself, but i dont think so..
I've missed the ORDER by cntSold DESC at the end of the query
it worked as far as i've tried :-)
sorry - let me get some things:

table bestSold7Days (generated hourly, articles best sold in the last 7 days)
prodID - product ID
cntSold - sold pieces in 7 days
prodType - product type - e.g. 0 hardware, 1 software, 2 special product
Title - product Title

table orders
orderID - order ID identity
custID - customer ID

table orderProdcuts (products contained in order)
orderID - order ID
prodID - product ID
pcs - Pieces ordered

the whole query puts out following:
prodID, Title, Price
349, H53-39, 393.33
39392, P3838-3, 5959.21

the sense of the hole thing is to get the top 2 sold products in the last 7 days, wich are the same of interest (prodType) wich the customer prefers and which he didn't already order...|||In your query statement, there are three JOIN words. That makes execution of the query very slow. My suggestion is that you may use a stored procedure in which you can separate your query into several steps. That will improve the performance.|||Hello gyuan,
I've already tried that.
Did a sp wich got me the favorite prodType, but overall it didn't really improve the performance very much.

Currently I'm using it through the stored procedure (4 different tables, 4 different prodTypes) and then querying the top 2 products which he didn't already order.

The SP gives out: 3,4,3,50 - this i split in vb and use it in the queries following...

The whole thing takes from 8-30 seconds (depending on how much the customer ordered)|||What about this?

select top 2 b.prodid
from
BestSold7Days b
left join
(
select distinct(p.prodID)
from Orders o
inner join OrderProducts p
on p.orderid = o.orderid
where o.custID = 394
) x
on x.prodID = b.prodID
where x.prodID is null
order by b.cntSold desc|||A good stored procedure can definitely improve the execution speed of the query statements, but it depends on the content of the stored procedure. If you can post the details of your tables and requirements, that will give us a good help to solve it.|||table sets:
products
products2
products3
products4
(all same structure)
prodID - identity
prodType - product type - int

orders
orderID - order ID (identity)
custID - customer ID - int

orderProducts
orderID - order ID - int
prodID - int
pcs - int - pieces ordered

Here's the sp i currently use to do the query:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE FUNCTION [dbo].[getTopprodType] (@.custID int )
RETURNS varchar(50) AS
BEGIN
DECLARE @.Ret varchar(50)
SET @.Ret=ISNULL((SELECT TOP 1 CONVERT(varchar(50),[products].prodType) FROM orders INNER JOIN orderProducts ON orders.orderID = orderProducts.orderID INNER JOIN products ON orderProducts.prodID = products.prodID WHERE (orders.custID = @.custID) GROUP BY products.prodType ORDER BY COUNT(orderProducts.PCS) DESC),'')
SET @.Ret=@.Ret + ',' + ISNULL((SELECT TOP 1 CONVERT(varchar(50),[products2].prodType) FROM orders INNER JOIN orderProducts ON orders.orderID = orderProducts.orderID INNER JOIN products2 ON orderProducts.prodID = products2.prodID WHERE (orders.custID = @.custID) GROUP BY products2.prodType ORDER BY COUNT(orderProducts.PCS) DESC),'')
SET @.Ret=@.Ret + ',' + ISNULL((SELECT TOP 1 CONVERT(varchar(50),[products3].prodType) FROM orders INNER JOIN orderProducts ON orders.orderID = orderProducts.orderID INNER JOIN products3 ON orderProducts.prodID = products3.prodID WHERE (orders.custID = @.custID) GROUP BY products3.prodType ORDER BY COUNT(orderProducts.PCS) DESC),'')
SET @.Ret=@.Ret + ',' + ISNULL((SELECT TOP 1 CONVERT(varchar(50),[products4].prodType) FROM orders INNER JOIN orderProducts ON orders.orderID = orderProducts.orderID INNER JOIN products4 ON orderProducts.prodID = products4.prodID WHERE (orders.custID = @.custID) GROUP BY products4.prodType ORDER BY COUNT(orderProducts.PCS) DESC),'')
RETURN @.Ret
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

There are 4 different product tables (unique id's) wich get together here.
The SP puts out the following
2,3,4,9
I put this into an array in vb to use it for the following query
SELECT TOP 2 bestSold7Days.Anz, products.prodID, products.Title, products.Price FROM bestSold7Days INNER JOIN products ON bestSold7Days.prodID = products.prodID WHERE ((products.Price IS NOT NULL) AND ((SELECT TOP 1 orderProducts.prodID FROM orders INNER JOIN orderProducts ON orders.orderID = orderProducts.orderID WHERE (orders.custID = " & Me.custID & ") GROUP BY orderProducts.prodID HAVING (orderProducts.prodID = bestSold7Days.prodID)) IS NULL)" & tempGen & " ORDER BY bestSold7Days.Anz DESC;

tempGen is replaced by the favourite prodType (e.g. AND prodType=4 if has any)|||found a faster solution for the second query!
if i do the lookup of the products wich the customer already ordered in a derived table (like a join, but the table is a query) it's much faster!
gut the whole process down to ~2-6 seconds.
Now i need to optimize the getTopProdType procedure, still taking some seconds...|||Could you post how many records in each of the tables? And what are indexes on those tables?|||Indexes on all searched fields
prodID
prodType
(in all tables)

products1 ~ 20000 records
products2 ~ 200000 records
products3 ~ 5000 records
products4 ~ 2000000 records
orders ~ 50000 records
orderProducts ~ 2000000 records

prodType is not clustered.
does anybody have a rule when to make an index clustered?|||1. Based on your query, an index on order.custID would be helpful.

2. It would speed up the query a lot if you store customers favorite prodType in a table instead of figuring it out every time you run the query. Daily update on this field might be good enough.|||sorry forgot this field - custID is also indexed.
Only field queried wich is not indexed is the pcs field in the orderProducts table.|||I think item 2 would be very helpful to speed up your query.
If a little slower update on orders related tables is acceptable, you may update the information in table xxx using triggers or whenever an order is updated.

SELECT TOP 2
prodID
, Title
, Price
FROM bestSold7Days
WHERE prodID NOT IN (SELECT prodID
FROM orders
INNER JOIN orderProducts
ON orders.orderID = orderProducts.orderID
WHERE orders.custID=394)
AND (prodType = COALESCE((SELECT prodType
FROM xxxx
WHERE custID=394), 2))|||what do you mean by item 2?
i've just seen that you can only do one clustered index... all main id's are clustered indexes...|||Originally posted by shianmiin
1. Based on your query, an index on order.custID would be helpful.

2. It would speed up the query a lot if you store customers favorite prodType in a table instead of figuring it out every time you run the query. Daily update on this field might be good enough.

I mean the item no. 2 above.|||that query would take 69 hours to finish (~50000 customers, ~5 seconds per prodType get).|||i've just got it working with a runtime of 60 seconds for all customers.
I don't know why it's that fast - but it works :)

INSERT INTO tProdTypeFavourites(custID, prodType1...)
(SELECT ... ) (SELECT ...)

the sub-queries are pretty big but the whole thing runs very fast!|||good job. :)|||thanks - same for you! thnx for every reply!

Optimizing MS OLAP Cube build time and response time

Hi,
Can someone highlight some of the common methods to improve and optimize the CUBE BUILD TIME and RESPONSE TIME using MS OLAP Services with SQL Server?
Regards,
Omkarwhat kindof dataset are you trying to load?

never use ROLAP, unless you have to.

do analysis services on a dedicated server.

www.sqlserverperformance.com

a lot of good tips are there..

Optimize the query performance?

Hi,
I want to improve performance of my query to ensure that my query does not
timeout.
Scenario:
Table : EmpCheckInCheckOut
Felds: RowNumber,TokenID,CheckinTime,CheckoutTi
me,Action,EmpName
Query : Select TokenID,CheckinTime,CheckoutTime,EmpName
,RowNumber from
EmpCheckInCheckOut EC
WHERE (Action = 'In' AND NOT EXISTS ( SELECT TokenID FROM
EmpCheckInCheckOut EC2 WHERE Action = 'Out' and TokenID = EC.TokenID and
CheckinTime = EC.CheckinTime ) ) OR Action ='Out'
Through this query I get all those employees who have checked-in but not
checked out yet and employee who have done checkin and checkout both.
Clustered Index: Action,TokenIDCheckinTime
Query is working fine. But when I bulk insert 400000 records into this
table, query timeout.
So how well I can create Index on this table so that I get query performance
as well as less bulk insert time.
Please give your suggestion to optimize this query or do I need to change
the existing index and create some other index or something else?
Thanks in Advance
PushkarPushkar
It is probable page spliting casued by insertion. Either remove all indexes
just before BULK INSERT and re-create after or re-build them after the
insertion.
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:OOnxDdFOGHA.4052@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I want to improve performance of my query to ensure that my query does not
> timeout.
> Scenario:
> Table : EmpCheckInCheckOut
> Felds: RowNumber,TokenID,CheckinTime,CheckoutTi
me,Action,EmpName
> Query : Select TokenID,CheckinTime,CheckoutTime,EmpName
,RowNumber from
> EmpCheckInCheckOut EC
> WHERE (Action = 'In' AND NOT EXISTS ( SELECT TokenID FROM
> EmpCheckInCheckOut EC2 WHERE Action = 'Out' and TokenID = EC.TokenID and
> CheckinTime = EC.CheckinTime ) ) OR Action ='Out'
>
> Through this query I get all those employees who have checked-in but not
> checked out yet and employee who have done checkin and checkout both.
> Clustered Index: Action,TokenIDCheckinTime
> Query is working fine. But when I bulk insert 400000 records into this
> table, query timeout.
> So how well I can create Index on this table so that I get query
> performance as well as less bulk insert time.
> Please give your suggestion to optimize this query or do I need to change
> the existing index and create some other index or something else?
> Thanks in Advance
> Pushkar
>
>|||Action is a poor choice for the first column of the clustered index.
The ideal is that the first column of any index, but most especially
the clustered, be highly selective. Action appears to be the least
selective choice available.
Looking at this query I woud suggest clustering on either of these:
TokenID,CheckinTime,Action
TokenID,Action,CheckinTime
If you do not change the clustering, then you need a second,
non-clustered index on one or the other of those.
For the bulk insert problem, if your regular processing removes all
rows from the table and then loads them again the idea of dropping the
indexes before the load and putting them back after is a good one. An
alternative would be to have another table,
EmpCheckInCheckOut_Imported, with the same layout but NO indexes. Bulk
insert into that table, then use INSERT/SELECT into the production
table. I would use an ORDER BY on the SELECT that matcches the
clustered index key - might help, can't hurt.
Roy
On Thu, 23 Feb 2006 14:28:04 +0530, "Pushkar"
<pushkartiwari@.gmail.com> wrote:

>Hi,
>I want to improve performance of my query to ensure that my query does not
>timeout.
>Scenario:
>Table : EmpCheckInCheckOut
>Felds: RowNumber,TokenID,CheckinTime,CheckoutTi
me,Action,EmpName
>Query : Select TokenID,CheckinTime,CheckoutTime,EmpName
,RowNumber from
>EmpCheckInCheckOut EC
> WHERE (Action = 'In' AND NOT EXISTS ( SELECT TokenID FROM
>EmpCheckInCheckOut EC2 WHERE Action = 'Out' and TokenID = EC.TokenID and
>CheckinTime = EC.CheckinTime ) ) OR Action ='Out'
>
>Through this query I get all those employees who have checked-in but not
>checked out yet and employee who have done checkin and checkout both.
>Clustered Index: Action,TokenIDCheckinTime
>Query is working fine. But when I bulk insert 400000 records into this
>table, query timeout.
>So how well I can create Index on this table so that I get query performanc
e
>as well as less bulk insert time.
>Please give your suggestion to optimize this query or do I need to change
>the existing index and create some other index or something else?
>Thanks in Advance
>Pushkar
>|||Does action track anythign other than check in and check out? If not, then
you could remove action entirely and just use the checkinTime and
CheckoutTime. You would know whether the employee checked out or not by
checking to see if Checkouttime was null. This assumes that, once checked
in, an employee cannot check in again until they have first checked out.
Having seperate rows for check in and check out, and seperate columns for
the same, seems redundant and can probably be simplified by removing on or
the other.
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:OOnxDdFOGHA.4052@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I want to improve performance of my query to ensure that my query does not
> timeout.
> Scenario:
> Table : EmpCheckInCheckOut
> Felds: RowNumber,TokenID,CheckinTime,CheckoutTi
me,Action,EmpName
> Query : Select TokenID,CheckinTime,CheckoutTime,EmpName
,RowNumber from
> EmpCheckInCheckOut EC
> WHERE (Action = 'In' AND NOT EXISTS ( SELECT TokenID FROM
> EmpCheckInCheckOut EC2 WHERE Action = 'Out' and TokenID = EC.TokenID and
> CheckinTime = EC.CheckinTime ) ) OR Action ='Out'
>
> Through this query I get all those employees who have checked-in but not
> checked out yet and employee who have done checkin and checkout both.
> Clustered Index: Action,TokenIDCheckinTime
> Query is working fine. But when I bulk insert 400000 records into this
> table, query timeout.
> So how well I can create Index on this table so that I get query
performance
> as well as less bulk insert time.
> Please give your suggestion to optimize this query or do I need to change
> the existing index and create some other index or something else?
> Thanks in Advance
> Pushkar
>
>

optimize nologging

Hi,
We have a reporting database with simple recovery model.
To improve performance we have to use SELECT..INTO clause
and create all tables...but problem now is that each
table is populating from 3-4 different result set...so if
we use SELECT ..INTO for first load(we can't use UNION in
SELECT..INTO CLAUSE) then for next 3-4 loads we have to
use INSERT INTO SELECT clause that will do lot of logging.
What are the possible options that we can use in this
scenario?
For temporary solution we are thinking of using SELECT
INTO and create 4 temp tables then bcp out the data and
then use BULK INSERT into origional table --what can be
possible flaws in this scenario?
Thanks
--HarvinderYou can use a derived table in the select statement of the select into, for
example:
SELECT column_1, column_2 INTO new_table
FROM
(SELECT column_1, column_2 FROM table_1
UNION ALL
SELECT column_1, column_2 FROM table_2) AS old_table
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"harvinder" <hs@.metratech.com> wrote in message
news:072401c3787a$cf8048a0$a001280a@.phx.gbl...
> Hi,
> We have a reporting database with simple recovery model.
> To improve performance we have to use SELECT..INTO clause
> and create all tables...but problem now is that each
> table is populating from 3-4 different result set...so if
> we use SELECT ..INTO for first load(we can't use UNION in
> SELECT..INTO CLAUSE) then for next 3-4 loads we have to
> use INSERT INTO SELECT clause that will do lot of logging.
> What are the possible options that we can use in this
> scenario?
> For temporary solution we are thinking of using SELECT
> INTO and create 4 temp tables then bcp out the data and
> then use BULK INSERT into origional table --what can be
> possible flaws in this scenario?
> Thanks
> --Harvinder
>

Optimization gurus: Help with varchar vs. text fields decision

Hi, I'm trying to improve performance for a db that stores messages. The message is currently stored in an ntext field, but when I look at all the records from the past 3 months, I found that 88% are less than 1000 characters, and 97% are less than 3000 characters.

I don't want to actually limit the message size, but it seems like I might get much better performance using a varchar(3000) field to hold most of the messages, and a separate text field just used for those 3% that really are long. Is this a good idea? If so, is it better to put the Message and LongMessage fields in the same table; or, have a separate table to hold the long messages? If it is in a separate table, it would need to be left joined with the message table each time messages are retrieved.

Also -- I am getting about 700 new messages daily, and right now have over 150,000 messages stored. The vast majority of activity involves new messages. Is this a good situation to look at using horizontal partitioning?

Thanks for any help, I don't really have anyone to discuss this with and it is really helpful to get some other views!!

Are you able to upgrade to SQL2005? VARCHAR(max) would be a simple solution to your problem.

|||

Yay, I'm already on SQL server 2005, so I could use varchar(max) -- now that I've heard of it! Are there performance issues to be aware of with max? Any drawback to a design where the row size will vary wildly from row to row??

|||

Celestine:

when I look at all the records from the past 3 months, I found that 88% are less than 1000 characters, and 97% are less than 3000 characters.

I calculated wrong -- it is an ntext field, so each char is two bytes, not one byte. Meaning 97% of the messages are actually less than 1500 characters, not 3000. All the messages are in English, so I'm not going to continue using ntext or nvarchar.

|||

Varchar(max) allows rows to span physical blocks, hence no row length restriction. Why not look it up on Books-On_line BOL?

|||

I understand that there is no length restriction, I looked it up right away; thanks for making me aware of the max option. What I am asking about is whether there are performance implications to consider with using varchar(max), when you are hoping to get multiple records to fit on a data page.

|||

With varchar(max) most of the records will be fetched with a single read whereas for ntext, two reads will be required for every record irrespective of its size.

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:
>