Counts in Date Ranges

Hi,
I have table with the following structure
User Name, Name of Product, Date of Purchase
I want to find out the number of times a particular product was purchased by
anybody in the last 30, 60 and 90 days.
Is there any function in SQL that will give this type of distribution? What
will be the best way of writing the query?
Thanks in anticipation,
Nitin MNitim
SELECT COUNT(ProdunctName) FROM
TableName WHERE [Date of Purchase] >=DATEADD(DAY,-30,GETDATE()) AND
<GETDATE()
AND ProductName='Something'
"Nitin M" <nitin@.nowhere.com> wrote in message
news:%23Zbsk3yPFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have table with the following structure
> User Name, Name of Product, Date of Purchase
> I want to find out the number of times a particular product was purchased
by
> anybody in the last 30, 60 and 90 days.
> Is there any function in SQL that will give this type of distribution?
What
> will be the best way of writing the query?
> Thanks in anticipation,
> Nitin M
>|||Thanks Uri,
This is the solution to get the purchase count for a given time period.
However I was looking for a solution that gives me the purchase counts for
three time periods in one query. I am looking for a result set that looks
like
Product PurchaseInLast30Days PurchaseInLast60Days
PurchaseInLast90Days
----
--
A 10 13
31
B 0 5
5
and so on ...
I have a query that looks like... but it amounts to scanning the table 4
times. Is there a better way out?
select
P.ProductName,
Last30Days = (select count(P1.ProductName) from Products P1 where
P1.ProductName = P.ProductName and
datediff(day,P1.PurchaseDate,getutcdate()) <= 30 group by P1.ProductName),
Last60Days = (select count(P1.ProductName) from Products P1 where
P1.ProductName = P.ProductName and
datediff(day,P1.PurchaseDate,getutcdate()) <= 60 group by P1.ProductName),
Last90Days = (select count(P1.ProductName) from Products P1 where
P1.ProductName = P.ProductName and
datediff(day,P1.PurchaseDate,getutcdate()) <= 90 group by P1.ProductName),
from
Products P
group by
P.ProductName
Thanks,
Nitin M
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eJjWlBzPFHA.2876@.TK2MSFTNGP10.phx.gbl...
> Nitim
> SELECT COUNT(ProdunctName) FROM
> TableName WHERE [Date of Purchase] >=DATEADD(DAY,-30,GETDATE()) AND
> <GETDATE()
> AND ProductName='Something'
>
> "Nitin M" <nitin@.nowhere.com> wrote in message
> news:%23Zbsk3yPFHA.2932@.TK2MSFTNGP09.phx.gbl...
> by
> What
>|||I see what you mean
select
ProductName,
COUNT(CASE WHEN datediff(day,PurchaseDate,getutcdate()) <= 30 THEN 1 END)
Last30Days ,
COUNT(CASE WHEN datediff(day,PurchaseDate,getutcdate()) <=60 THEN 1 END)
Last60Days ,
COUNT(CASE WHEN datediff(day,PurchaseDate,getutcdate()) <= 90 THEN 1 END)
Last90Days
from
Products
group by
ProductName
"Nitin M" <nitin@.nowhere.com> wrote in message
news:OVtGoHzPFHA.532@.TK2MSFTNGP09.phx.gbl...
> Thanks Uri,
> This is the solution to get the purchase count for a given time period.
> However I was looking for a solution that gives me the purchase counts for
> three time periods in one query. I am looking for a result set that looks
> like
> Product PurchaseInLast30Days PurchaseInLast60Days
> PurchaseInLast90Days
> ----
--
> A 10 13
> 31
> B 0 5
> 5
> and so on ...
> I have a query that looks like... but it amounts to scanning the table 4
> times. Is there a better way out?
> select
> P.ProductName,
> Last30Days = (select count(P1.ProductName) from Products P1 where
> P1.ProductName = P.ProductName and
> datediff(day,P1.PurchaseDate,getutcdate()) <= 30 group by P1.ProductName),
> Last60Days = (select count(P1.ProductName) from Products P1 where
> P1.ProductName = P.ProductName and
> datediff(day,P1.PurchaseDate,getutcdate()) <= 60 group by P1.ProductName),
> Last90Days = (select count(P1.ProductName) from Products P1 where
> P1.ProductName = P.ProductName and
> datediff(day,P1.PurchaseDate,getutcdate()) <= 90 group by P1.ProductName),
> from
> Products P
> group by
> P.ProductName
> Thanks,
> Nitin M
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eJjWlBzPFHA.2876@.TK2MSFTNGP10.phx.gbl...
purchased
>|||Thanks a lot Uri ... I got it ... It helped me.
Thank You
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23dOdsRzPFHA.1096@.TK2MSFTNGP12.phx.gbl...
>I see what you mean
> select
> ProductName,
> COUNT(CASE WHEN datediff(day,PurchaseDate,getutcdate()) <= 30 THEN 1 END)
> Last30Days ,
> COUNT(CASE WHEN datediff(day,PurchaseDate,getutcdate()) <=60 THEN 1 END)
> Last60Days ,
> COUNT(CASE WHEN datediff(day,PurchaseDate,getutcdate()) <= 90 THEN 1 END)
> Last90Days
> from
> Products
> group by
> ProductName
> "Nitin M" <nitin@.nowhere.com> wrote in message
> news:OVtGoHzPFHA.532@.TK2MSFTNGP09.phx.gbl...
> --
> purchased
>

Counting Results in a field in SQL 2000

I've got a varchar field and I'm trying to count the whole field and obtain a total count for the number of entries in a particular field. Can someone please provide the syntax on how to display a count of all the results in a field?

Thanks

Quote:

Originally Posted by Alpenk

I've got a varchar field and I'm trying to count the whole field and obtain a total count for the number of entries in a particular field. Can someone please provide the syntax on how to display a count of all the results in a field?

Thanks

Please send an example of your table or tables

|||select count(column_name) from table_name

In this case you count all not null values in this column.
If you want to count distinct values in this colun do following:

Select count(distinct column_name) from table_name

Good Luck.

Counting No Of Words In a Column

Hi,

for some reason, i had to write a function to count the number of words in a particular column in a table. (pl find the attachment). i would like to know whether there is any other mechanism with which we can count the number of words in a particular column.

for example, if the column data is,'This Is A Test', the function, will return 4.
pl suggest any other efficient strategies to accomplish this

thanksIs this too simple?

DECLARE @.Text AS VarChar(100)
SET @.Text = 'This is a sentence'
PRINT 'Number of words: ' + CAST(LEN(@.Text) - LEN(REPLACE(@.Text, ' ', '')) +1 AS VarChar(3))|||BTW - if it isn't then it is vastly more efficient.|||Is this too simple?

DECLARE @.Text AS VarChar(100)
SET @.Text = 'This is a sentence'
PRINT 'Number of words: ' + CAST(LEN(@.Text) - LEN(REPLACE(@.Text, ' ', '')) +1 AS VarChar(3))

one more qn..

wat if the data contains blank space

e.g. : 'This Is A Sentence '|||Bol

Rtrim(ltrim())|||Bol

Rtrim(ltrim())

Thank U all for the comments...

:)|||A bigger issue would be if the text contains double spaces:
"This is a single line of text. This text contains two sentences separated by two space characters."|||A bigger issue would be if the text contains double spaces:
"This is a single line of text. This text contains two sentences separated by two space characters."
LEN(REPLACE(@.Text, ' ', ' ')) - LEN(REPLACE(REPLACE(@.Text, ' ', ' '), ' ', '')) +1 :)|||You're gonna ask about triple spaces now ain't cha?|||Yup. I am.
To make this robust, you need a loop to eliminate double spaces until none remain. And that means this should be a multi-step function rather than a simple formula.|||Does the OP live in Iceland?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56195&whichpage=1|||... you need a loop ...muthaf*$#%$n loops in a muthaf*$#%$n function?

next you're going to suggest using a cursor, aren't you

:)

counting child rows?

I've got a query where I have to find all messages for a particular topic,
and then I need a count of all child messages for each message. Here's what
I have so far:
ALTER PROCEDURE [dbo].[udForumTopicMessageByForumTopicID]
@.ForumTopicID int
AS
SELECT
A.ForumTopicMessageID AS "ForumTopicMessageID",
A.ForumTopicID AS "ForumTopicID",
A.ContactID AS "ContactID",
A.MessageTitle AS "MessageTitle",
A.MessageText AS "MessageText",
A.ApprovedInd AS "Approved",
A.ReviewedInd AS "ReviewedInd",
A.ParentMessageID AS "ParentMessageID",
A.OwnerCompany AS "ForumTopicMessageOwnerCompany",
A.CreateUser AS "ForumTopicMessageCreateUser",
A.UpdateUser AS "ForumTopicMessageUpdateUser",
A.CreateDate AS "ForumTopicMessageCreateDate",
A.UpdateDate AS "ForumTopicMessageUpdateDate",
'('+COUNT(B.ParentMessageID)+')' As "ChildResponseCount",
(T_Contact.Lastname + ', ' + T_Contact.Firstname) As "ContactName"
FROM [T_ForumTopicMessage] A
INNER JOIN [T_Contact] ON [T_Contact].ContactID = A.ContactID
INNER JOIN [T_ForumTopicMessage] B On B.ParentMessageID =
A.ForumTopicMessageID
WHERE T_ForumTopicMessage.ForumTopicID = @.ForumTopicID
GROUP BY B.ParentMessageID
SQL Server Management Studio says:
Msg 4104, Level 16, State 1, Procedure udForumTopicMessageByForumTopicID,
Line 6
The multi-part identifier "T_ForumTopicMessage.ForumTopicID" could not be
bound.
I'd like to get beyond this, just to find out if the join on itself will
even work.
Or maybe someone has a better way to do this?Take a look at this example:
http://milambda.blogspot.com/2005/0...or-monkeys.html
ML
http://milambda.blogspot.com/