Friday, May 3, 2013

[T-SQL] How to sum in SQL removing varchar

[T-SQL] How to sum in SQL removing varchar

How to sum in SQL removing varchar

Posted: 02 May 2013 05:08 PM PDT

Hi All,I am trying to sum of amount (data type varchar) column but I am getting error error converting data type varchar to float.when I checked the data very carefully I found that there is an amount like C000214534. But when I am removing that varchar amount I am not getting errorselect sum(CONVERT(float, ft.amount)) FROM filetransaction ft WHERE ft.fileid <>332 this query gives me the result.is there any way in sql so that it can sum removing varchar? I do not want to update as everyday the data is inserted from the client's end.How can I sum those data except the data where varchar type.Please help!!

Inner join returning all rows.

Posted: 03 May 2013 12:14 AM PDT

I figured it out....Thank you,I would like to return all the data for a [b]single[/b] empnum, but this query is returnng data for [b]ALL[/b] empnum's. any suggestion would be greatly appreciated. select E.empnum, rtrim(C.lname) + ', ' + rtrim(C.fname) as Name, rtrim(substring(C.dept,1,3)) as Office, C.dept, calldate, duration_in_decimal, iofrom CALLS C INNER JOIN EMP E on C.empnum = E.empnum and [b]C.empnum = 6396[/b] where(month(calldate) = case month('2012/11/30') when '1' then '12' else month('2012/11/30') - 1 end and year(calldate) = case month('2012/11/30') when '1' then year('2012/11/30') - 1 else year('2012/11/30') end and duration_in_decimal > .0055555 and (len(phonenum) IN (7,10,11))) or (month(calldate) = case month('2012/11/30') when '1' then '12' else month('2012/11/30') - 1 end and year(calldate) = case month('2012/11/30') when '1' then year('2012/11/30') - 1 else year('2012/11/30') end and io = 'I') ORDER BY C.empnum;

HHMMSS int field to human-friendly time?

Posted: 06 Jan 2012 05:02 AM PST

I'm looking at the next_run_time field in sysjobschedules, which is stored as an integer with a presumed format of HHMMSS according to BOL. I'd like to get this to a human-friendly time display of HH:MM:SS. I have code that's working, but it's ugly. Really ugly. I know there's better available but either my search skills are seriously lacking or nobody's sharing and I'm drawing a blank. Since it's an int field, there has to be a way of left padding with 0.Here's what I have:[code] SELECT next_run_date , next_run_time , LEFT(RIGHT('000000' + CAST(next_run_time AS VARCHAR(6)), 6), 2) + ':' + SUBSTRING(RIGHT('000000' + CAST(next_run_time AS VARCHAR(6)), 6), 3, 2) + ':' + RIGHT(RIGHT('000000' + CAST(next_run_time AS VARCHAR(6)), 6), 2) AS TheTime FROM msdb.dbo.sysjobschedules AS s[/code](BTW, this is going into an SSRS report so if there's a way to format it on that end I'm all for it.)

How to do this type of insert?

Posted: 02 May 2013 02:53 PM PDT

I'm sorry if this question has been asked here, but I couldn't find. I am new to sql and need to do the following type of insert: Insert data INTO Table 1 FROM Table 2. The data is to be inserted based on the values of certain columns in Table 1 i.e. Table1.columnA =null, Table1.columnB = null (or in some cases a particular value). What would be the SQL query I can use to achieve this? Thanks.

COLLATION Between two databases, checking on which collate to use

Posted: 02 May 2013 07:10 AM PDT

DATABASE A SQL_Latin1_General_CP850_BINDATABASE B SQL_Latin1_General_CP1_CI_ASTEMPDB SQL_Latin1_General_CP1_CI_ASUPDATE DATABASEA.TABLE1 SET XXDT = A.COMPLT_DT -1 FROM DATABASEB.TABLE2 A INNER JOIN DATABASEA.TABLE1 B ON A.ID = B.ID COLLATE Latin1_General_CI_AS WHERE A.ID = B.ID COLLATE Latin1_General_CI_AS AND ISDATE(A.SYS_DT) = '1' AND A.DATECOM= @DateComIf i am writing back to a database table that is set to 850, does the check need to be COLLATE Latin1_General_CP850_BIN ?How do we create temp tables with collation 850?CREATE TABLE ##TS_ACCT ( [TS_ID] [nvarchar](100) NULL )

convert a PLSQL to T_SQL for a function

Posted: 02 May 2013 08:46 AM PDT

I would like to convert a function from PL-SQL to T_SQL, not sure about the syntax,Any one can help?, code are in attachment

A Pivot, but not exactly.

Posted: 02 May 2013 02:43 AM PDT

I have a table that could look like the following:ID FK_ID Value1 100 'Blue'2 100 'Black'3 100 'Green'4 101 'Blue'5 101 'Green'6 102 'Black'I need a query that outputs the following:FK_ID NewVal100 'Blue,Black,Green'101 'Blue, Green'102 'Black'Also, I won't always know that 'Blue', 'Black', or 'Green' are going to be the distinct values.

Logging Raiserror log messages into a table

Posted: 21 Feb 2011 08:47 PM PST

Hi,I have several raiserror statements (all informational messages ) inside my stored procedure, Can I have them logged into a table automatically when the stored procedure is executed ?

Get Latest Revision of Attribute Based on Date

Posted: 02 May 2013 02:48 AM PDT

Hi all,First real post here although an avid reader of some of the posts. Mainly a DBA but started to get more in the TSQL development side of things. Came across a problem that I'm struggling to get a good set-based solution to (I can do this with cursors, but I'd rather stay away from them if possible).Basically, I am having to create a table that is the child of the parent table. It mirrors the structure of the parent table with an additional 3 columns, a PK, a date changed (inserted) and a user_id field.The application will insert a row (which is essentially a change record to the parent) for every change to the parent record that a user makes. So if they change all 3 attributes, all the col1/2/3 are populated and so on.I basically need to bring back the latest revisions to the attributes based on the date. I need to bring back the foreign key (fk) and cols 1, 2 and 3 to display to the user. See the code.[code="sql"]IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test]') AND type in (N'U'))DROP TABLE [dbo].[test]GOCREATE TABLE [dbo].[test]( id [int] NOT NULL, [fk] [int] NOT NULL, [col1] [int] NULL, [col2] [int] NULL, [col3] [int] NULL, [changed] [datetime] NOT NULL, [userid] [int] NOT NULL) GOALTER TABLE dbo.test ADD CONSTRAINT pk_test PRIMARY KEY(id)GOINSERT INTO test VALUES (1,1,1,NULL,3,GETDATE()+1,1)INSERT INTO test VALUES (2,1,NULL,2,NULL,GETDATE()+2,2)INSERT INTO test VALUES (3,1,4,NULL,NULL,GETDATE()+3,3)GO[/code]So for this example, I would like a single row displayed with the values1,4,2,3 for cols fk,col1,col2,col3I have no requirement to display anything other than that - so effectively it'd be like collapsing the row to the latest date. I've looked a using ROLLUP, but I'm not sure it will give me what I need. Like I say, I could do this using cursors, but I'm hoping there's a better way. Anyone else had to do something like this? Any help would be greatly appreciated!
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog