|
Hi, thanks for looking
I have a numeric column that i would like to reorder
I mean, there are gaps between the numbers, for example: 1,3,4,5,8,9
I would like it to be: 1,2,3,4,5,6
This column is the primarty key, i obiously have many rows
In other words, i want this column to have the value corresponsing to the row number
How could i accomplish this?
Thanks in advance
Alexei Rodriguez
|
|
|
|
|
Well, after thinking or a little bit, i found a solution using row_number() and a temp table
But i remember once i saw another way, it was a single query, but i dont remember where i saw it
If you know any other way, let meknow
Thanks
Alexei Rodriguez
|
|
|
|
|
I hope this query is which you saw:
select <br />
(select count(t1.columnname)+1 from mytable as t1 where t1.columname < mytable.columnname) as RowNumber<br />
from mytable
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Thanks for the reply
I was talking about a single query to update the numeric column according to a sort
Im trying to eliminate all the gaps on my primary key, You know about the little problem with autoincremented columns
Alexei Rodriguez
|
|
|
|
|
Alexei, auto incrementented numbers should NEVER be "filled". Read the link from Mika. You are heading down a very nasty path....
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
When using autoincrement for primary key (surrogate key), the values should have no meaning. For this reason the gaps shouldn't be any problem in a real world situation. See: http://en.wikipedia.org/wiki/Surrogate_key[^].
The operation you're thinking of, should include:
- copying the actual data to for example temp table
- re-seeding the identity
- inserting the data back
However, if you have foreign keys referencing to this table, this sequence is much more complicated.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi all,
I am using SQL Server 2000 in my organization. We have several databases. I want to restrict a set of users from accessing other databases using Enterprise Manager. In other words, these users should be able to access only their database where they have been created as users through Enterprise Manager.
Now I have read that creating Database specific roles help to restrict access. But unfortunately the role permissions cannot be spanned across databases. Therefore, are any SQL MVPs or intelligent readers out there who can provide me an answer. Many thanx in advance.
Regards
vinodi
|
|
|
|
|
Creating a role in a database helps you to restrict access for objects in database. If you don't want that a user has access to a database at all, you'll manage database users.
First create a login and then add that login as a user to those databases you want. See:
- sp_addlogin[^]
- sp_adduser[^]
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
I have a table that looks like this:
11
28
18
.
.
.
i'm trying to roll up the values so it would take the value and add it to the previous sum of all the rows above it
11
39
57
.
.
.
I am having the hardest time, any ideas?
Einstein argued that there must be simplified explanations of nature, because God is not capricious or arbitrary. No such faith comforts the software engineer.
-Fred Brooks
|
|
|
|
|
ahhh stupid me, forgot about fetch
DECLARE SalesYearCursor CURSOR FOR
SELECT * FROM #tmpSalesYear
OPEN SalesYearCursor
FETCH NEXT FROM SalesYearCursor
INTO @Percent,@xxxxx,@xxxxx,@xxx
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Current = @percent + @previous
SET @previous = @Current
UPDATE #tmpSales SET Roll = @Current
WHERE Percentage = @percent
AND SalesYear = @year
FETCH NEXT FROM SalesYearCursor
INTO @Percent,@xxxxx,@xxxxxx,@xxxxxx
END
CLOSE SalesYearCursor
DEALLOCATE SalesYearCursor
EDIT: removed names that identify business 
Einstein argued that there must be simplified explanations of nature, because God is not capricious or arbitrary. No such faith comforts the software engineer.
-Fred Brooks
|
|
|
|
|
Not sure, but sounds like ROLLUP / GROUPING operations. Have a look at this, it might help you: GROUPING (Transact-SQL)[^]
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
What I need to do seems really easy, but I don't know how to do it and haven't been able to find the answer.  I have a SQL reporting services report that takes a date parameter. I want to create a subscription to the report that will run early each morning and show the data for the day prior. Nothing fancy, just a Now - 1. Only Now - 1 doesn't work. Does anyone know if/how this can be done?
Mike Devenney
|
|
|
|
|
Not sure about SSRS, but in SQL you would say GETDATE()-1. If both of those don't work, what's the error message (or any other problem description)?
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
I'm familiar with the SQL command to do this, I wanted to know if there is a way to specify a dynamic parameter in an SSRS report. GETDATE()-1 would work perfectly, but the question is how to set that as the parameter?
Mike Devenney
|
|
|
|
|
Where are you launching the report from? C#? Could you post the code that's causing the problem.
I must admit that I haven't used SSRS very much, only client side invocation and from IIS so this may be beyond my knowledge...
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
SSRS reports can be scheduled for delivery (called a subscription) through the web interface. When setting up a subscription you can specify the value you want passed to the report when it runs. I want to pass GETDATE()-1 but don't know how.
Mike Devenney
|
|
|
|
|
|
Please don't apologize! I've been banging my head against the wall trying to find a way to do this that didn't involved modifying the underlying stored procedure, which I now think I'll end up doing. I've asked DBA's, developers, project managers and anyone else who would continue to listen to the question past the words SQL Reporting Services but no one seems to know. Well, to be more accurate most of them knew, but they didn't have the answer I was looking for. 
Thanks for your time, I'll hit the links you posted and see if someone out there is going to save me from some relatively minor stored proc updates. Now that I think about it I've expended more effort trying to find the easy answer than I would have if I'd just made the update and pushed it to production. *sigh* I guess the adventure is the journey, right?
Mike Devenney
|
|
|
|
|
Mike Devenney wrote: I guess the adventure is the journey, right?
You're absolutely right about that! But then again, don't we all know the answer already (check the link if not familiar): 42[^]. However, the ultimate question seems kind of... forgotten
Anyhow, I really hope you get your original question solved!
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
You can use the following expression: =DateAdd("d",-1,Today())
|
|
|
|
|
For parameters StartDate and EndDate build two new parameters StartDateDefault and EndDateDefault. Move these two 'Default' parameters up the parameter list so they are calculated before StartDate and EndDate.
Point StartDate and EndDate to StartDateDefault and EndDateDefault respectively so that default values are picked up eg. by StartDate from StartDateDefault.
For the StartDateDefault and EndDateDefault, make the 'available values' come from a data set using Sql such as...
select dateadd(day, -1, datediff(d,0,getdate())) as Code, 'Yesterday' as Description
union
select dateadd(day, -0, datediff(d,0,getdate())) as Code, 'Today' as Description
Set StartDateDefault and EndDateDefault to be hidden. Also give these two parameters default values themselves if required, which will be dates matching those in the Sql query data set, use non-queried default values to provide a date in the same format as the Sql query eg. =DateAdd("d",-1,today())
Now, when the report initially runs, StartDate gets its value from StartDateDefault, which has a default start date of =DateAdd("d",-1,today()) and when you go to create a subscription, the DefaultStartDate parameter is available to change, and the values you can select come from the Sql query/data set created earlier.
|
|
|
|
|
A simpler solution is to add a hidden boolean parameter to the report, called for example 'IsSubscription' with a default value of false and add the folowing expression to the StartDate and EndDate default values:
=IIf(Parameters!IsSubscription.Value, DateAdd("d",-1,Today()), Today())
When running the report normally, the IsSubscription parameter is defaulted to false and the default date is today.
When creating the subscription, the IsSubscription parameter must is set to true, thus setting the default value to today-1 = yesterday.
This solution can be aplied to do all kinds of changes based on the IsSubscription parameter value and expresions for the rest of the paremeters based on its value.
The IsSubscription parameter must be defined before any other parameter that uses its value (moved up in the parameter list).
|
|
|
|
|
I have used the code snippet above and it works great. However, for my purposes I would like to revert to using a user entered date, if it is not being run by subscription.
Any suggestions?
|
|
|
|
|
Wow, it's been almost 3 years since this thread was started and the answer is still helpfull 
|
|
|
|
|
It's been a few years since I have done cursors and my head is starting to hurt due to changing user requirements. We are getting a file with three fields, SKU, pkg, and owner. I want to look up in the parts master table if there is a record where these three items match.
What I'm really getting confused is what to put in my logic to look at all three fields. First they just wanted to see if the sku existed. Okay, that was easy enough. But since this is really a composite key my brain just can not wrap itself around the solution. There are a few lines still in the code when I was dealing with only the SKU.
DECLARE partCursor CURSOR
FOR
SELECT
count(sku)
FROM
' + @svr + '.' + @dbname + '.dbo.pm_f
WHERE
sku IN (
SELECT
sku, pkg, owner
FROM
WMS_InboundOrder_D
WHERE
source_Id = ''' + @source_id + '''
AND application = ''' + @application + '''
AND trans_type = ''' + @trans_type + '''
)
OPEN partCursor
DECLARE @sku VARCHAR(20)
DECLARE @pkg VARCHAR(6)
DECLARE @owner VARCHAR(20)
DECLARE @partErrors VARCHAR(1000)
DECLARE @partFlag bit
SET @partErrors = ''''
FETCH NEXT FROM partCursor INTO @sku, @pkg, @owner
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
WHAT TO PUT HERE????
BEGIN
SET @partErrors = @partErrors + '' SKU: '' + @sku + '' SKU does not exist in master part table! | ''
END
FETCH NEXT FROM partCursor INTO @sku, @pkg, @owner
END
SELECT CASE WHEN @partFlag = 0 THEN ''FALSE'' ELSE ''TRUE'' END as Result, @partErrors as Errors
CLOSE partCursor
DEALLOCATE partCursor
Blog link to be reinstated at a later date.
modified on Friday, November 14, 2008 12:48 PM
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
