I have a ACTION column. i.e Its only disply SELL/BUY.
ACTION
BUY
BUY
BUY
SELL
SELL
SELL
SELL
BUY
BUY
SELL
SELL
SELL
BUY
How to create another column display like this
ACTION
BUY 1
BUY 2
BUY 3
SELL 1
SELL 2
SELL 3
SELL 4
BUY 1
BUY 2
SELL 1
SELL 2
SELL 3
BUY 1
Help me anyone...
I have a ACTION column. i.e Its only disply SELL/BUY.
ACTION
BUY
BUY
BUY
SELL
SELL
SELL
SELL
BUY
BUY
SELL
SELL
SELL
BUY
How to create another column display like this
ACTION
BUY 1
BUY 2
BUY 3
SELL 1
SELL 2
SELL 3
SELL 4
BUY 1
BUY 2
SELL 1
SELL 2
SELL 3
BUY 1
Help me anyone...
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:
Code:
ALTER PROCEDURE dbo.sp_NewPersonalPic
(
@photo_name VARCHAR(50) = NULL,
@photo_location VARCHAR(100) = NULL,
@photo_size VARCHAR(50) = NULL,
@user_name VARCHAR(50) = NULL,
@photo_caption VARCHAR(150) = NULL,
@photo_default BIT = NULL,
@photo_private BIT = NULL,
@photo_number INTEGER = NULL,
@photo_date DATETIME = NULL
)
AS
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:
photo_number photo_date
|1| 2006-06-26 21:43:36.653
|2| 2006-06-26 21:43:50.000
|3| 2006-06-26 21:45:25.217
|4| 2006-06-26 21:45:33.763
|5| 2006-06-26 22:39:42.670
|6| 2006-06-26 22:39:49.200
If |3| is removed above, the numbers are reordered based on the time of entry sequence.
Any suggestions on how to acheive this in my stored procedure? Currenly, i get the correct order, but it goes crazy when i delete and add.
Thanks and sorry for the verbose post.
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?
Hello,
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?
Hello everyone,
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.
BlackCatBone
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
View 1 Replies View RelatedCan 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.
Thanks!
Jim
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.
thanks,
Hi,
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 have a table with date in the following format:
orderID | dateOrdered | customerIDÂ
101 | 01/01/2015 | 1
102 | 02/02/2015 | 2
103 | 05/06/2015 | 3Â
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.
I want to add $ symbol to column values and convert the column values to western number system
Column values
Dollar
4255
25454
467834
Expected Output:
$ 4,255
$ 25,454
$ 467,834
My Query:
select ID, MAX(Date) Date, SUM(Cost) Dollars, MAX(Funded) Funding from Application
COST is the int datatype and needs to be changed.
Hi,
I am trying to create a inline function which is listed below.
USE [Northwind]
SET ANSI_NULLS ON
GO
CREATE FUNCTION newIdentity()
RETURNS TABLE
AS
RETURN
(SELECT ident_current('orders'))
GO
while executing this function in sql server 2005 my get this error
CREATE FUNCTION failed because a column name is not specified for column 1.
Pleae help me to fix this error
thanks
Purnima
Hi. Is it possible to select the columns you want given the column number instead of column name like the 3rd and 5th column?
Thanks!
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
View 2 Replies View Relatedhi
I am using sql Server as database what value should I send for the autonumber field
Thanks
I swear I have done this before and can't remember how.
I want to select columns in a SELECT statement by their number instead of name.
I have looked everywhere for the syntax.
Also, is there a function to 'count' the number of columns in a table?
Thanks.
is there a way to sql query via a spesific column number
'get from table A row 1 column 1' and then 'get from table A row 2 column 3'
and so on
thanx
I want to select with column order without field name .
Is it possible ?
SQL2000 db
...
I want to select a field by its order in table, but without naming it to have a constant name of it, like :
select (column01) as L1 from myTable
Hi,
I have several INT columns in a table that I need to update.
For example, in column 'aa' I need to add 2 to all of the values in that column.
I'm using Query Analyzer - what update statement should I write?
I have table
id, series, seriesnr
and data
65557,AS,0
74443,AS,0
how to update table so seriesnr would be incremental , like
65557,AS,1
74443,AS,2
if i use
update x set seriesnr=select max(seriesnr) from x
then i got same seriesnr for all rows like max was calculated only one time on the begining not on each row update(why is that itseem so illogical)
65557,AS,1
74443,AS,1
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?
Thank You,
Insert into newusers (Timer-1)
Select Timer
From newuses
GO
HI,
Can I come to know Column number of particular cell.The way we get rownumber().
-Thanks,
Digs
I have several tables where I have used the command
distinct first_name, last_name, dob
to unduplicate.
I was wondering if I could create a new column in my table that combines the existing records for first_name, last_name and dob?
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"?
View 3 Replies View RelatedI have a table that looks like this:
id, x
1 9.5
2 10
3 11
...
n x
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)
...
Any suggestions on the best way to do this?
Hello,
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?
Thanks a lot,
Andy
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