Hi. I am trying to figure out the code for sorting a manual (non-identity) number column in my table. the purpose is to show the user's pictures in perfect order (1,2,3,4,5,6...).
The Jist of my problem... When a user first inserts six pictures, he gets:
|1| |2| |3| |4| |5| |6|
All is good. But, say he deletes picture |3|. Now the list order looks like this:
|1| |2| <- |3| is removed |4| |5| |6|
And, then he inserts two more pictures, now he his this:
|1| |2|
|4| |5| |6| |7| <- |7| & |8| are added |8|
What i want to acheive is a "reshuffling" of the number order every time a picture is removed. So, when |3| is removed, |4| becomes |3|, |5| becomes |4| and so on. There should never be a gap in the order.
I am new to stored procedures, and have been trying to figure this out. Below is my guesswork:
BEGIN SELECT @photo_date = CONVERT(DATETIME,convert(char(26), getdate(), 109)) END
BEGIN SET @photo_number = 1 SELECT
@photo_number = ( SELECT COUNT(*) FROM dbo.PersonalPhotos b WHERE a.photo_date < b.photo_date ) FROM dbo.PersonalPhotos a ORDER BY a.photo_date END
BEGIN
My thinking is that it would be a safe bet to use the "photo_date" column as a litmus for my "photo_number" column (ie, the most recent record inserted by the user will always be at a later date than the previously inserted record). So:
I have a transformation where final result set give me 25 rows of data. Now before I put into destination table, I need to add another column which will show how many total records we have. Like.
My dataset:
A Â 20 abc B 24 mnp c 44 apq
Now I need to add another column within my transformation before I store the result set to destination like this:
A 20 abc 3 b 24 mnp 3 c 44 apq 3
Here. new column gives count of total rows in our dataset which was 3.
How can I achieve this? Can I use derive column to this?
I am inserting some data from a temp to an existing table. The existing table has a primary key that is a number that just appears to be number 1-460 (there are 460 rows in the table). The data type for the primary key is int.
When I insert from my temp table to the existing I want to be able to add the next number in but am not sure how I can set that up?? I have pasted my code below. I am inserting into Lab_test_add_conf and the primary key value is lab_test_conf_id. In my select statement where I am getting the values to input I just put a number for now but that isn't going to work. You can ignore the rest of the numbers I have inserted and commas with no values I actually need to go add those to the temp table and just set their default values but I just first wanted to determine how I can create an autonumber. This is a third party software so I don't want to change the actual datatype of the existing table.
Insert Into Lab_Test_Add_Conf (lab_test_conf_id,system_id,labtest_key,value_code, value_description,value_code_system,value_type,units,sequence_number,table_name, field_name,created_by,create_timestamp,modified_by,modify_timestamp,row_timestamp)
select '460','2250',labtestkey,valuecode,value_description,'L',value_type,units, sequence_number,table_name,field_name,'58',,'58',, from #TempLabTestConfigImport
Database in:SqlServer builtin interface with VS05 Using:ASP.NET VISUAL STUDIO 2005 I want to create a unique ID that identifies a row. Id:STU+(SeqNumber) for Student and Id:EMP+(SeqNumber) for Employee [Question]:But how can i create sequence number and store it for use like that of ORACLE
Current Idea:Based on registration info if type is student generate STU+seq num and insert to table along with username and other details and if employee like wise. So a procedure will receive this from user and append that with seqnumber and stores that in the table
I have a table in which a non-primary key column has a unique index on it. If I am inserting a record into this table with a duplicate column value for the indexed column, then what will be the error number of the error in above scenario? OR How could I find this out?
I have a stored procedure that supplies rows for a front-end DataGrid that allows custom paging. The stored procedure must return the requested "page" of rows as identified by a sproc argument. Currently, I'm loading the the query's result set into a temporary table that has an identity column as primary key. I then run a second query against the temp table that uses the identity column value to strip out the requested "page" of rows and return them to the front-end DataGrid.
I'd like to eliminate the temporary table. To do so I would need to create the equivalent of an identity column in the query's sorted results and reference this value in the WHERE clause in order to return only the requested rows.
Does anyone know of a way to generate a sequential number (starting at 1) within a query (and after the rows have been sorted by the ORDER BY)? I don't think this can be done but I put it out for those who may know better.
Thanks for your help. If you know this is impossible, that would be helpful feedback as well.
Hello All,Is there a way to run sql select statements with column numbers inplace of column names in SQLServer.Current SQL ==> select AddressId,Name,City from AddressIs this possible ==> select 1,2,5 from AddressThanks in Advance,-Sandeep
Can anyone help me figure out how to set up a new table using SSIS with a record field that will generate a unique number? The record number is also the primary key in the table. I did this about a year ago with a bit of help, but once you get the language right and initiate the creation, the text for defining the table is no longer available. I have seen a lot of references for MySQL and MS SQL 2000 and an AUTO_Generate or a UNIQUE ID. None of this works within MS SQL 2005 in SSIS.
I can't map other sql servers without creating alias with proper port number on client network utility. Other users using same version of the client tools, MMC, SQL DMF etc. I need to map 70 sql servers on using my client tools. Any help is appreciated.
We are building an application with a database that contains Jobs. These Jobs have properties like Name, Code etc. and some custom properties, definable by the application admin. For bulk import of Jobs, we want to allow the import of an Excel sheet with the columns Name, Code and a variable amount of columns. If the header names of these columns in the Excel sheet match the name of a custom property in the system we want to add the value of that cell into the database as property value.
In our Data Flow of our Import Package in SSIS we added an Excel Source that points to a test excel sheet with the Name and Code columns and €“ for this example - 3 custom property columns (Area, Department, Job Family). When we configure the Excel Source in the Excel Source Editor, we have the option to select the Columns from the Available External Columns table. But here lays the problem, we do not know at design time, what custom property columns to expect. We DO expect the Name and Code columns, but the rest is uncertain at design-time.
That raises the question: Is there some way to select all of any incoming columns (something like a SELECT * in T-SQL)? This looks like a big problem since it would mean that the .DTSX XML that is being generated at design-time would need to be updated at run-time to reflect the variability of the columns that might be encountered while reading the excel sheet.
Then, we thought, we could add a Script Component to our data flow that passes some kind of DataSet (or DataReader) in which we can walk through the columns ourselves? But then still, we miss the option to include ANY of the columns while reading an Excel sheet (or any other datasource by the looks of it)
We are aware of the option of optional columns in combination with the RaggedRight option, but it seems that we would have to put all of the columns of a row in just one column and then extract all the columns later with Derived Columns. But then, since the source import file is being prepared by an application admin, we want don€™t want to burden him with this horrendous task of putting everything in one column.
We would like to have some way of iterating through all the columns, either in a Script Component or maybe with a Pivot/Unpivot mechanism.
Does anyone have any suggestions? Are there other options we should have considered?
I need to create a measure that tells me how many customer's placed an order in the previous 91 days of any given date, not necessarily today.Â
So, if you imagine a PivotTable which has a series of dates down the left hand side and 'totalNoCustomersLast60Days' as the only column, I need each day to tell me how many customers ordered in the x number of days.
For example if we say 60 days my table would look like the following:
Each row in this table would be telling me that "x number of customers placed orders within the period 60 days prior to,and including, the date on the left".
I have tried numerous ways of doing this and despite thinking it should be incredible simple it eludes me.
I am trying to calculate how much revenue we may get, based on potential new business opportunities. The core fields we have are
Total Contract Value ($ or £)Duration of contract (months)Revenue start dateVarious information about the new business - ID, Title, Customer etc.
We can easily calculate the revenue per month with "Total Contract value divide by duration".
However what I would really like to do is be able to know how much revenue we will be getting each month.
To do this I was thinking we should probably create a new row for each month entry, with the mm-yyyy being the only difference for each row. But how to create the appropriate months and the correct amount of rows.
Is there a way for me to create a column in SQL? What Im looking to do is create a view with columns from a specific table. In addition to this I would like to append a column to the view which would contain data based on the data from a pre-existing column. Table AColumn 1Column 2Column 3 View AColumn 1Column 2Column 3Column 4 Now here is the Row structureColumn 1 Column 2 Column 3 Column 4Test Test A ONTest Test B OFF So if Column 3 contains "A" as its field data than Column 4 will contain "ON" and if Column 3 contains "B" than Column 4 will contain "OFF" Is this possible? TIA, Stue.
Hello,How should I create a column to save data with the folowing format 10.10 or 10.20 or 10.30 or 150.30 or 10 or 150.It is basically process step in a diagram flow.I tried with decimal but with 10.10 , it removes automatically the 0.Thanks
A want to user a procedure to decrease a number in a col. by 1 each day.
I need some type of operation
then a job has to run the procedure to decrease a number once a day.
I don't know how to go about the operation? I am going to the SQL Query Anylizer to help me run the script to test it. I have in a col. of 201 numbers, and I will subtract 1 from each day from all recorders. I guess you could call this a count down or timer. Does this code work for me?
I'm using SQL Server 2008 to solve the following exercise.
I have three Tables: Table CUSTOMER with columns: CustomerID,Company,ContactName,Phone Table ORDER with columns: OrderID,OrderDate,ShippedDate,ShipperID,Freight,CustomerID Table Shipper with columns:ShipperID,Company,Phone
I need to Create a view listing the shipper and the number of unshipped orders. I have been trying to use joins or possibly subqueries, but I'm not getting a correct result for a ShipperID with NO unshipped orders.My view should looks like: ShipperID, Company, Phone, UnshippedOrders
I have created new columns fields in a table using EManager. However, I am wondering Which command do we use to create columns through the analyser??? Is it the same like the "Insert into"?
and what I would like is to update this table with the differences of values in the x column (x[2]-x[1]) where the first difference would just be defined as 0.
result table would look like this:
id, x, dx 1 9.5 0 (where dx[1] defined to be 0) 2 10 0.5 (where dx[2] = 10-9.5 = 0.5) 3 11 1.0 (where dx[3]= 11-10 = 1.0) ...
I'm just using SSIS to build my first data warehouse. I've read quite a bit, but I'm stuck at the very first task: I need to assign a key column (just an int that starts at 1 and basically numbers the rows). What transformation do I use? I was going to use the derived column, but if that's the case, I don't know the script for the expression to do it. If I'm right, would someone mind supplying that also?
Hello, I have a field of type the Var, the stored data makes about 700 characters. During a select through the query analyser, it tronque in about 200 or 300 characters. How to explain it? thank in advance Pascal