Hi Folks,

I am assigned a problem which i am not sure how to write it in SQL SEVER (version 5/8) yet. Here is the simple query and its output:

Select location, date_time, Item_sold
From Product

Location        Date_time                   Item_sold
VA            12/10/2010 1:30:00 PM           Candy
VA            12/10/2010 3:30:00 PM           Chips
VA            12/13/2010 12:50:00 AM          Wine
DC            12/13/2010 8:00:00 AM           Gum
DC            12/13/2010 12:30:00 PM          Bags
DC            12/13/2010 1:16:00 PM           Cheese
DC            12/13/2010 12:00:00 AM          Hotdog
NJ            12/15/2010 12:00:00 AM          Coffee
NJ            12/15/2010 1:15:00 PM           Beers
NJ            12/15/2010 3:45:00 AM           Cream

Here is my desired output, which I guess a while/for loop or a pivot function could do the job but my experience is not there yet. Basically, I need to count the number of item sold from the Item_sold column incrementally (base line date starts from 12/8 to 12/9, 12/8 to 12/10, 12/8 to 12/11, 12/8 to 12/12...)

I was wondering if anyone could edcuate and solve this problem. Many thanks in advance, Joe

Location 12/8 |12/8-12/9 |12/8-12/10 |12/8 - 12/11 |12/8 - 12/12 |12/8 - 12/13
VA       0         0            2         0           0            3
DC       0         0            0         0           0            4