January 31, 2008 at 8:15 am
Hi,
I am asked to make a report where the user can view the number of items they have bought daily, in the last six months in a specific price range.
Example:
They want to see how many things were ordered by customer id '12' inthe past six month daily and in the price ranges (1-500, 501-1000, 1001-1500, 1501-2000)
The table structure is following
CID (decimal)
Amount (decimal)
Item ID (decimal)
processdate (datetime)
I did write the query for one price range but dont know how it can be done for all of them, so far ive done this but copy pasting the data is taking me ages because there are days that they have not ordered anything and then i have to manually feed in the data in excel for the days they did order something..
select count (*) as [1001to1500], processdate as [Date]
from RPTView
where CID= '12'
AND processdate between '08/01/07' and '02/01/08'
and amount between 2001.00 and 2500.00
Group by processdate
order by processdate asc
and this gives me the number of itesm broken down into dates for the price range 1001-1500, please let me know what i can do to get the results for all ranges together.. someone recommeded to use the excel pivot tables so thats what im working on right now...
Thanks for any help
January 31, 2008 at 8:33 am
Does this help?
Select ProcessDate
,[1to500] = count(case when amount between 1 and 500 then amount else null end)
,[501to1000] = count(case when amount between 501 and 1000 then amount else null end)
...
From
RPTView
where CID= '12'
AND processdate between '08/01/07' and '02/01/08'
Group by processdate
order by processdate asc
This is off the top of my head with no testing so...
If you want customer in there as well, then get rid of the customer id filter, and add it into the select and group by
Hope this helps.
January 31, 2008 at 10:07 am
This worked!!.. Thank you so much, saved me a couple of hours, I am new to SQL so learned something very helpful today.. thanks alot
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply