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 |
|
ckuo@kahluadesigns.com
Yak Posting Veteran
58 Posts |
 Posted - 2005-03-18 : 16:07:27
|
Hi, I have a table likeID CartID TransID------------------------1 1 2 2 3 1 1004 3 5 16 17 1 200 I am given TransID = 200, so I find the CartID of this, which is 1, I then need to return all records of CartID = 1. Which I can do, but, i need to narrow it down to records from 200 and below, but only to when i run into a non-null TransID, so in this case, from TransID 200 - 100 where they share the same cartID. making sense? good becuase i'm not sure if i can make any! haha. In some cases however, there might not be a TransID before 200, so i would just return everything before it. I suppose I can do some filtering programmitcally in my app, but wondering if there was a way with a clever sql statment. Thanks. |
|
|
gpl
Posting Yak Master
195 Posts |
Posted - 2005-03-18 : 17:43:26
|
| I am sorry to say, this is a bad designIt looks like a flat file that you have massaged into a database.There is no concept of row ordering in a database, other than the particular index that you are using.You cannot rely on the rows being by default in the order that they were inserted in.You should add more identifying information, like the TransID being on every row.If you really need to know the order in which the items were selected within each TransID group, then store a timestamp on each row too.Does this help you ?Graham |
 |
|
|
ckuo@kahluadesigns.com
Yak Posting Veteran
58 Posts |
Posted - 2005-03-18 : 20:01:27
|
| Thanks. I know this is not a good design. I do have a Date Insertted field. How would you suggest I redesign this? What I do is,everytime a visitor comes to the site, I insert a record, there are other fields like Http Refer, where they came from, etc, but that does not have an effect on this query. So, I do know their CartID, which is unique to them. So, say they come in for this session, I insert a record for it, then say they actually buy something, then I insert another record but with the TransID. THe goal is to be able to trace back their steps on how they got to this site and ultimately completed a purchase. I can get all this info with a query but it is not a snapshot of a particulare TransID. For example, if they bought something, then I only want information between that TransID back to their last TransID. Thanks for yhour advance. |
|
|
|
Crito
Starting Member
40 Posts |
Posted - 2005-03-19 : 04:15:04
|
Something like this maybe:CREATE PROCEDURE dbo.usp_TraceTransID ( @TransID int )ASBEGINSELECT *FROM TableName WHERE CartID = (SELECT CartID FROM TableName WHERE TransID = @TransID) AND TransID BETWEEN (@TransID - 99) AND @TransID ORDER BY IDENDGO Crito Philippatoshttp://www.4crito.com |
|
|
|
Crito
Starting Member
40 Posts |
Posted - 2005-03-19 : 04:32:07
|
| Oh, you'll probably need to modify the subquery. I couldn't tell by the limited dataset you provided, but if TransID #'s repeat per CartID then it might look something like:WHERE CartID = (SELECT MAX(CartID) FROM ...or if TransID is always unique and there are multiple carts within a range then perhaps something like:WHERE CartID IN (SELECT CartID FROM ... |
|
|
|
ckuo@kahluadesigns.com
Yak Posting Veteran
58 Posts |
Posted - 2005-03-19 : 11:34:46
|
| AND TransID BETWEEN (@TransID - 99) AND @TransID Thanks for the suggestion. A few questions. Why 99? The current @TransID is set and known, but in terms of how far back their last @TransID is not known. Also, this might be their first purchase, so there could be no prior @TransID.Thanks |
|
|
|
Crito
Starting Member
40 Posts |
Posted - 2005-03-19 : 14:38:38
|
| Just seemed you were incrementing by 100, so I took your 100-200 example to be noninclusive on the 100 side. Again, I couldn't infer much from the limited dataset provided. In any case, shouldn't matter if there's no prior transid, between -99 and 0 works just fine too. Just remember BETWEEN is inclusive. |
|
|
|
|
|
|
|
|
Query Help