locked
Need SQL Script Help RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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