Need help with query syntax

[minli98]

Hi,

I need help writing a query statement. Say I have a sales table with two columns: SalesDate and ProductID. I want to calculate a variable x for each ProductID using the following function:

x(ProductId) = sum_for_all_sales_of_ProductId{
if ((datediff(d,SalesDate,getDate()) <= 100)
datediff(d,SalesDate,getDate)
else
0
}

In words, for each product, I want to calculate sum of the day difference between the sales dates and today. The part I don't know how to do is that when the datediff is greater than 100, I want to use zero rather than the day difference.

This is my attempt so far:

select ProductId, 
       dayIndex = sum(
           if (datediff(d,salesDate,getDate()) <= 100)
               datediff(d,salesDate,getDate())
           else
               0
           )
from sales
group by ProductId

Thank you in advance for your help.

Regards,
Min

 

[gmmastros]

select ProductId, 
       dayIndex = sum(
           Case When datediff(d,salesDate,getDate()) <= 100
                Then datediff(d,salesDate,getDate())
                else 0
                End
           )
from sales
group by ProductId

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom