Wednesday, February 27, 2013

[T-SQL] Date Conversion

[T-SQL] Date Conversion

Date Conversion

Posted: 26 Feb 2013 09:36 PM PST

Hi allI have a date which is in a text file which looks like this '25/02/12 11:39:34'I have been playing around with CONVERT so I can insert it into a DATETIME field of a database but I keep getting conversion errors.Does anyone have any suggestions on how I can do this?Thanks

Compare the data in two tables, If Different, Update

Posted: 26 Feb 2013 02:35 AM PST

Hello everyoneI am working on a problem where I need to compare the data in two tables. I can have some columns in the table that will be the same, those I use to join on. But a couple columns in the table, will be changing. If the values have changed, I need to update the data in the other table.[code="sql"]--===== If the test table already exists, drop it IF OBJECT_ID('TempDB..#OriginalData','U') IS NOT NULL DROP TABLE #OriginalData--===== Create the test table with CREATE TABLE #OriginalData ( RowID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED , MemberID int , LocationID varchar(5) , FirstName varchar(25) , LastName varchar(25) , Birthdate date , AreaCode int , PhoneNumber varchar(8) ) --===== If the test table already exists, drop it IF OBJECT_ID('TempDB..#UpdatedData','U') IS NOT NULL DROP TABLE #UpdatedData--===== Create the test table with CREATE TABLE #UpdatedData ( RowID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED , MemberID int , LocationID varchar(5) , FirstName varchar(25) , LastName varchar(25) , Birthdate date , AreaCode int , PhoneNumber varchar(8) )INSERT INTO #OriginalData (MemberID, LocationID, FirstName, LastName, Birthdate, AreaCode, PhoneNumber) SELECT 222, 'Viol','Sammy','Miller','1965-12-15', 888, '555-1212' UNION ALL SELECT 223, 'Green','Sarah','Smith','1968-03-02', 523,'458-2356' UNION ALL SELECT 224, 'Green','Michelle','Foster','1966-12-12', 657,'241-7815' UNION ALL SELECT 225, 'Gray', 'Robert','Gillum','1970-07-20', 879,'251-6300' UNION ALL SELECT 226, 'Red', 'Joe','Roberts','1960-05-05', 211,'205-8785' UNION ALL SELECT 227, 'Red', 'Danny','Jones','1961-10-30', 696,'575-5611' UNION ALL SELECT 228, 'Blue', 'Andy','Hill','1966-08-01', 315,'473-2501' UNION ALL SELECT 229, 'Blue', 'Bill','Height','1962-11-20', 315,'474-5670' INSERT INTO #UpdatedData (MemberID, LocationID, FirstName, LastName, Birthdate, AreaCode, PhoneNumber) SELECT 222, 'Viol', 'Sammy','Miller','1965-12-15', 888, '555-1212' UNION ALL SELECT 223, 'Green', 'Sarah','Smith','1968-03-02', 523,'458-2356' UNION ALL SELECT 224, 'Green', 'Michelle','Foster','1966-12-12', 657,'241-7800' UNION ALL -- Changed PhoneNumber SELECT 225, 'Gray', 'Robert','Gillum','1970-07-20', 879,'251-6300' UNION ALL SELECT 226, 'Red', 'Joe','Roberts','1960-05-07', 211,'205-8585' UNION ALL -- Changed Birthdate, PhoneNumber SELECT 227, 'Red', 'Danny','Jones','1961-10-30', 696,'575-5611' UNION ALL SELECT 228, 'Blue', 'Andrew','Hill','1966-08-01', 315,'473-2502' UNION ALL -- Changed FirstName, PhoneNumber SELECT 229, 'Blue', 'Bill','Height','1962-11-20', 315,'474-5670' SELECT * FROM #OriginalDataSELECT * FROM #UpdatedDataDROP TABLE #OriginalDataDROP TABLE #UpdatedData[/code]MemberID and LocationID are the only two columns that can never change.If any row in the #UpdatedData table, except for MemberID or LocationID is different that Update #OriginalData row with the same row in the #UpdatedData table. As you can see, I have set 3 rows in the #UpdatedData table to be different than the rows in the #OriginalData table.Ignore all rows that have not changed, or are different between the two tables.So, my issues is, how can I identify the rows that are different between the two tables?Thank You in advance for all your assistance, advise and suggestions. This one is driving me crazy, I am coming up with a huge number of rows in my query, where in fact, only a couple rows have actually changed. I have tried a couple different JOINs to join the table tables, but something is not quite correct.Andrew SQLDBA

Data query

Posted: 26 Feb 2013 10:40 PM PST

Good day Gents,I have been trying on how to do this but i seems cant to think well of how to get this right.I have two tables, A and BTable A has two records as follow[u]IDA[/u]12Table B has lets say four records where IDA is a unique ID from table A[u]IDA[/u] - [u]Status[/u] - IDB1 - Closed - 101 - Closed - 112 - Active - 122 - Closed - 13Now i want to select all records from B where their Status is Closed, this can only be if all records are closed. i.e the query should return IDA - Status - IDB1 - Closed - 101 - Closed - 11The query could be something like this.[code="sql"]Select a.IDA, b.Status, b.IDBfrom A aJOIN B b on a.IDA = b.IDB[/code]

Query to list all jobs

Posted: 26 Feb 2013 12:24 AM PST

Hello,We are consolidating servers and I was asked to write a query that returns a list of jobs on a server so that we can get an idea of what jobs will need to be copied over to the new server. I have gotten pretty close with this query below.select j.name, step_id, step_name,subsystem,database_name, category_id from msdb..sysjobs j left join msdb..sysjobsteps ton (j.job_id = t.job_id)where enabled = 1order by category_idThe problem is it's returning jobs that where created automatically for the replication agents and other system jobs. We are looking for only the user defined jobs. I feel like category_id may help but I have not been able to find the meaning behind the integer in category_id. I would appreciate any help, thanks!

Restrict overlapping records

Posted: 26 Feb 2013 02:06 AM PST

HI All, Just wanted to know whether can we create a constraint to restrict the overlapping records in the following table.Here is the table script..SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[TranBreakUp]( [TranID] [int] IDENTITY(1,1) NOT NULL, [ProductCode] [nchar](4) NOT NULL, [AccountCode] [nchar](4) NOT NULL, [FromAmt] [numeric](17, 2) NOT NULL, [ToAmt] [numeric](17, 2) NOT NULL, [FromYear] [tinyint] NOT NULL, [ToYear] [tinyint] NOT NULL, [USRID] [varchar](10) NOT NULL, [SystemDt] [datetime] NOT NULL) ON [PRIMARY]GOALTER TABLE [dbo].[TranBreakUp]ADD CONSTRAINT PK_TranBreakUp_TranID PRIMARY KEY (TranID);GOALTER TABLE [dbo].[TranBreakUp]ADD CONSTRAINT UK_TranBreakUp UNIQUE (ProductCode, AccountCode, FromAmt, ToAmt, FromYear, ToYear );GOALTER TABLE [dbo].[TranBreakUp]ADD CONSTRAINT CK_TranBreakUp_FromAmt CHECK (FromAmt >= 0);GOALTER TABLE [dbo].[TranBreakUp]ADD CONSTRAINT CK_TranBreakUp_ToAmt CHECK (ToAmt > FromAmt);GOALTER TABLE [dbo].[TranBreakUp]ADD CONSTRAINT CK_TranBreakUp_FromYear CHECK (FromYear >= 0);GOALTER TABLE [dbo].[TranBreakUp]ADD CONSTRAINT CK_TranBreakUp_ToYear CHECK (ToYear >= FromYear);GOINSERT INTO [dbo].[TranBreakUp] ([ProductCode] ,[AccountCode] ,[FromAmt] ,[ToAmt] ,[FromYear] ,[ToYear] ,[USRID] ,[SystemDt]) VALUES ('P001' ,'A002' ,0 ,10000 ,0 ,1 ,'986532' ,GETDATE()), VALUES ('P001' ,'A002' ,10001 ,100000 ,2 ,3 ,'986532' ,GETDATE()), VALUES ('P001' ,'A002' ,100001 ,1000000 ,3 ,4 ,'986532' ,GETDATE())The above data is idealI have created check constraint on columns "FromAmt" to restrict amount values greater than equal to 0..Also created check constraint on column "ToAmt" to restrict To Amount is always greater than From Amount. (ToAmt > FromAmt)But, i just want to know whether is there any constraint that could be added to restrict last 2 data rows in the following script.INSERT INTO [dbo].[TranBreakUp] ([ProductCode] ,[AccountCode] ,[FromAmt] ,[ToAmt] ,[FromYear] ,[ToYear] ,[USRID] ,[SystemDt]) VALUES ('P001' ,'A002' ,0 ,10000 ,0 ,1 ,'986532' ,GETDATE()), VALUES ('P001' ,'A002' ,1000 ,10000 ,0 ,3 ,'986532' ,GETDATE()), VALUES ('P001' ,'A002' ,5000 ,10000 ,2 ,3 ,'986532' ,GETDATE())

Spilt List Function

Posted: 14 Feb 2013 12:08 AM PST

This must be much simplier than I'm making it...I have an unique idenifier for a person, and a space delimited field containing grades they teach, since they could teach more than one grade I'm looking to have their unique idenifier and a single grade taught. I have a split list function but how do I do this...SELECT ID, (SELECT * FROM dbo.udfSplitList(Grades,' ')FROM TableAI know this is the wrong way and I get errors, any guidance would be great!Thanks!

using OUTPUT on remote server

Posted: 26 Feb 2013 05:04 AM PST

I have the following:INSERT INTO TableB VALUES(COL1, COL2,....)OUTPUT INSERTED.COL1 INTO AUDITTable(COL1)SELECT COL1,COL2,....FROM TABLEAThis is giving me error:A remote table cannot be used as a DML target in a statement which includes an OUTPUT clause or a nested DML statement.I know that OUTPUT cannot work on remote tables.So what is the alternative to this?Thanks.

Right align Alpha field in msdb.dbo.sp_send_dbmail

Posted: 26 Feb 2013 12:15 AM PST

I'd love to have some pointers on this problem. I see postings on the 'net claiming to correct the problem but have had no success.I am using msdb.dbo.sp_send_dbmail to query an ERP system tables and email the results in a spreadsheet to users. I found what works is to email a csv file. They open in Excel beautifully - except for one email, one field. The customer PO field is defined by the system documentation as Alphabetic 20. If I select it in a query, it is right aligned. Once it is opened in Excel, the fields with any text in them are left aligned, and fields with all numbers are right aligned. I have tried using REPLICATE and CONVERT but they don't fix the problem. In fact Excel opens the Replicate cell with a width of 255.To add to the fun, the example below I pasted in from one of the emailed spreadsheets, and the formatting was changed to 100% left aligned, so I've had to add spaces to make it appear the way it appears in a spreadsheet. (EDITED TO ADDD) But it still doesn't post correctly. I added comments. I've attached a small excel file as well. Thanks in advance for any pointers. I'm new on SQL 2008 and don't recall having this problem on earlier versions. :-) Customer PO # -------------------- < (And, does anyone know how to get rid of this line of dashes?) CLE0007639 < Left AlignedCLE0007639 < Left Aligned 7840 <Right913 for Samples < Left 9693<Right1979-0 < Left 1996<Right

Deleting unused objects (tables, SP, UDF etc) not in use

Posted: 26 Feb 2013 04:24 AM PST

[b][/b]I have task in hand to clean up production database by deleting unused objects. i am thinking to use DMV's to figure out what are the objects in cache and delete remaining object with further analysis.My question is: do anyone have any script to see all unused objects present on SQL server or when it was executed last? I am looking for one script for all the objects,Any suggestion will help,This query i developed ---Below Query will give all the objects in cache memory SELECT @@SERVERNAME, 'staging' as DatabaseName, s3.name as ObjectName, s3.type, MAX(s1.max_rows) as rowscount, CONVERT(DATETIME,GETDATE()),s1.execution_count, s1.total_elapsed_time FROM sys.dm_exec_query_stats s1 cross apply sys.dm_exec_sql_text(s1.sql_handle) as s2 [b]join[/b] sys.objects s3 on ( s2.objectid = s3.object_id ) join sys.indexes i on (s3.object_id = i.object_id) join sys.schemas sch on(s3.schema_id = sch.schema_id) WHERE s3.type = 'U' GROUP BY s3.name, s3.type,s1.execution_count, s1.total_elapsed_time --Below Query will give all objects including objects not in chache memory SELECT @@SERVERNAME, 'staging' as DatabaseName, s3.name as ObjectName, s3.type, MAX(s1.max_rows) as rowscount, CONVERT(DATETIME,GETDATE()),s1.execution_count, s1.total_elapsed_time FROM sys.dm_exec_query_stats s1 cross apply sys.dm_exec_sql_text(s1.sql_handle) as s2 [b] FULL OUTER JOIN[/b] sys.objects s3 on ( s2.objectid = s3.object_id ) join sys.indexes i on (s3.object_id = i.object_id) join sys.schemas sch on(s3.schema_id = sch.schema_id) WHERE s3.type = 'U' GROUP BY s3.name, s3.type,s1.execution_count, s1.total_elapsed_timeAny suggestion???
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog