Tuesday, April 2, 2013

[T-SQL] Selection using date held as a string

[T-SQL] Selection using date held as a string

Selection using date held as a string

Posted: 01 Apr 2013 09:11 PM PDT

In our database we have a parameters table that holds a parameter type (e.g. D for date, N for number, I for integer etc) and then a varchar column which holds the parameter value.In the case of dates, the data is held in the format 'yyyy-mm-dd hh:mm:ssZ' which will convert quite hapily into a Datetime2 datatype for displayExample Data values (key_value):2013-10-29 22:59:00Z2013-04-25 23:59:00Z2013-03-06 22:59:00Z2013-03-06 22:59:00Z2013-03-06 22:59:00Z2013-03-29 22:59:00Z2013-03-06 22:59:00Z2013-04-27 23:59:00Z2013-03-06 22:59:00Z2013-04-03 23:59:00Z[code="sql"]SELECT TOP 10 key_value ,convert(datetime2,BPK.key_value) as DT2 ,convert(datetime,CONVERT(DATETIME2, PV.key_value)) AS DT FROM parameter_value PV WHERE PV.datatype = 'D'[/code]Displaying the data is not a problem, but when I try to use the data in selection criteria, it fails with a 'Conversion failed when converting date and/or time from character string.'e.g.[code="sql"]SELECT TOP 10 key_value ,convert(datetime2,BPK.key_value) as DT2 ,convert(datetime,CONVERT(DATETIME2, PV.key_value)) AS DT FROM parameter_value PV WHERE PV.datatype = 'D'and convert(datetime,CONVERT(DATETIME2, PV.key_value)) BETWEEN GETDATE() and DATEADD(d, 10, GETDATE())[/code]It doesn't seem to matter how I convert the data - to Dattime, Datetime2, Varchar etc - I always get the conversion error when the data is used in a WHERE clause.Any insights?

Help with Query

Posted: 01 Apr 2013 08:30 AM PDT

Hi I have a query which returns the followingKey segment ValueA001 001 3A001 002 4A001 003 4A002 001 2A002 002 2A002 003 1A003 001 4A003 002 4A003 003 4I would like to change the query so it only returns the first row for each Key which contains the max value for the key. The output I want with look like the followingKey segment ValueA001 002 4A002 001 2A003 001 4Thanks in advance.

Add Auto Incrementing column to my SELECT statement

Posted: 01 Apr 2013 02:34 AM PDT

Along with the data that I am pulling back from query, I also need to provide an ImportID column with the following requirements:YYMMDDGP0001, YYMMDDGP0002, YYMMDDGP0003, and so on. The 0001, 0002, and 0003 part could get big into the hundreds of thousands.I have the YYMMDDGP part down with the following expression:SELECT CONVERT(VARCHAR(6), GETDATE(), 12) + 'GP' AS [ImportID]Now I need to get the Auto Incrementing number part of this. I have been researching this trying SELECT INTO a temp table using the Identity Function and declaring different variables all with no luck. If someone could point me into the right direction on this or if you have a snippet of TSQL that will work for me, I would greatly appreciate it.Thanks Again!

Get summary of column values from a result set

Posted: 01 Apr 2013 06:08 PM PDT

good morning everyone,I have a procedure taking some input parameters that are used for a select statement. After returning the select statement I need the distinct values of 2 columns as comma seperated list.The only solution seems to be that I do the select 3 times: once for the result and once for each column I want the distinct values for. This summary is needed by the reporting tool calling the procedure for showing it to the user.Is there a better solution? Here's the (very simplified) example:[code="sql"]-- This is my base tabledeclare @CustomerOffices table ( OfficeId int, CustomerName varchar(100), PostalCode char(4) );insert into @CustomerOffices ( OfficeId, CustomerName, PostalCode ) values ( 1, 'Cust1', '6020' ) ,( 3, 'Cust5', '8010' ) ,( 4, 'Cust5', '8010' ) ,( 5, 'Cust3', '6020' ) ,( 6, 'Cust3', '5020' ) ,( 7, 'Cust2', '6020' ) ,( 8, 'Cust2', '1030' ) -- this is part of an sql procedure, taking the OfficeId as inputdeclare @FromOfficeId int, @ToOfficeId int;-- We want to have OfficeId 4 to 7set @FromOfficeId = 4;set @ToOfficeId = 7;-- This is our resultselect OfficeId, CustomerName, PostalCodefrom @CustomerOfficeswhere OfficeId between @FromOfficeId and @ToOfficeId;[/code]Now I want to have all distinct CustomerNames and all distinct PostalCodes of the result above.[code="sql"]declare @SummaryCustomerNumbers varchar(max) = '', @SummaryPostalCodes varchar(max) = '';select @SummaryCustomerNumbers += s.CustomerName + ','from ( select distinct CustomerName from @CustomerOffices where OfficeId between @FromOfficeId and @ToOfficeId ) as sorder by s.CustomerName;select @SummaryPostalCodes += s.PostalCode + ','from ( select distinct PostalCode from @CustomerOffices where OfficeId between @FromOfficeId and @ToOfficeId ) as sorder by s.PostalCode-- Remove last kommaset @SummaryCustomerNumbers = left( @SummaryCustomerNumbers, len( @SummaryCustomerNumbers ) - 1 )set @SummaryPostalCodes = left( @SummaryPostalCodes, len( @SummaryPostalCodes ) - 1 )select 'Summary' as Summary, @SummaryCustomerNumbers as CustomerNumbers, @SummaryPostalCodes as PostalCodes;[/code]This is a quite complicated way and, as I said, I have to do the same select 3 times. As the original select is done on a large table the procedure needs quite a lot time to do this.I already tried to use the compute clause but it only works for aggregate functions.For getting the whole thing faster I could save the original result in a temp table and get the distinct values from the temp table. But that's not very elegant, isn't it?Thank's for any help.

Stored Procedure using huge IO?

Posted: 01 Apr 2013 05:44 PM PDT

Hi,one of the SQL SP written like this as below. it is getting blocking every time due to waiting IO pending for compeletion... please suggest me how to resolve blocking and optimize this sp. is there any alternative way re-write this sp?each table having total records 2334567[code="sql"]Create procedure [dbo].[DeleteforRetention] @LastChangeDate DateTimeasbegindelete from dbo.OperatingLimitHighValues_tracking where OperatingLimitHighValue_PK_ID in (select a.OperatingLimitHighValue_PK_ID from OperatingLimitHighValues a where a.EffectiveTime<@LastChangeDate and EffectiveTime not in (select MAX(EffectiveTime) from OperatingLimitHighValues b where b.OperatingLimit_PK_ID=a.OperatingLimit_PK_ID)) delete from dbo.BoundaryHighValues_tracking where BoundaryHighValue_PK_ID in ( select a.BoundaryHighValue_PK_ID from BoundaryHighValues a where a.EffectiveTime < @LastChangeDate and EffectiveTime not in (select max(EffectiveTime) from BoundaryHighValues b where b.Boundary_PK_ID = a.Boundary_PK_ID)) delete from BoundaryHighValues where BoundaryHighValue_PK_ID in(select a.BoundaryHighValue_PK_ID from BoundaryHighValues a where a.EffectiveTime < @LastChangeDate and EffectiveTime not in (select max(EffectiveTime) from BoundaryHighValues b where b.Boundary_PK_ID = a.Boundary_PK_ID)) endGO[/code]Thanksananda

Transaction Isolation Level

Posted: 01 Apr 2013 09:12 AM PDT

Hello EveryoneI have a medium sized SSIS Package, with multiple steps, that call a single sproc each. I am working on a query that I am having to change the database Transaction Isolation Level approx 3/4 of the way thru the package steps.I alter the Transaction Isolation LevelSET TRANSACTION ISOLATION LEVEL SERIALIZABLE;Begin Tran BlahBlahSome Update Query is hereUpdate Query CompletesCommit Tran BlahBlahMy question is: does the Isolation Level get set back to the database default after the commit, or after the package is complete?There are other processes that use some of the tables that the queries in the spocs have already used after I have changed the Isolation Level. I am thinking that I should set it back to the database default, so that the other processes can use the table that I just updated in the Update query that is between the Begin and Commit trans.Thank you in advance for all your assistance, comments and suggestionsAndrew SQLDBA

TRIGGERS...

Posted: 01 Apr 2013 07:23 AM PDT

Hey guys, Our team ran into a problem over the weekend and Im confused over it...We are in the process of migrating to a new system! The new system does not have anything to track the history of an application. When an application gets entered and goes through each step of the workflow, it gets updated, it just updates the single row in the table for that app...Well we need a history of the app as it relates to when it goes through each step in the workflow so my boss and I came up with the idea to create triggers on that table (INSERT, UPDATE, and DELETE) so when an application gets inserted, it will write that to another table, when it goes through the steps of workflow, each step gets captured with the UPDATE trigger and written to that other table, and when the application goes through all the steps, it gets deleted from that main table and that record goes into the other table with the DELETE trigger...Now my boss has some horror stories from the past with triggers because he had used them as a FOR instead of AFTER...So we made sure all the triggers were set up as AFTER triggers meaning that after the insert, update, or delete happens THEN the trigger fires off and does what it needs to do! Well when we went live to the new system, they were noticing after they pushed an application through the workflow, it was not getting deleted! I had an error on my DELETE trigger (rookie mistake: I was calling a table from the dev environment) so it was not allowing the delete to happen! Now to me, an AFTER trigger is a trigger that will fire AFTER the statement happens...So when you go to delete something, the delete would happen then the trigger would fire off, but apparently that is not the case! It was not allowing the delete to happen b/c of my trigger failing! In research, even the way MSDN words it, it sounds as if our assumption was correct:http://msdn.microsoft.com/en-us/library/ms189799%28v=sql.100%29.aspxFOR | AFTER AFTER specifies that the DML trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger fires.AFTER is the default when FOR is the only keyword specified. AFTER triggers cannot be defined on views. You can see that our assumptions of the trigger not causing any problems were well founded. At best, this documentationis misleading. In my mind "All operations" includes the committing of the delete transaction.Can someone explain AFTER trigger to me as to why it would not allow the delete to happen b/c of trigger failure even though the trigger was an AFTER trigger?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog