Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tugboatfarrow
Starting Member
2 Posts |
 Posted - 2010-07-29 : 00:12:12
|
| Hi AllI have a table named productView in the form of:id, productid, loginref, viewDateI run a query to return all of the total new product views for a given period as specified by a user, something like:SELECT COUNT(DISTINCT PV.productId) AS totalNewDistinctViewsFROM productView PVWHERE PV.viewDate >= '28 june 2010' AND PV.viewDate <= '20 july 2010'AND PV.loginref=123AND PV.productid NOT IN ( SELECT productid from productView WHERE loginref=123 AND viewDate < '28 june 2010')So if that product had been viewed by the login in question prior to the reporting period then do not count it.Now this was fine but I have been told we need to modify the query so that we do not count a product twice if that product has been viewed within a month of the view in question. This is where it becomes too tricky for my small brain as I will have to somehow do this with a self-join(?) or perhaps a cursor(?) as I can't just use the NOT IN clause as I need to check the date of each view record in turn and make sure there is no corresponding view (of the same productid from the same loginref) within a month of this date. I have tried a number of joins but can't seem to return the correct count on some test data. Can anyone point me in the right direction? Will buy you some beers... |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-07-29 : 01:35:34
|
| Can you post some sample data & the expected o/p?Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless. PBUH |
 |
|
|
tugboatfarrow
Starting Member
2 Posts |
Posted - 2010-07-30 : 01:11:11
|
| id, productid, loginref, viewDate1,224,678,'22 july 2010'2,224,678,'24 july 2010'3,224,678,'26 july 2010' 4,224,678,'19 january 2010'5,119,678,'13 july 2010'And running a report from the 1 July to the 31 July would return a count of 2 for the user with loginref of 678. The count would be made up of the 1 view of product 119 + 1 view of product 224(it would only count 1 of these as for the all three views within the report period are within 1 month. Now if we had another row in the table:id, productid, loginref, viewDate74,224,678,'29 june 2010'then none of the views of product 224 would be counted as all three views within the report period are within one month of this view...Hope this makes sense... |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-07-30 : 03:24:08
|
| So you are saying that the benchmark should be always one month & even though there is a view for a given date range those views should be ignored if there is any other view within one month of the given date range selected.So just to make it more clear what happens if there is a view on say 2-June-2010 & also on 31-may-2010 & the selected date range is between 1-July-2010 and 31-July-2010 & there are 3 views in those selected date ranges??Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless. PBUH |
|
|
|
|
|
|
|
|
Filtering table results