Tuesday, March 27, 2012
Cursor Issue - Order not correct
I am finding that this works for about 95% of the stores, but about 5% are getting fouled up, where the store's records are getting split in the sequencing, and so the store ends up with two zips ranked 1, two ranked 2, two ranked 3, etc.
In the DB structure, there is a constraint restricting one record per store (org_id) per zip code (postalcode).
Here's my code. Basically what I'm trying to have the cursor do is go through the table, ordered by org_id (store) asc, org_criteria_value (sales) desc, and rank the zip codes. When a new store is encountered, reset the counter to 1 and start ranking again. Do this until all the records are processed.
/*CREATE Sales_Table table */
CREATE TABLE [dbo].[Sales_Table] (
[count_id] [int] NULL ,
[org_id] [int] NULL ,
[postalcode] [varchar] (20) NULL,
[sales] [numeric](18,6) NULL ,
[sales_rank] [integer] NULL,
[org_criteria_input_date] [datetime] NULL
) ON [PRIMARY]
insert into Sales_Table
select 0 as count_id, omd.org_id, omd.postalcode, omd.org_criteria_value, 0 as cum_rank, org_criteria_input_date
from org_model_data omd
join org o on o.org_id = omd.org_id
where o.client_id = @.ClientID
and model_Criteria_id = 27
and org_criteria_value <> 0
order by omd.org_id asc, omd.org_criteria_value desc
-- DECLARE CURSOR for Sales_Table
declare SalesRankCursor CURSOR
SCROLL dynamic FOR
select org_id, sales, sales_rank
from Sales_Table
for update of sales_rank
-- CREATE LOOP TO UPDATE SALES RANK in Sales_Table with valid values
OPEN SalesRankCursor
while exists (Select * from Sales_Table where sales_rank = 0)
Begin
FETCH NEXT FROM SalesRankCursor
set @.StoreNext = @.StoreCurrent
set @.SalesRank = (@.SalesRank + 1)
update Sales_Table
set @.StoreCurrent = org_id
where current of SalesRankCursor
if @.StoreCurrent <> @.StoreNext
begin
set @.SalesRank = 1
end
update Sales_Table
set sales_rank = @.SalesRank
where current of SalesRankCursor
End
CLOSE SalesRankCursor
DEALLOCATE SalesRankCursor
Any ideas?You insert the data ordered, but since the order of the rows are irrelevant to SQL Server, you are not guaranteed to get the rows out in the same order. So, what you basically need is to have the order by in the cursor definition.|||That's what I thought, but how do you order within the cursor definition. When I try an 'order by' statement within the cursor definition, I get a message saying it can only be used in an 'read only' cursor, and my cursor needs to allow an update.|||What I've done for a solution is put a Clustered Index on my source table 'Test Table' on the fields org_id, and org_criteria_value. This appears to have resolved the issue.
I would have preferred to have solved the problem within the cursor, however, so if anyone has a suggestion I would appreciate it.
Thanks.|||What I've done for a solution is put a Clustered Index on my source table 'Test Table' on the fields org_id, and org_criteria_value. This appears to have resolved the issue.
You should be aware that just because it has a clustered index on it, you are not GUARANTEED that the results will be returned in the expected order.
I would have preferred to have solved the problem within the cursor, however, so if anyone has a suggestion I would appreciate it.
Thanks.
Maybe I missed something in the code, but how about something like:
DECLARE SalesRank CURSOR
READ_ONLY
FOR SELECT Store_ID
FROM Stores
ORDER BY Sales DESC
DECLARE @.Store_ID int, @.Counter int
SELECT @.Counter = 1
FETCH NEXT FROM SalesRank INTO @.Store_ID
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
UPDATE Stores
SET SalesRank = @.Counter
WHERE Store_ID = @.Store_ID
END
SELECT @.Counter = @.Counter + 1
FETCH NEXT FROM SalesRank INTO @.Store_ID
END
CLOSE SalesRank
DEALLOCATE SalesRank
This would not handle ties or other things, but it should work (with some modification).
Also, there is a RANK function in SQL 2005. That might be an option for you.
Regards,
hmscott|||HMScott,
Let me take a shot at that. I may have to play around with it because I can't have a tie... I'll have to figure out a tie breaker and work from there.
The Clustered IX appeared to solve my problem, but it wasn't the solution I wanted because of lack of certainty. Hopefully the approach you're recommending will work.
Thanks.
Thursday, March 22, 2012
Cursed with trailing whitespace or trailing 0's
I have a databound textbox that is used to store a decimal value.
If my sql table stores this column as a decimal(2,2), then all of the numbers entered into the field will automatically put decimal places in that I don't want. For example, 45 becomes 45.00... 34.5 becomes 34.50.
If I set the sql table to nchar(10) and the dataset to system.string (max length of -1), then the number looks the way I would like it, however after a datatable update I end up with trailing whitespace after the number - filling up the rest of the unused 10 characters. For example, "45" becomes "45 " (8 spaces afterwards).
Does anybody know how I can fix this? I would prefer to store the numbers in SQL as a string (nchar(10))... but I don't know how to get rid of that darned whitespace. I would like to remove it at the database level and not at the client level if at all possible.
Thanks!
Using char means that you want to have fixed length attributes. using Varchar would use only the space that is needed for storing the attribute. You have to keep in mind that the insert behaviour is determined by your inserting application. if the application do insert "45 " then you have to manuipulate the application ratehr than the database. But normally you should only switch to a variable character length field, e.g. in this case VARCHAR(10).HTH, Jens Suessmeyer.http://www.sqlserver2005.de
Saturday, February 25, 2012
Cube design question
I have a Fact table which stores details about each revision made on the document.
There is a one to many relationship between a document and the revisions
I need to display information based on the last revision made by a particular UserType.
How do I select records from the fact table based on a particular UserType and the maximum revision number for that usertype?
Hi Reena,
Are you using AS 2000 or AS 2005?
So you have RevisionFact table and also Dim_Dt, Dim_UserType, Dim_Users, Dim_Doc etc. and you store every revisions in fact table along with UserTypeID, Date_ID, Doc_ID, and User_ID.
It would be easy if you tell what measures you store in fact table. You can use "Count" Aggregate Function in measure and query using UserType , Date and measure count on the field you store for each revisions in fact table.
-Ashok
|||I am using AS 2005
I need to display the count of documents that were written within the target time as well as the count of documents that were not written within the target time set for each user.
The user list has to be displayed along the rows and the count of documents along the columns ( 2 columns)
I have measures that would display time taken to write a document and target time for the document.
Using COUNT function I can get the count of documents written by a user.
But how do I break that count up into two separate columns displaying number of documents written within target time and number of documents written outside target time - for each user?
|||There are a few ways you could do this, but here's one suggestion. Its a very simple pattern which csn be used again and again for similar problems.
You have a table or view with the time taken and target time in as columns.
Add another column (this is easier if you have a view) and define it as:
case when TimeTaken>TargetTime then 0 else 1 end as TargetMetId
create a lookup table as TargetMet with values
0,"Target Not Met"
1,"Target Met"
You can then link the 2 together and create a nice TargetMet Dimension. This will give you all of the counts you require.
You can expand this by changing the case statment, and building a lookup table like this
1,"Finished Same Day"
2,"Finished Early"
3,"On Time"
4,"A bit late"
5,"Do they still work here?"
etc
|||Thanks for the reply.