Thursday, April 18, 2013

[T-SQL] avoiding duplicates in comma separated values column

[T-SQL] avoiding duplicates in comma separated values column

avoiding duplicates in comma separated values column

Posted: 18 Apr 2013 12:36 AM PDT

Hi Guys,I am working on stored proc to update master table from temp table and getting following issue. [b]temp_table[/b]sa, Logger, Reader[b]master_table[/b]sa, Reader[b]Reqired_Output_On_master_table [/b]sa, Reader, LoggerSo i only want to add comma seperated value when it is not already present in master table. (to avoid duplicate entry). i think i have to use CHARINDEX but don't know exact implementation. Any help from you guys will be gr8...Thanks,

Is there a TVP equivalent to an optional parameter?

Posted: 18 Apr 2013 01:15 AM PDT

I'm trying to help my client solve an issue with frequent "breaking changes" to procedures that use table-valued-parameters. The calling code is in a highly branched environment, but due to resource constraints, there is only one SQL Server database environment for all the development branches and the production-equivalent testing environment.The resulting issues have been dealt with largely through the use of optional parameters. The calling code doesn't need to know about optional parameters that it doesn't use.Recently, one of the business lines implemented a number of procedures using TVPs. This particular product is unique in the business for processing large batches, and it saw significant performance gains from the TVPs. Usage of TVPs is new for this client.But now we are dealing with the problem of needing "optional" columns on the table definition. Different development branches need different optional columns, and they are headed out to production at different times. But there's no such thing as an optional column defintion. The calling code (C#) has to know about the new column, even if it's nullable. Is there a technical solution to this problem? Anything that I'm missing?

TOP 25% rows by Weightage

Posted: 17 Apr 2013 02:10 AM PDT

I have a table as below. I need to find the members which are in the bottom quartile (25%) of the weights in each class[code="other"]ClassID MemberID Weight A 1 10A 2 20A 3 40A 5 25A 6 15A 8 10B 1 50B 2 10B 3 60B 6 400[/code]For above example, total weight of class A is 120. If I order by weight and pick the top ones such that their summed weight comprise less than or equal to 25% of total weight (25% of 120 = 30 for class A)I would pick Members 1 and 8. If I include member 6, it would exceed 25% (30)The output I need is[code="other"]ClassID MemberID Weight A 1 10A 8 10B 1 50B 2 10B 3 60[/code]What would be the best way of achieving this in TSQL (Sql server 2008)?Hope the question is clear

Trigger that collects data from two tables

Posted: 12 Apr 2013 08:35 AM PDT

Hi,Is it possible to create an After Update trigger on table A that can gather both the delete and insert data for the After Update from columns A.1, A.2, A.3 when A.3 is updated to NULL? I need that plus the delete and insert data from columns B.1 and B.2 from table B as well when column A.3 is updated and set to NULL? I have the trigger below that will get me what I need from table A but is it possible to also get the data from table B from this trigger? In other words, I'm trying to figure out how to populate the Table B OldWorkFlowStepId and NewWorkFlowStepid columns that are created in the CONTAINER_DEBUG table and declared as variables. The OldWorkFlowStepId value needs to be taken when the delete.CurrentStatusId is taken from the first table and the NewWorkflowStepId value needs to be taken when the insert.CurrentStatusId is taken from the first table. How would I go about that? I am trying to troubleshoot a column that is getting set to NULL when it shouldn't and I'm trying to trap exactly where in the workflow this is happening.[code="sql"]DROP TABLE CONTAINER_DEBUGGOCREATE TABLE CONTAINER_DEBUG( ContainerId CHAR(16) ,ContainerName VARCHAR(256) ,OldCurrentStatusId CHAR(16) ,NewCurrentStatusId CHAR(16) ,OldLastCompletedTaskId CHAR(16) ,NewLastCompletedTaskId CHAR(16) ,OldWorkflowStepId CHAR(16) ,NewWorkflowStepId CHAR(16) ,LastActivityDate DATETIME )GODROP TRIGGER CONTAINER_TRG_BUGOCREATE TRIGGER CONTAINER_TRG_BUON CONTAINERAFTER UPDATE AS IF ( UPDATE (CurrentStatusId) )BEGIN -- DECLARE @OldCurrentStatusId CHAR(16); DECLARE @NewCurrentStatusId CHAR(16); DECLARE @OldLastCompletedTaskId CHAR(16); DECLARE @NewLastCompletedTaskId CHAR(16); DECLARE @OldWorkflowStepId CHAR(16); DECLARE @NewWorkflowStepId CHAR(16); -- SELECT @OldCurrentStatusId FROM deleted; -- SELECT @NewCurrentStatusId FROM inserted; -- IF ( ISNULL(@OldCurrentStatusId,'XXX') <> 'XXX' AND ISNULL(@NewCurrentStatusId,'XXX') = 'XXX' ) -- BEGIN -- INSERT INTO CONTAINER_DEBUG ( ContainerId ,ContainerName ,OldCurrentStatusId ,NewCurrentStatusId ,LastActivityDate ) SELECT i.ContainerId ,i.ContainerName ,d.CurrentStatusId ,i.CurrentStatusId ,i.LastActivityDate FROM Inserted i INNER JOIN Deleted d ON i.ContainerId = d.ContainerId -- RAISERROR ('CurrentStatusId is set to NULL - Aborting...', 16, 10); -- END; --END;GO[/code]

query Suggestion please

Posted: 17 Apr 2013 05:40 AM PDT

My requirement is to update the ApplicationID column if Server matches and Insert the server name and ApplicationID data if server has more than one applicationID.Here is the case:Server can have multiple App ID'swe need to check as follws.1. Check the server name matches or not. 2. If server matches check the corresponding App ID. If app ID is null then update3. If server matches and has multiple APPID's insert servername and AppIDex:ApServer-----------Server|||||AppIDAAABBB|||||1000AAABBB|||||1005ASFQRE|||||1001AGSFSD|||||1002CCCDDD|||||1003CCCDDD|||||1009LUT2013------------Server|||||AppIDAAABBB|||||NULLASFQRE|||||NULLAGSFSD|||||1002CCCDDD|||||NULLOUTPUT TABLE LUT2013 SHOULD LOOKE LIKE BELOW.Server|||||AppIDAAABBB|||||1000AAABBB|||||1005AGSFSD|||||1002CCCDDD|||||1003CCCDDD|||||1009Please give me idea to start working on this requirement.thanksAswin

Unexpected results in Full Text Search in SQL Server 2008R2

Posted: 17 Apr 2013 04:33 AM PDT

Hi All,I have a full text search SP to search a field that contains English&French. But the result of the search was not expected. Is it because that contains multi languages. The catalog I defined in Language for Word Breaker was "Neutral". Here is my Full text search query below (SQL Server 2008 R2 -64bits)Declare @sSearchString nvarchar(1000), @sLanguage varchar(30), @iRank intset @sLanguage = 'Neutral'set @iRank = 100set @sSearchString = N'"Code de procédure civile"'SELECT t1.test_id, t1.Rtn1, t1.Rtn2, t2.s_Name, t2.s_Address, t_Master.rank as RankingFROM test1_Master t1 Inner JOIN test2_Client t2ON t1.test_id = t2.test_id Inner JOIN test_Table3 t3ON t2.legis_id = t3.legis_idinner join FREETEXTTABLE (test1_Master, (Rtn1,Rtn2),@sSearchString, Language @sLanguage, @iRank) as t_Masteron t1.test_id = t_Master.[key]where t1.Rtn1 is not NULL and t1.Rtn2 is not NULLunion allSELECT t1.test_id, t1.Rtn1, t1.Rtn2, t2.s_Name, t2.s_Address, test_Address.rank as RankingFROM test1_Master t1 Inner JOIN test2_Client t2ON t1.test_id = t2.test_id Inner JOIN test_Table3 t3ON t2.legis_id = t3.legis_idinner join FREETEXTTABLE (test2_Client, (s_Name,s_Address),@sSearchString, Language @sLanguage, @iRank) as test_Addresson t1.test_id = test_Address.[key]where t2.s_Name is not NULL and t2.s_Address is not NULLif I changed the Language to English or French, the number of rows returned were different but was not able to predict what the returned results.btw, I am new to Fulltext search programming. Hope the expert can give me some suggestion. Also I would like to have google search type of results. Can someone point out my error and would be great if you have some sample scripts. Thanks in Advance!Jim

can we compare time AM/PM time format?

Posted: 17 Apr 2013 01:34 AM PDT

Hi,Can we compare AM PM date format?Example datecolumn > 12:00PM and datecolumn < 1:00PM.Here datecolumn is varchar. Pleae suggest any idea?ThanksAbhas.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog