SQL Server - query help

Asked By Anjali agarwal on 02-Mar-12 02:24 PM
Earn up to 20 extra points for answering this tough question.

Hi All,

  I have this table and I want the following columns in the table

HuNum   DTG        SerialNumber   MH           GE   SF    TA  DA
A123    200405141541    04035     100GE 180 SF   
A123    200406090234    04055     12GE 12GE
A567    200512030235    06664     11TA        
A123    200512030235    09987     31   
H567    200512030235    02234     4DA
P123    200405241545    04051     51 general 12 task
A456    200405241545    0998      12 District 17 District
I need to parse the MH field so that 100 appears in the GE field and 180 appears in the SF field, but if both are GE like in second row then I want them to be added 24 and if it is just a single number then I want it in column GE. There are some different values in MH column like general instead of GE and I want to move geneeral to GE column and task to Ta column and district to DA column.

The resulting table will be like


HuNum   DTG        SerialNumber   MH               GE    SF    TA    DA
A123    200405141541    04035     100GE 180 SF       100    180
A123    200406090234    04055     12GE 12GE         24
A567    200512030235    06664     11TA                       11     
A123    200512030235    09987     31   
H567    200512030235    02234     4DA                             4
P123    200405241545    04051     51 general 12 task     51         12
A456    200405241545    0998      12 District 17 District                 29

any help will be appreciated.

Pat Hartman replied to Anjali agarwal on 05-Mar-12 11:06 PM
I would like to write the code to parse the MH field for you but I just don't have the time to figure out how to do it in a T-SQL stored procedure.  You also haven't defined the data well enough plus the alignment of the columns in your example is not precise.  How many items can MH contain? is there a consistant delimiter?  Your example shows none.  The developer who allowed this mess to be created should be shot at sunrise.

If you are going to the trouble to parse the field, why not create a properly normalized table?

HuNum
DTG
SerialNumber
Type
Amount

Type would be MH, GE, SF, TA, or DA.  Amount would be the numeric portion.
Sandeep Mittal replied to Anjali agarwal on 06-Mar-12 09:48 PM
Find below query and a function to filter your recorrds. Try this query and if required do modifications in the function according to your reuqirement. But for some records i think you would have to do manually

;WITH CteData AS (
  SELECT Hull_No, DTG, SerialNumber , ManHour
  FROM    tblcasrepV2
  WHERE   manhour <> 'NULL' and ISNULL(manhour,'')<> ''
)
--for area and Impact
SELECT  Hull_No, DTG, SerialNumber, ManHour
  , dbo.udf_Filter(manhour, 'GE') AS 'GE'
  , dbo.udf_Filter(manhour, 'SF') AS 'SF'
  , dbo.udf_Filter(manhour, 'TA') AS 'TA'
  , '' AS DA
FROM    CTEDATA D
ORDER   BY Hull_No, DTG, SerialNumber
 
CREATE FUNCTION dbo.[udf_Filter](
  @value varchar(max), @valuetype varchar(10)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
  SET @value = UPPER(@value)
  DECLARE @RETURN_VALUE VARCHAR(MAX)
  IF  @valuetype = 'GE'
  BEGIN
    IF ISNUMERIC(@value) = 1
      SET @RETURN_VALUE = @value
    ELSE IF (@value LIKE '%HOUR%' OR @value LIKE '%M.H%')
      SET  @RETURN_VALUE = dbo.udf_GetNumeric(@value)
    ELSE
      SET  @RETURN_VALUE = ''
  END
  ELSE IF @valuetype = 'SF'
  BEGIN
    IF (@value LIKE '%SHIP%FORCE%' OR @value LIKE '%SF%' OR @value LIKE '%S/F%')
      SET @RETURN_VALUE = dbo.udf_GetNumeric(@value)
    ELSE
      SET  @RETURN_VALUE = ''
  END
  ELSE IF @valuetype = 'TA'
  BEGIN
    IF (@value LIKE '%TA%' OR @value LIKE '%T/A%' OR @value LIKE '%TECH%ASSIST%')
      SET @RETURN_VALUE = dbo.udf_GetNumeric(@value)
    ELSE
      SET  @RETURN_VALUE = ''
  END
  RETURN @RETURN_VALUE
END
Anjali agarwal replied to Sandeep Mittal on 07-Mar-12 04:52 PM
Thanks.
Anjali agarwal replied to Sandeep Mittal on 14-Mar-12 04:13 PM

Hi,
Is it possible to filter out only one number in above query  from the row so for e.g I have

12 GE 13

15  GE

I want null in the corresponding rows for 12 GE 13, but for 15 GE, i want 15 in the GE column.

any help will be apprecaited.

Sandeep Mittal replied to Anjali agarwal on 14-Mar-12 11:28 PM
;WITH CteData AS (
    SELECT Hull_No, DTG, SerialNumber , ManHour
    FROM tblcasrepV2
    WHERE manhour <> 'NULL' and ISNULL(manhour,'')<> ''
)
SELECT Hull_No AS HUNum, DTG, SerialNumber, ManHour AS MH
       , dbo.udf_Filter(manhour, 'GE') AS 'GE'
       , dbo.udf_Filter(manhour, 'SF') AS 'SF'
       , dbo.udf_Filter(manhour, 'TA') AS 'TA'
       , '' AS DA
FROM CTEDATA D
ORDER BY Hull_No, DTG, SerialNumber
 
ALTER FUNCTION dbo.[udf_Filter]( @value varchar(max), @valuetype varchar(10))
RETURNS VARCHAR(MAX)
AS
BEGIN
    SET @value = UPPER(@value)
    DECLARE @RETURN_VALUE VARCHAR(MAX)
    IF @value LIKE '%[0-9]%[^A-Z]%[0-9]%'
    BEGIN
        SET @RETURN_VALUE = NULL
    END
    ELSE
    BEGIN
        IF @valuetype = 'GE'
        BEGIN
            IF ISNUMERIC(@value) = 1
                SET @RETURN_VALUE = @value
            ELSE IF (@value LIKE '%HOUR%' OR @value LIKE '%M.H%')
                SET @RETURN_VALUE = dbo.udf_GetNumeric(@value)
            ELSE
                SET @RETURN_VALUE = ''
        END
        ELSE IF @valuetype = 'SF'
        BEGIN
            IF (@value LIKE '%SHIP%FORCE%' OR @value LIKE '%SF%' OR @value LIKE '%S/F%')
                SET @RETURN_VALUE = dbo.udf_GetNumeric(@value)
            ELSE
                SET @RETURN_VALUE = ''
        END
        ELSE IF @valuetype = 'TA'
        BEGIN
            IF (@value LIKE '%TA%' OR @value LIKE '%T/A%' OR @value LIKE '%TECH%ASSIST%')
                SET @RETURN_VALUE = dbo.udf_GetNumeric(@value)
            ELSE
                SET @RETURN_VALUE = ''
        END
    END
    RETURN @RETURN_VALUE
END
Anjali agarwal replied to Sandeep Mittal on 15-Mar-12 04:01 PM
Thanks Sandeep!
Sandeep Mittal replied to Anjali agarwal on 15-Mar-12 10:45 PM
U r welcome.