I have this query where I do certain conversions and on top of this how can I sum the column values.
'$ '+ Replace(CONVERT(varchar,CAST(Total_Amount AS money),1) ,'.00','') as Total_Amount,
'$ '+ Replace(CONVERT(varchar,CAST(Monthly_Amount As money),1),'.00','') as Monthly_Amount,
It was my understanding earlier that -- it is mandatory to have a Derived Column for a DATE String in a Flat File
to be loaded properly into a table with "datetime" column type
However, i tried running my package with the Input Column as "DT_STR" and the Destination on the table is DATETIME.
Looks like the process went on fine.. Am i missing something here?
My Date Format from the source looks like -- "DD-MON-YY"
Regards
I have multiple columns in a table that I only want to convert if they are not null or of the proper type. I don't really want to redirect the whole row if one of the conversions fails, rather I would just not want to do the cast or set it to another value. In this scenario is it better to use a custom script or multiple tasks? The multiple tasks seems like a lot of overhead for processing the same data. Just curious how others handle this scenario as it seems as though it has come up quite often on our current project? Thanks for the assistance.
-Krusty
PS: Is it possible to embed evaluation statements along with conversion statements in a task expression? e.g. (len(trim([Column1])) > 0) substring([Column1], 1, 4)
We have SharePoint list which has, say, two columns. Column A and Column B.
Column A can have three values - red, blue & green.
Column B can have four values - pen, marker, pencil & highlighter.
A typical view of list can be:
Column A - Column B
red - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red - pen
blue - pencil
blue - highlighter
blue - pencil
We are looking to create a report from SharePoint List using SSRS which has following view:
red blue green
pen 2 0 1
marker 0 1 0
pencil 1 3 0
highlighter 1 1 1
We tried Sum but not able to display in single row.
I have an SSIS package that imports data from an Excel file, replaces any value in Excel that reads "NULL" to "", then writes the data to a couple of databases.
What I have discovered today, is I have two columns of dates, an admit date and discharge date column, and what I need to do is anywhere I have a null value in the discharge date column, I have to replace it with the value in the admit date column.
I have searched around online and tried a few things using the Replace funtion in Derived columns but no dice so far.
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.
I have SQL Server 2012 SSIS. I have Excel source and OLE DB Destination.I have problem with importing CustomerSales column.CustomerSales values like 1000.00,2000.10,3000.30,NotAvailable.So I have decimal values and nvarchar mixed in on Excel column. This is requirement for solution.However SSIS reads only numeric values correctly and nvarchar values are set as Null. Why?
CREATE TABLE [dbo].[Import_CustomerSales](
[CustomerId] [nvarchar](50) NULL,
[CustomeName] [nvarchar](50) NULL,
[CustomerSales] [nvarchar](50) NULL
) ON [PRIMARY]
Bitmask fields! I am capturing row changes manually via a high frequency ETL task. It works effectively however i am capturing the movement of multiple fields. A simple example, for Order lines, i have a price, a discount and a date. I am capturing a 001, 010, 100 respectively for each change.
I would like my users to be able to select from a dimension which has the 3 members in it and they can select one, multiples, or all values (i.e. only want to see rows that have had the date and price changed).
Obviously if i only had 3 columns i would use bit's and be done with it, i have many different values (currently around 24 and growing).
How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?
View 5 Replies View RelatedI have a table with 2 columns and my source data looks like this..
PolicyNum InsCode
1ABC12 1001
1ABC12 1002
1ABC12 1003
1ABC12 1004
1ABC12 1005
[Code] ....
My output should look like this..I need T-sql to get below output.
PolicyNum InsCode1 InsCode2
1ABC12 1001 1005
1ABC12 1002 1006
1ABC12 1003 1004
1ABC20 1001 1005
[Code] ...
Basically it's converting certain row values to new column. Every PloicyNum will have 1001 to 1006 Fixed InsCode values as a group.
Rule-1: InsCode value 1001 should always mapped to 1005
InsCode value 1002 should always mapped to 1006
InsCode value 1003 should always mapped to 1004
Rule-2: For a policyNum, If any Inscode value is missed from the group values 1001 to 1006, still need to mapped with corresponding values as shown in Rule-1
In the above sample data..
for PolicyNum - 1ABC20 , group values 1003,1006 are missing
for PolicyNum - 1ABC25 , group values 1002,1003,1004,1005,1006 are missing
Create Table sampleDate (PolicyNum varchar(10) not null, InsCode Varchar(4) not null)
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1001')
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1002')
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1003')
[Code] ....
Hi All, I struck by a issue, In a table(tblMemberPayments) i had two columns one is to hold (month) as CHAR and other one is to hold (year) as INT. Now i need to implement search for this table based on the dates given , I mean i need to get the records from this table in between any two dates given by the user (Eg : feb 2007 to feb 2008). While retriving i am unable to type cast the things as required for the above case. I am getting an error unable to convert varchar to datetime while using CONVERT FUNCTION the same thing a raises while using DATEDIFF FUNCTION .help me if any one knows the solution . here are the couple of ways i tried 1) 'CONVERT(varchar(10),(tblMemberPayments.ExpirationMonth + tblMemberPayments.Expirationyear),1) between CONVERT(varchar(10),''feb 2007'',1) AND CONVERT(varchar(10),''feb 2008'',1)' Msg 245, Level 16, State 1, Line 1Conversion failed when converting the varchar value 'Apr' to data type int. 2)'datediff(m,Cast(tblMemberPayments.ExpirationMonth + tblMemberPayments.ExpirationMonthint as datetime), ''Feb 2008'') >= 0 and datediff(m, cast(tblMemberPayments.ExpirationMonth + tblMemberPayments.ExpirationMonthint as datetime), ''Feb 2007'') <= 0' Thanks & Regards, Sudheer.Y
View 2 Replies View RelatedHI ALL,
declere @d varchar(10)
declare @t varchar(10)
set @d=convert(varchar,'7/25/2007 10:10:53',101)
set @d=convert(varchar,'7/25/2007 10:10:53',108)
it returns as
@d=7/25/2007
@t=7/25/2007
but @t should be equal to 10:10:53
what's wrong in the conversions
Malathi Rao
In my DB I have strings which are all 8 characters long and all pass the isDate == 1 thing so everything row can be converted into a date.
When I run a select convert(datetime, value) it gives me an error saying that it's unable to convert datetime from string. However when I insert everything into a temp table and run each row individually, it converts fine.
Any ideas as to what I'm doing wrong? I've checked the date settings on SQL 2005 SP2 and the host itself and everything seems correct. I've been stuck on this all day and any help is very much appreciated.
Hi,I'm trying to load date fields into SQLServer using DTS, but theformat of the raw data is the number of days since 1 Jan 1900. How doI convert this to a useful format, is there a standard conversionroutine?ThanksTim
View 2 Replies View RelatedWhat are the pros/cons of datatype conversions using the advanced properties of the source adapters or transformation components themselves vs. using the data conversion transformation? Sometimes I am able to do all the conversions I need using the advanced properties rather than use a data conversion component, but didn't know if this was considered bad practice, or has some other impact that isn't immediately apparent.
Thanks
Kory
Having some trouble converting strings to dates. I am using a .dtsx package and have figured out how to use a derived column to convert the string to a new string which is a valid date. The problem is now converting the column data type to date. I insert a new data conversion step to convert the vchar field to date, then when it runs it fails on this step. Error is
The conversion returned status value 2 and status text "The value could
not be converted because of a potential loss of data."
If I leave out the data conversion step and run the package, then manually change the data type to date it warns with the same message, but if I accept it converts correctly.
Here is my derived column code
REPLACE(SUBSTRING([Data Conversion 1].START_OF_EXECUTION,1,10) + " " +
RIGHT([Data Conversion 1].START_OF_EXECUTION,8),".",":")
Please Help
I'm working on a social network where I store my friend GUIDs in a table with the following structure:user1_guid user2_guidI am trying to write a query to return a single list of all a users' friends in a single column. Depending on who initiates the friendship, a users' guid value can be in either of the two columns. Here is the crazy sql I have come up with to give what I want, but I'm sure there's a better way... Any ideas?SELECT DISTINCT UserIdFROM espace_ProfilePropertyWHERE (UserId IN
(SELECT CAST(REPLACE(CAST(user1_guid AS VarChar(36)) + CAST(user2_guid AS VarChar(36)), @userGuid, '') AS uniqueidentifier) AS UserId FROM espace_UserConnection WHERE (user1_guid = @userGuid) OR
(user2_guid = @userGuid))) AND (UserId IN
(SELECT UserId FROM espace_ProfileProperty))
This is a report I'm trying to build in SQL Reporting Services. I can do it in a hacky way adding two data sets and showing two tables, but I'm sure there is a better way.
TheTable
Order# Customer Status
STATUS has valid values of PROCESSED and INPROGRESS
The query I'm trying to build is Count of Processed and INProgress orders for a given Customer.
I can get them one at a time with something like this in two different datasets and showing two tables, but how do I achieve the same in one query?
Select Customer, Count (*) As Status1
FROM TheTable
Where (Status = N'Shipped')
Group By Customer
Can anyone show how to alter the value in a column using DerivedColumn component when creating an SSIS package programatically.
View 4 Replies View Related
I need to create the following table in reporting services
PRODUCT April March Feb
2008 2007 2008 2007 2008 2007
chair 8 9 7 4 4 4
table 3 4 5 6 4 6
My problem is the month names are a column in the dataset, but I dont know how to get it to fill as column headers???
Thanks in advance!!!
I have a question regarding a casting error I recieve when I explictly assign a value to a value in a stored procedure.......
The value pstrLoginName is a public string defined in a code module
The stored procedure is NVARCHAR Datatypes.....
Here is the Stored Procedure
CREATE procedure dbo.Appt_GetPermissions_NET
(
@LoginName nvarchar(15),
@Password NvarChar(15),
@DeleteScan bit Output
)
as
select
@DeleteScan=UserP_DeleteScan
from
Clinic_Users
where
UserName = @LoginName
and
UserPassword = @Password
GO
Here is my error
Specified cast is not valid
Line 194: cmdsql.CommandType = CommandType.StoredProcedure
Line 195:
Line 196: parmLogin = cmdsql.Parameters.Add("@LoginName", SqlDbType.NVarChar, 15).Value = pstrLoginName
Line 197: parmPassword = cmdsql.Parameters.Add("@LoginPassword", SqlDbType.NVarChar, 15).Value = pstrLoginPassword
Line 198: parmDelete = cmdsql.Parameters.Add("@DeleteScan", SqlDbType.Bit)
my vb .net code to execute this
Public Sub ObtainPermission()
'get the needed data
Dim consql As New SqlConnection("Server=myserver;database=APPOINTMENTS;uid=webtest;pwd=webtest")
Dim cmdsql As New SqlCommand
Dim parmLogin As SqlParameter
Dim parmPassword As SqlParameter
Dim parmDelete As SqlParameter
cmdsql = New SqlCommand("Appt_GetPermissions_NET", consql)
cmdsql.CommandType = CommandType.StoredProcedure
parmLogin = cmdsql.Parameters.Add("@LoginName", SqlDbType.NVarChar, 15).Value = pstrLoginName <----error occurs here
parmPassword = cmdsql.Parameters.Add("@LoginPassword", SqlDbType.NVarChar, 15).Value = pstrLoginPassword <---I'm sure it will happen here then
parmDelete = cmdsql.Parameters.Add("@DeleteScan", SqlDbType.Bit)
parmDelete.Direction = ParameterDirection.Output
consql.Open()
cmdsql.ExecuteNonQuery()
'obtain rights
pstrPermissions = cmdsql.Parameters("@DeleteScan").Value
consql.Close()
End Sub
Hi all,I have a DataRow that I'm attempting to insert into SQL. The row has an item I"ll refer to as row["AbsorbtionDeficiency"]. I'm filling this data row from a ODBC connection to a propriatary database. Now I want to insert this data into SQL where the same row data type is "money".I'm not to familiar with the different data types, but the long and short of it is this. When I build my SQL Insert statement, sometimes this value is blank, sometimes it's 0, and sometimes it will have a dollar amount(no formating just the numeric). How to I get this data out of the row in a format that SQL understands... Since I'm creating a SQL string the datatype of the row I assume doesn't matter, so I"m doing a .ToString() when I build the statement.Do I need to do a CAST('0' AS MONEY) to each line that is money type? Or is there an easier way?I would insert the code, but it's a pretty masive statement and I don't think it's really needed yet.ThanksJosh
View 1 Replies View RelatedI can't get sp_executesql to accept more than 10 converted parameters in one execution. A stored procedure loops through a table variable that presents up to 100 parameters and their named data types. I've tried selecting into sql_variant and nvarchar variables and converting them to the named data type in the loop.
sp_executesql is outside the loop.
For the first 10 parameters sp_executesql recognizes the data type conversions of the variables that hold the parameter values, but then it fails to accept the conversions of the 11th and subsequent variables. Is there a limit to the number of conversions that sp_executesql can cope with?
I have to do a lot of inporting from Access files. Is there a place where I can change the default datatype conversion for Access Text from nvarchar to varchar?
Thanks.
Kato
Hi
I am quite new to SSIS and have been given the task of importing some data from a text file into the database. The data contains dates which are in the American format of mm/dd/yyyy. I need them in the datadase in the format of dd mon yy.
I realise I could load it and do a SQL task to convert once it is in the database but ideally i would like this data transformed before it is loaded into the tables.
any suggestions will be gratefully recieved.
Best regards
I'm importing a csv file into sql server and everything is working fine however here are my problems.
The csv file fields contain some money fields and even though my db column type is money the dts package console complains that my data type from the csv is of type string 'for the money value' and cannot be inserted into the db field because they don't match. I changed the db field to nvarchar and hey presto it works fine. Problem is that this is no good because the data in the db is no longer a money value and therefore I cannot query the way I should.
So basically how do i map my csv to pass its values as the right types instead of just string values. If this was in c# i could just assign the parameter types accordingly but I'm not sure how to with sqlservers dts package.
Any help!!!!!
Recently I decided to start using DotNetNuke, which uses an SQL Server database. My webhoster offers SQL Server 2000, and I was able to install and run DNN successfully there. However, I wish to have a local copy of DNN on my PC with the same contents as is on the web. It means that I need either to convert SQL Server 2000 database to my local SQL Server 2005 Express database, or vice versa.
Although I programmed in (Visual) FoxPro for many years, I am new to SQL Server and I am overwhelmed by the vast number of new terms. Can anyone tell me in simple English, how to perform the above mentioned conversions? My webhoster allows me to backup the SQL Server 2000 database in an *.sql file, but how shall I convert this file into an SQL Server 2005 Express *.mdf file?
Am getting ready to perform upgrade/migrate from 2000 to 2005 and have concerns about all the usernames/passwords converting accurately, as the existing 2000 instance has hundreds of them (am used to conversions with few usernames/passwords). Any suggestions as to the best way to proceed. The upgrade/migration is being done on the same box. Will a straight upgrade convert the master db cleanly with u/p, what issues does a migration on the same box present? Thanks.
I'm trying to write some code that will alter column to change its type, in this case from Nvarchar to Int, but I'm not sure if there will be any columns that are uncovertable (say, there's a string that does not have an Int value). In this case, I'd like it to default to 0. Is there a way to write a T-SQL statement to do this, or will I have to write an UPDATE stamement first to clear out any problem cases?
View 1 Replies View RelatedGood afternoon,
I am trying to figure out a way to use a columns default value when using a stored procedure to insert a new row into a table. I know you are thinking "that is what the default value is for", but bare with me on this.
Take the following table and subsequent stored procedure. In the table below, I have four columns, one of which is NOT NULL and has a default value set for that column.
CREATE TABLE [dbo].[TestTable](
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[SSN] [nvarchar](15) NULL,
[IsGeek] [bit] NOT NULL CONSTRAINT [DF_TestTable_IsGeek] DEFAULT ((1))
) ON [PRIMARY]
I then created the following stored procedure:
CREATE PROCEDURE TestTable_Insert
@FirstName nvarchar(50),
@LastName nvarchar(50),
@SSN nvarchar(15),
@geek bit = NULL
AS
BEGIN
INSERT INTO TestTable (FirstName, LastName, SSN, IsGeek)
VALUEs (@FirstName, @LastName, @SSN, @geek)
END
GO
and executed it as follows (without passing the @geek parameter value)
EXEC TestTable_Insert 'scott', 'klein', '555-55-5555'
The error I got back (and somewhat expected) is the following:
Cannot insert the value NULL into column 'IsGeek', table 'ScottTest.dbo.TestTable'; column does not allow nulls. INSERT fails.
What I would like to happen is for the table to use the columns default value and not the NULL value if I don't pass a parameter for @geek. OR, it would be really cool to be able to do something like this:
INSERT INTO TestTable (FirstName, LastName, SSN, IsGeek)
VALUEs (@FirstName, @LastName, @SSN, ISNULL(@geek, DEFAULT))
Does this make sense?
Can anyone assist me with a script that adds a new column to a table then inserts new values into the new column based on the Table below. i have included an explanation of what the script should do.
Column from
Parts Table Column from
MiniParts New Column in
(Table 1 ) (Table 2 ) MiniParts (Table2)
PartsNum
MiniPartsCL
NewMiniPartsCL
1
K
DK
1
K
K
1
Q
Q
0
L
L
0
L
LC
0
D
G
0
S
S
I have 2 tables in a database. Table 1 is Parts and Table 2 is MiniParts. I need a script that adds a new column in the MiniParts table. and then populate the new column (NewMinipartsCL) based on Values that exist in the PartsNum column in the Parts Table, and MiniPartsCL column in the MiniParts columns.
The new column is NewMiniPartsCL. The table above shows the values that the new column (NewMiniPartsCL) should contain.
For Example
Anytime you have "1" in the PartsNum column of the Parts Table and the MiniPartsCL column of the MiniParts Table has a "K" , the NewMiniPartsCL column in the MiniParts Table should be populated with "DK" ( as shown in the table above).
Anytime you have "1" in the PartsNum column of the Parts Table and the MiniPartsCL column of the MiniParts Table has a "K" , the NewMiniPartsCL column in the MiniParts Table should be populated with "K" ( as shown in the table above). etc..
Hi, how are you?
I'm having a problem and I don't know if it can be solved with a derived column expression. This is the problem:
We are looking data in a a sql database.
We are writting the SQL result in a flat file.
We need to transform data in one of the columns.
For example: we can have 3 digits as value in a column but that column must be 10 digit length. So we have to complete all the missing digits with a zero. So, that column will have the original 3 digits and 7 zeros. How we can do that tranformation? We must do it from de the flat file or it can be a previous step?
Thanks for any help you can give me.
Regards,
Beli
Hi,
I have got a table where i want to display sum of count(Column1), count(Column2) in another column.How can this be done?
for example
SELECT SUM(Count(pxInsName)+Count(pxFlowName)) AS "pySummaryCount(1)" , Count(pxInsName) AS "pySummaryCount(2)" , Count(pxFlowName) AS "pySummaryCount(3)" , pxAssignedOrg AS "pxAssignedOrg" , pxAssignedOrgDiv AS "pxAssignedOrgDiv" , pxAssignedOrgUnit AS "pxAssignedOrgUnit" FROM pc_assign_worklist WHERE pxObjClass = ? GROUP BY pxAssignedOrg , pxAssignedOrgDiv , pxAssignedOrgUnit ORDER BY 'pySummaryCount(1)' DESC
But sum function can not be used on aggregate function.
Is there any other way.