locked
Need SQL Script Help RRS feed

  • Question

  • Hi All,

    I have one scenario .I have table with following details.

    APPID   USERCODE1  USERCODE2  FILEDDATE

    1022      TLME            MKNM            12-02-2014

    1022      MKNM           SKPM            12-012014

    1022      SKPM           HIGJ               12-03-2014

    In above table i have assign the both users ( UserCODE1,USerCode2 ) field to 1022 based on the Oldest( First filed date)  date and 

    Take the oldest "UserCode1" as the 1st owner. Finally i want to migrate 4 users from both user fields. Out put would be like following .

    APPID   USERCODE( Alias)  FILEDDATE

    1022      TLME            12-01-2014

    1022      MKNM          12-01 - 2014

    1022      SKPM           12-02  -2014

    1022      HIGJ            12-03  -2014

    can any one help me on this critical task to achieve this . Thanks in advance if any one have any idea.

    Please let me know any information need on this.

    Regards,

    Sri

     

    • Moved by Olaf HelperMVP Monday, March 7, 2016 3:11 PM Moved from "Database Engine" to a more related forum
    Monday, March 7, 2016 2:57 PM

Answers

  • Hi Srinivasraokoppu,

    According to your description, please check if the following script works for you.

    ;with cte as
    (
    	SELECT APPID, [USERCODENUM], [USERCODE], FILEDDATE
    	FROM YourTable
    	UNPIVOT ([USERCODE] FOR [USERCODENUM] IN ([USERCODE1], [USERCODE2])) AS unpvt
    ),
    cte2 as
    (
    	SELECT APPID, USERCODENUM, USERCODE, FILEDDATE, ROW_NUMBER() OVER (PARTITION BY USERCODE ORDER BY FILEDDATE) AS row_num
    	FROM cte 
    )
    SELECT APPID, USERCODE, FILEDDATE
    FROM cte2
    WHERE row_num = 1
    ORDER BY FILEDDATE, USERCODENUM


    Sam Zha
    TechNet Community Support

    Tuesday, March 8, 2016 9:17 AM

All replies

  • Hi Srinivas,

    Could you double check your expected result and post correct expected result so that I can provide query 


    Srinivasarao

    Monday, March 7, 2016 3:20 PM
  • Try this on:

    DECLARE @table TABLE (APPID INT, USERCODE1 CHAR(4), USERCODE2 CHAR(4), FILEDDATE DATE)
    INSERT INTO @table (APPID, USERCODE1, USERCODE2, FILEDDATE) VALUES
    
    (1022, 'TLME', 'MKNM', '2014-12-02'),
    (1022, 'MKNM', 'SKPM', '2014-12-01'),
    (1022, 'SKPM', 'HIGJ', '2014-12-03')
    
    
    SELECT t1.*, t2.USERCODE1 AS firstOwner
      FROM @table t1
        LEFT OUTER JOIN @table t2
    	  ON t1.APPID = t2.APPID
    	  AND t2.FILEDDATE = (SELECT MIN(FILEDDATE) FROM @table WHERE APPID = t1.APPID)
    
    SELECT APPID, USERCODE1, MIN(FILEDDATE) AS FILEDATE
      FROM (
            SELECT APPID, USERCODE1, FILEDDATE
              FROM @table
             UNION
            SELECT APPID, USERCODE2, FILEDDATE
              FROM @table
           ) a
     GROUP BY APPID, USERCODE1


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Monday, March 7, 2016 4:01 PM
  • Hi Srinivas, 

    I am not sure what are expecting result here . it may be some confusion in the date field .

    I am giving the result here once again with correction. TLME , MKNM Have given the same date . but lets take the example if we have only 3 users  ( I hae removed the HIGJ user ) 

    APPID   USERCODE( Alias)  FILEDDATE

    1022      TLME            12-01-2014

    1022      MKNM         12-02 - 2014

    1022      SKPM           12-03  -2014

    I want this result like this way ... as per the Firist priority date and first User.... 

    Please let me know if you need any other information.

    Tuesday, March 8, 2016 7:01 AM
  • Hi Srinivas,

    Again results which you provided seems wrong. How come TLME filedate will show it as 12-01-2014

    APPID   USERCODE( Alias)  FILEDDATE

    1022      TLME            12-01-2014

    1022      MKNM         12-02 - 2014

    1022      SKPM           12-03  -2014

    Give me proper Input data and expected result 

    Please mark as answer if my post is helped to solve your problem
    and vote as helpful if it helped so that forum users can benefit 


    Srinivasarao G http://sqlcart.blogspot.in

    Tuesday, March 8, 2016 7:38 AM
  • Hi Srinivasraokoppu,

    According to your description, please check if the following script works for you.

    ;with cte as
    (
    	SELECT APPID, [USERCODENUM], [USERCODE], FILEDDATE
    	FROM YourTable
    	UNPIVOT ([USERCODE] FOR [USERCODENUM] IN ([USERCODE1], [USERCODE2])) AS unpvt
    ),
    cte2 as
    (
    	SELECT APPID, USERCODENUM, USERCODE, FILEDDATE, ROW_NUMBER() OVER (PARTITION BY USERCODE ORDER BY FILEDDATE) AS row_num
    	FROM cte 
    )
    SELECT APPID, USERCODE, FILEDDATE
    FROM cte2
    WHERE row_num = 1
    ORDER BY FILEDDATE, USERCODENUM


    Sam Zha
    TechNet Community Support

    Tuesday, March 8, 2016 9:17 AM