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
>