Wednesday, September 4, 2013

[T-SQL] Help with this query (duplicate records)

[T-SQL] Help with this query (duplicate records)

Help with this query (duplicate records)

Posted: 04 Sep 2013 12:45 AM PDT

Hi all, i have this data: [code="sql"]DECLARE @data TABLE (tesplan_id int,tesplan_name varchar(250),tc_external_id int,tcversions_id int,tc_name varchar(250),estimated_time int,is_numeric int,ticket int,name varchar(150),prefix varchar(50),login varchar(50),Status char(1),EXECUTION_TS datetime)INSERT @dataSELECT 115905,'CLARK_5.2.0.0_Testing_Plan',3010,108838,'TC_IM6909_Verify a skid can be associated to a WIP skid',70,1,108836,'IM6909','Clark-','christian.jurupe','p','2013-08-23 10:38:54.000' UNION ALLSELECT 115905,'CLARK_5.2.0.0_Testing_Plan',3010,108838,'TC_IM6909_Verify a skid can be associated to a WIP skid',70,1,108836,'IM6909','Clark-','christian.jurupe','f','2013-08-22 16:54:43.000'select * from @data[/code]I want to stay with the last record executed (EXECUTION_TS), y mean with the '2013-08-23 10:38:54.000'.Thanks for the help!.

Converting GMT to EST

Posted: 03 Sep 2013 04:03 AM PDT

I need to write a function that converts a time from GMT to EST taking daylight savings time into consideration. On a high level I was thinking of doing the following:a) a table that holds the daylight saving dates for this year and upcoming years. does anyone know a website that has these dates published?b) if the date passed to the function is greater or equal to the date for the fall daylight saving date (i.e., 11/2013) but less than the spring daylight saving date (i.e., 3/2014) then subtract 6 from the gmt to get estc) if the date passed to the function is greater or equal to the date for the fall daylight saving date (i.e., 3/2014) but less than the spring daylight saving date (i.e., 11/2014) then subtract 5 from the gmt to get est

case when looping through column values and applying conditions depending on value

Posted: 03 Sep 2013 10:15 AM PDT

Hi Hi I want to create a sql script which loops through a column (segment) and transforms the value if it meets a certain requirements. For example -In the following table the column 'Segment' contains a string of airportcodes with departurecodes and arrivalcodes. All the legs are separated by '*' in the Segment column. One 'leg' is a pair of departure code and arrival code. The column 'Desired Result' is the one I need to create. Following is the condition.If the arrival code of the first leg is same as the departure code of the second or subsequent leg, then only one of the value is chosen. For example ID =5, SYD is arrival code for leg1 and dept code for leg2, so in the desired result SYD is repeated only once.If the arrival code is different to that of the departure code of the subsequent leg, then the 2 codes needs to be separated by a '***' in between both the codes. So for ID=10, the desired result is 'MEL/SYD/***/BNE/MEL'I tried case statements but it currently hard coded and I need to the script to be dynamic ID Segment Desired result5 MEL/SYD*SYD/MEL MEL/SYD/MEL10 MEL/SYD*BNE/MEL MEL/SYD***/BNE/MEL14 CDG/SIN*SIN/SYD*CBR/SYD*SYD/SIN*SIN/CDG CDG/SIN/SYD***/CBR/SYD/SIN/CDGthis is the code i have so far but it is hard coded.[code="sql"]DECLARE @String AS varchar(50)= 'MEL/SYD*SYD/MEL'DECLARE @str1 AS VARCHAR(50)= (SELECT SUBSTRING(@string,CHARINDEX('*',@String)+1,3))DECLARE @str2 AS VARCHAR(50) =(SELECT SUBSTRING(@string,CHARINDEX('*',@String)-3,3))SELECTCASEWHEN @str1 = @str2 THEN (SUBSTRING(@string,1,7)+ SUBSTRING(@string,CHARINDEX('*',@String)+4,4))--WHEN (condition2)--WHEN (condition3)ELSE NULL ENDfrom Airport [/code]==========Create Table Airport[code="sql"]USE [DW]GO/****** Object: Table [dbo].[Airport] Script Date: 09/03/2013 21:05:21 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[Airport]( [id] [int] NULL, [Segment] [varchar](100) NULL) ON [PRIMARY]GOSET ANSI_PADDING OFFGOINSERT [dbo].[Airport] ([id], [Segment]) VALUES (1, N'MEL/SYD*SYD/MEL')INSERT [dbo].[Airport] ([id], [Segment]) VALUES (2, N'MEL/SYD*BNE/MEL')INSERT [dbo].[Airport] ([id], [Segment]) VALUES (3, N'CDG/SIN*SIN/SYD*CBR/SYD*SYD/SIN*SIN/CDG')[/code]Any pointers in the right direction will be really helpful thanks.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog