Thursday, June 20, 2013

[T-SQL] convert int int to date urgent please

[T-SQL] convert int int to date urgent please

convert int int to date urgent please

Posted: 20 Jun 2013 12:18 AM PDT

/****** Script for SelectTopNRows command from SSMS ******/SELECT TOP 1000 [ServerName] ,[IsExisting] ,[IsEnabled] ,[IsScheduleEnabled] ,[NAME] ,CONVERT(date, CONVERT(VARchar(18), LASTRUNDATE)) ,[Date] FROM [Monitor].[dbo].[Job_Monitor] plz need it urgent lastrundate is int (20130620)format need to convert to date 2013/06/20error :::Msg 241, Level 16, State 1, Line 4Conversion failed when converting date and/or time from character string.

UPDATE, MERGE or table-valued function UDF?

Posted: 19 Jun 2013 09:11 AM PDT

Hi,I'm confused how to solve this:Using UPDATE, MERGE, table-valued UDF or what?:w00t:I know using a cursor is possible but there are disadvantages...tblA [code="plain"]colA1 colA2 colA3=======================C 15 NULLD 17 NULL G 18 NULL L 19 NULL R 20 NULL [/code]tblB (the lookup table) [code="plain"]colB1 colB2 col3=======================28 15 134 17 135 18 136 18 037 18 042 19 143 20 044 20 1[/code]To do:Find in tblB the lines with col3 = 1 (exists only once for same colB2-value), Then UPDATE tblA colA3 with value colB1. RI exists on colB2 = colA2

Interesting Question related to Splitting table row conditionally

Posted: 19 Jun 2013 10:47 PM PDT

Hi SQL Addicts,Please help! Its emergencyThe Table setup is as followsCREATE TABLE dbo.Receipt(ReceiptID int NOT NULL IDENTITY(1,1), PolicyID int NOT NULL, ReceiptAmt NUMERIC(17,2) NOT NULL)GOINSERT INTO dbo.Receipt(PolicyID, ReceiptAmt)SELECT 11, 3000UNION ALLSELECT 11, 5000UNION ALLSELECT 11, 1200GOCREATE TABLE dbo.MemberPremiumDet(MemberID int NOT NULL IDENTITY(1,1), PolicyID int NOT NULL, PremiumAmt NUMERIC(17,2) NOT NULL)GOINSERT INTO dbo.Receipt(PolicyID, PremiumAmt)SELECT 11, 1200UNION ALLSELECT 11, 1600UNION ALLSELECT 11, 2200UNION ALLSELECT 11, 2456UNION ALLSELECT 11, 1144GOReceipt Table DataPolicyID ReceiptID ReceiptAmt11 1 300011 2 500011 3 1200Member DataPolicyID MemberID PremiumAmt11 1 120011 2 160011 3 220011 4 245611 5 1144With the above given two tables the below output to be generated without using Cursors or loopsI Want the output as followsMemberID ReceiptID ReceiptAmt ConsumedAmt BALAmt171 11 3000 1200 1800172 11 1800 1600 200173 11 200 200 0173 14 5000 2000 3000174 14 3000 2456 544175 14 544 544 0175 16 1200 600 600Thanx in advance!!!

pivot? can't make it work.

Posted: 19 Jun 2013 09:05 PM PDT

Hi there, I'm hoping for a pointer in the right direction as I can't suss this out & feel that it shouldn't be that complicated.I have a table id characteristic value(nvarhcar)1 color blue1 age 51 gender female2 color green2 cost 53 desc blahblah3 lastcheck 27/jan/093 ... 3 ...Client wants output as follows:id characteristic1 value1 characteristic2 value2 ... 1 color blue age 2 color green desc blah There is no aggregate.Ideally query would by dynamic as new characteristics values may be added. not all id's have same number of characteristic values.does this make sense?any ideas / pointers gratefully accepted?thanks.

Strange issue with sp_OAMethod

Posted: 19 Jun 2013 08:51 PM PDT

My procedure creates a COM object with sp_OACreate. Then it uses the sp_OAMethod to call a "load" method of the underlying DLL. Finally it uses sp_OADestroy to dispose the COM object.The procedure works perfectly fine the first round.During the second round of execution, sp_OACreate succeeds. But sp_OAMethod returns insufficient memory error. I was under the impression that sp_OADestroy would have released the memory, but that doesn't seem to be the reality.I tried the following commands after the first round of executionDBCC DROPCLEANBUFFERSDBCC FREEPROCCACHEDBCC FREESESSIONCACHEDBCC FREESYSTEMCACHE ('ALL')But none of them (nor all of them together) help.The only way out is to restart the SQL Service after each round of execution.Is there a way around this?

Append characters in a sentence after certain length

Posted: 19 Jun 2013 03:21 AM PDT

Hi,Need your help in accomplishing this one. We run on SQL Server 2008. I've a string that can be upto 200 characters max and after every 32 characters a pile (|) got to be inserted. if that 32 characters come in midword, then that pipe has to be placed in the beginning of that word, not in the middle of the word. Example:Original String: ABCDEFGH IJKLMNOPQRS TUVWXYZ ABCDEFGHIJKLMN OPQRSTUVWXYZA BCDEFGHIJKLMNOPWant this way: ABCDEFGH IJKLMNOPQRS TUVWXYZ |ABCDEFGHIJKLMN OPQRSTUVWXYZA |BCDEFGHIJKLMNOPsince these strings are in a table.column and this need to operate on the entire data set (about 200k records), performance is also important.thanks in advance..

compare all tables and find number of common columns between all tables

Posted: 19 Jun 2013 03:05 PM PDT

is there any way to compare every table with each other in a database list the number of common column between each tables!! ?? prefer using join over subquery and information_schema.column, if possible thx. example: lets say there are 4 tables in a database table1, table2, table3 and table4.list1 list2 commonColumns ----- -------- --------------------table1 table2 1table1 table3 2table1 table4 0table2 table1 1table2 table3 5table2 table4 2table3 table1 3 table3 table2 0table3 table4 1table4 table1 2table4 table2 3table4 table3 0this is what i have got so farselect c1.table_name,c2.table_name,c1.COLUMN_NAMEfrom information_schema.columns c1JOIN information_schema.columns c2 ON c1.column_name= c2.column_name where c2.table_name <> c1.table_name and c2.column_name = c1.column_nameORDER BY c1.table_name, c2.TABLE_NAME

Non cluster index help (SSMS 2008 R2)

Posted: 19 Jun 2013 05:02 PM PDT

Hi Friends,I have table (Table_A (A_id int(PK), col1 nvarchar, col2 nvarchar, col3 nvarchar, col4 nvarchar, col5 nvarcarCol6 nvarchar))A_id int(PK),--- already have one clusterd index on this column as this is PKI hav only ONE non clustered index on col1, col2 and col3 and i have below querySELECT col4, COl5, col6FROM Table_A a INNER JOIN Some_Table_B bON a.a_id = b.b_some_idWHERE a. col1 = 'Some value' AND a.col2 = 'some Value' and a.col3 = 'SOme value'**Question 1** do i need more NON Clustered index on this table so that, this query will perform well(as SELECT statement has more columns which dont have any indexex, ). **Question 2**if i put the non clusterd index on the columns which are only considerd for displaying the result set(example, here col4, COl5, col6 with SELECT only, these columns are not utilised in WHERE or JOIN clause ) WIll this improve performance of the query **Question 3**Another question (as as i have mentined earlier NON clustred index is on col1, col2 and col3 )if my WHERE clause hav only col_1 and col_2 ,Example SELECT col4, COl5, col6FROM Table_A a INNER JOIN Some_Table_BON a.a_id = b.b_some_idWHERE a. [b]col1 [/b]= 'Some value' AND a.[b]col2 [/b]= 'some Value' --- no column3 okwill this non cluster index help or do i need to create another index only with these two columns.Please do the needful.**Thanks in AdvanceParixitsinh**

How to create the Dynamic Table...

Posted: 19 Jun 2013 04:45 AM PDT

Hi Friends,I have the sample data for the following Structure[code="sql"]Create Table PivotA ( IDT int,A int,B int)insert into PivotA values (24,1,-1),(24,2,-2),(24,3,-3),(24,4,-4),(25,5,-5),(25,6,-6),(25,7,-8),(26,8,-8),(26,9,-9),(26,10,-10)select * from PivotA[/code]but i need to create the tables dynamically for the following structure..based on the PivotA Table...[code="sql"]create table IDT_24( A int,B int)insert into IDT_24 values (1,-1),(2,-2),(3,-3),(4,-4)create table IDT_25( A int,B int)insert into IDT_25 values (5,-5),(6,-6),(7,-8)create table IDT_26( A int,B int)insert into IDT_26 values (8,-8),(9,-9),(10,-10)select * from IDT_24select * from IDT_25select * from IDT_26[/code]How To Get it?any one help me...Thanks & RegardsK.D.Saravanan
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog