OK, I am trying to define a query where I can choose those customers that
had an existing account more than 30 days ago that did not have a specific
class of product more than 30 days ago and then did have that class of
products within the last 30 days. Does that make sense? Basically, they had
an account in May and they didn't have product A in their account. Then in
June they bought product A. Below is my query and a little more definition
on the tables.

select i.dtinsertdate, cp.iownerid, cp.chproductnumber pn1,
cp.dtpurchasedate pd1, cp2.chproductnumber pn2, cp2.dtpurchasedate pd2
from customerproduct cp left outer join customerproduct cp2
on cp.iownerid=cp2.iownerid
inner join individual i on cp.iownerid=i.iindividualid
where cp.chproductnumber like 'pafgpcmv%'
and datediff(day,i.dtinsertdate, getdate())>=30
and cp.chProductNumber NOT LIKE '%UUU%'
and cp.vchSerialNumber NOT LIKE '%INVALID'
and cp2.chproductnumber not like 'pafgpcmv%'
and datediff(day,cp.dtpurchasedate,getdate())<30
ORDER BY pd1 asc

Where individual has the personal info and includes the date the account was
created (dtinsertdate), Customerproduct has the products for each customer
account and the date that product was purchased (dtpurchasedate).

thanks for any help you can give me.

Willie

http://www.aspfaq.com/5006





Show quote Hide quote

OK, the post with everything was too large, so here are the create table
statements:

CREATE TABLE [dbo].[CustomerProduct] (
[iProductId] [int] NOT NULL ,
[iSiteId] [int] NOT NULL ,
[iOwnerId] [int] NOT NULL ,
[chLanguageCode] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[iContactId] [int] NULL ,
[chProductNumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[vchSerialNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[flQuantity] [OnyxFloat] NULL ,
[dtPurchaseDate] [datetime] NULL ,
[iTrackingId] [int] NULL ,
[iSourceId] [int] NULL ,
[iStatusId] [int] NULL ,
[iAccessCode] [int] NOT NULL ,
[vchUser1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser3] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser4] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser5] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser6] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser7] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser8] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser9] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser10] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[chInsertBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dtInsertDate] [datetime] NOT NULL ,
[chUpdateBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dtUpdateDate] [datetime] NOT NULL ,
[tiRecordStatus] [tinyint] NOT NULL ,
[dtModifiedDate] [smalldatetime] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Individual] (
[iIndividualId] [int] NOT NULL ,
[iSiteId] [int] NOT NULL ,
[chLanguageCode] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[vchAssignedId] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[vchSalutation] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchFirstName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[vchMiddleName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchLastName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[vchSuffix] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchAddress1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchAddress2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchAddress3] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchCity] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[chRegionCode] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[chCountryCode] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[vchPostCode] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[vchPhoneNumber] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[vchEmailAddress] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[vchURL] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[chGender] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[iUserTypeId] [int] NULL ,
[iUserSubTypeId] [int] NULL ,
[iCompanyId] [int] NOT NULL ,
[vchCompanyName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[chTitleCode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[vchTitleDesc] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[chDepartmentCode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchDepartmentDesc] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[iPhoneTypeId] [int] NULL ,
[iAddressTypeId] [int] NULL ,
[iSourceId] [int] NULL ,
[iStatusId] [int] NULL ,
[bValidAddress] [tinyint] NOT NULL ,
[iAccessCode] [int] NOT NULL ,
[bPrivate] [tinyint] NOT NULL ,
[vchUser1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser3] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser4] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser5] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser6] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser7] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser8] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser9] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser10] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[chInsertBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dtInsertDate] [datetime] NOT NULL ,
[chUpdateBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dtUpdateDate] [datetime] NOT NULL ,
[tiRecordStatus] [tinyint] NOT NULL ,
[dtModifiedDate] [smalldatetime] NULL
) ON [PRIMARY]

I have the data as well, but it is rather large and it won't let me post it
(and it is just a subset). ANy thoughts on that aspect?

Willie
Show quoteHide quote

Great, what is an OnyxFloat?  I don't seem to have that datatype on my
system.  Is it just a FLOAT?  If so, why not use a FLOAT?  Or better yet, an
INT, because I don't know why you would want an approximate value to
represent the data in a column called Quantity.

Can you provide enough INSERT statements (and you only have to include the
important columns, e.g. you can leave out the nullable ones) so that we can
understand which rows will meet your requirements and which should NOT be
included in the results.

If we have no idea what the data looks like, and which ones you want
included and which ones you don't, we can only struggle to provide you a
rough idea of a solution.  This always leads to 20 follow-up questions, "but
what if I have a row with this data" and so on and so on, because the query
based on guesses and no real knowledge of the data will rarely be correct
the first time.

And FWIW I feel sorry for the people who have to write queries against these
tables.  I will never understand why people think it's a good thing to
prefix the name of an element with the datatype used to store the element.
Ugh.  I deal with one such system and as if the naming convention is not bad
enough, it's not consistent.  In *most* places they use these silly
prefixes; in a few places they don't, and it makes for very interesting
head-scratching when doing joins (e.g. ON t1.iElementID = t2.ElementID).
Ugh.  Again.

Yes, I agree on both the datatypes and the field naming conventions. It's a
mess of pre-written CRM along with custom code and cobbled together
additional tables. Some things we can change and some we can't, it's lovely.
Anyway, I will pull together some data in the morning. Now, what I was
wondering is if you need data that actually fulfills the characteristics I
am looking for, or if just data with the proper fields and format would be
enough? Is this OK, am I missing anything else, is my goal clear? And yes,
Float is equifvalent to OnyxFloat.

Willie

Show quoteHide quote

It is essential that you provide a wide enough sampling of data that some
will meet your criteria, some will not, and that you can easily identify
which is which.  As I explained before, this is the only way we can know
that a query we produce will give you the correct results.

OK, here is the data pared down with people that fall into all of the
categories. Thx for your help and patience with this.

Willie

[attached file: Data070705.xls]

Let's see if this is helpfull to you

First a create a view which basically selects all the customers whom bought
any product within the first 30 days from the day they open his/her account

Create View CustomersBought30Less
As
SELECT Individual.iIndividualId, CustomerProduct.iProductId, DateDiff(day,
Individual.dtInsertDate,CustomerProduct.dtPurchaseDate) AS DifDays
FROM Individual INNER JOIN CustomerProduct
ON Individual.iIndividualId = CustomerProduct.iOwnerId
WHERE
(((DateDiff(day,Individual.dtInsertDate,CustomerProduct.dtPurchaseDate))<=30))

Then I made this second view o since this is the final resultset you could
create a SP

Create View CustomersNotBought30Less
As
SELECT Individual.iIndividualId, Individual.vchFirstName,
Individual.vchLastName, Individual.dtInsertDate,
DateDiff(day,Individual.dtInsertDate,GetDate()) AS DifDays,
CustomerProduct.chProductNumber, CustomerProduct.dtPurchaseDate
FROM Individual INNER JOIN CustomerProduct
    ON Individual.iIndividualId = CustomerProduct.iOwnerId
WHERE (((Individual.iIndividualId) Not In (Select iIndividualId From
CustomersBought30Less)) AND
((DateDiff(day,Individual.dtInsertDate,GetDate()))>30))


So, in this final query you could and any extra criteria you may need.



Show quoteHide quote

Hmm... the logic isn't quite what I was looking for, but I'll play around
with the idea of views.
What I need is a view that has:
1) Users that have an account created > 30 days ago
    a) Those accounts that did not have pcmover > 30 days ago

Then I can select those users that added pcmover within the last 30 days.

Show quoteHide quote

Pros/Cons using sqltransaction class vs stored procedure
Can I avoid using a cursor
DECLARE CURSOR ON EXEC... (Sybase does it, so why cant SQL Server?
Aaron, Itzik - about the MIN Function
IDENTITY_INSERT