locked
Really don't know what to call this problem, please help RRS feed

  • Question

  • Hi All,

    I am going to be very brief. I have the following table:

    And I want to see the following result:

    The logic is, If 2 records get the same MAU value, then they should get the same ID in result (case of MAU = X). Also if they have the same combination of (Patient_Code, Store_Code) they get the same ID as well (Case of MAU=Y and Z).

    The code to generate the table and it's data is attached.

    Thanks for your help in advance.

    Cheers.

    CREATE TABLE [dbo].[MAU_Test](
    	[MAU] [varchar](50) NULL,
    	[Patient_Code] [varchar](50) NULL,
    	[Store_Code] [varchar](50) NULL,
    	[RankNumber_MAU] [int] NULL,
    	[RankNumber_Patient] [int] NULL
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    INSERT [dbo].[MAU_Test] ([MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (N'X', N'p1', N's1', 1, 1)
    INSERT [dbo].[MAU_Test] ([MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (N'X', N'p1', N's2', 1, 2)
    INSERT [dbo].[MAU_Test] ([MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (N'Y', N'p2', N's1', 2, 3)
    INSERT [dbo].[MAU_Test] ([MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (N'Y', N'p2', N's2', 2, 4)
    INSERT [dbo].[MAU_Test] ([MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (N'Z', N'p2', N's2', 3, 4)
    INSERT [dbo].[MAU_Test] ([MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (N'ZZ', N'p3', N's3', 4, 5)
    


    http://thebipalace.wordpress.com

    Thursday, August 1, 2013 4:53 AM

Answers

  • Probably you need something more complex than a simple query, try this:

    --initial state
    --UPDATE MAU_Test SET RankNumber_MAU=NULL
    
    --you need an ID in the table
    
    DECLARE 
            @id INT,
    	@MAU varchar(50), 
    	@Patient_Code varchar(50), 
    	@Store_Code varchar(80),
    	@count int,
    	@prevMAU int;
        
    DECLARE patient_cursor CURSOR FOR 
    SELECT id,MAU,Patient_Code,Store_Code FROM MAU_Test WHERE RankNumber_MAU IS NULL
    ORDER BY MAU;
    
    SET @count=(SELECT MAX(RankNumber_MAU)+1 FROM MAU_Test);
    IF @count IS NULL SET @count=1
    --PRINT @count
    
    OPEN patient_cursor
    
    FETCH NEXT FROM patient_cursor 
    INTO @id, @MAU, @Patient_Code, @Store_Code
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
      --PRINT @MAU+' '+@Patient_Code+' '+@Store_Code
      
      SET @prevMAU=(SELECT TOP 1 RankNumber_MAU FROM MAU_Test WHERE MAU=@MAU OR (Patient_Code=@Patient_Code AND Store_Code=@Store_Code));
      
      IF @prevMAU IS NULL
      BEGIN
        SET @prevMAU=@count
        SET @count=@count+1
      END
      
      UPDATE MAU_Test SET RankNumber_MAU=@prevMAU WHERE id=@id
      
      --PRINT @prevMAU
      
      FETCH NEXT FROM patient_cursor 
      INTO @id, @MAU, @Patient_Code, @Store_Code
    END
    
    CLOSE patient_cursor;
    DEALLOCATE patient_cursor;

    I did not test it extensively, but it works on your example data and I think it could be a good starting point.

    ---CREATE/INSERT---

    CREATE TABLE [dbo].[MAU_Test](
    	[id] [int] IDENTITY(1,1) NOT NULL,
    	[MAU] [varchar](50) NULL,
    	[Patient_Code] [varchar](50) NULL,
    	[Store_Code] [varchar](50) NULL,
    	[RankNumber_MAU] [int] NULL,
    	[RankNumber_Patient] [int] NULL
    ) ON [PRIMARY]
    GO
    SET IDENTITY_INSERT [dbo].[MAU_Test] ON
    INSERT [dbo].[MAU_Test] ([id], [MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (1, N'X', N'p1', N's1', NULL, 1)
    INSERT [dbo].[MAU_Test] ([id], [MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (2, N'X', N'p1', N's2', NULL, 2)
    INSERT [dbo].[MAU_Test] ([id], [MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (3, N'Y', N'p2', N's1', NULL, 3)
    INSERT [dbo].[MAU_Test] ([id], [MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (4, N'Y', N'p2', N's2', NULL, 4)
    INSERT [dbo].[MAU_Test] ([id], [MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (5, N'Z', N'p2', N's2', NULL, 4)
    INSERT [dbo].[MAU_Test] ([id], [MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (6, N'Z', N'p5', N's5', NULL, 6)
    INSERT [dbo].[MAU_Test] ([id], [MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (7, N'ZZ', N'p3', N's3', NULL, 5)
    INSERT [dbo].[MAU_Test] ([id], [MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (8, N'ZZZ', N'p5', N's5', NULL, 6)
    SET IDENTITY_INSERT [dbo].[MAU_Test] OFF


    • Proposed as answer by dsmwb Thursday, August 1, 2013 8:32 AM
    • Edited by dsmwb Thursday, August 1, 2013 3:10 PM typo
    • Marked as answer by SaeedB Monday, August 5, 2013 2:51 AM
    Thursday, August 1, 2013 7:48 AM

All replies

  • Try

    select [MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], (select min([RankNumber_MAU]) from [dbo].[MAU_Test] b where b.Store_Code=a.Store_Code) ID
    from [dbo].[MAU_Test] a;
    


    Many Thanks & Best Regards, Hua Min

    • Marked as answer by SaeedB Thursday, August 1, 2013 5:33 AM
    • Unmarked as answer by SaeedB Thursday, August 1, 2013 5:56 AM
    Thursday, August 1, 2013 5:20 AM
  • Hi Hua Min, Thanks for the query. It solved my problem with a little change:

    select [MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], 
    	(select min([RankNumber_MAU]) 
    		from [dbo].[MAU_Test] b 
    		where b.Store_Code=a.Store_Code
    				AND b.Patient_Code=a.Patient_Code) ID
    from [dbo].[MAU_Test] a;


    Cheers mate,

    Saeed.


    http://thebipalace.wordpress.com


    • Edited by SaeedB Thursday, August 1, 2013 5:30 AM
    Thursday, August 1, 2013 5:26 AM
  • Try

    select [MAU], [Patient_Code], [Store_Code], (select min([RankNumber_MAU]) from [dbo].[MAU_Test] b where b.Store_Code=a.Store_Code) ID
    from [dbo].[MAU_Test] a;
    go
    


    Many Thanks & Best Regards, Hua Min

    Thursday, August 1, 2013 5:30 AM
  • Actually it turns out that what I need is more than what I explained above. In the example I provided above there are maximum 2 levels match between the records on MAU and (Patient_Code,Store_Code). I just realized there could be infinite levels of matches. See the example below:

    What I need as the result is:

    As you can see, the record with MAU = 'ZZZ' gets ID = 2 since its (Patient_Code, Store_Code) matches with those of Z.

    Please note that this could happen on multiple levels, meaning that there could be some other records having MAU = 'ZZZ' but different (PAtien_Code, Store_Code) that need to have the same ID, 2, because they match at MAU level.

    Your help is highly appreciated.

    Cheers.


    http://thebipalace.wordpress.com

    Thursday, August 1, 2013 5:55 AM
  • Try

    select [MAU], [Patient_Code], [Store_Code], case when (select count(*) from [dbo].[MAU_Test] b where b.Store_Code=a.Store_Code)=1 then (select min([RankNumber_MAU])-1 from [dbo].[MAU_Test] b where b.Store_Code=a.Store_Code) else (select min([RankNumber_MAU]) from [dbo].[MAU_Test] b where b.Store_Code=a.Store_Code) end ID
    from [dbo].[MAU_Test] a;
    


    Many Thanks & Best Regards, Hua Min

    Thursday, August 1, 2013 6:14 AM
  • That's nothing like what I need mate, please check the result of your query against what I posted as required result.
    And here comes the script to populate table:

    CREATE TABLE [dbo].[MAU_Test]( [MAU] [varchar](50) NULL, [Patient_Code] [varchar](50) NULL, [Store_Code] [varchar](50) NULL, [RankNumber_MAU] [int] NULL, [RankNumber_Patient] [int] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO INSERT [dbo].[MAU_Test] ([MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (N'X', N'p1', N's1', 1, 1) INSERT [dbo].[MAU_Test] ([MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (N'X', N'p1', N's2', 1, 2) INSERT [dbo].[MAU_Test] ([MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (N'Y', N'p2', N's1', 2, 3) INSERT [dbo].[MAU_Test] ([MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (N'Y', N'p2', N's2', 2, 4) INSERT [dbo].[MAU_Test] ([MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (N'Z', N'p2', N's2', 3, 4) INSERT [dbo].[MAU_Test] ([MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (N'Z', N'p5', N's5', 3, 6) INSERT [dbo].[MAU_Test] ([MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (N'ZZ', N'p3', N's3', 4, 5)

    INSERT [dbo].[MAU_Test] ([MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (N'ZZZ', N'p5', N's5', 5, 6)


    Cheers.


    http://thebipalace.wordpress.com


    • Edited by SaeedB Thursday, August 1, 2013 6:36 AM
    Thursday, August 1, 2013 6:35 AM
  • I see no problem by these

    CREATE TABLE [dbo].[MAU_Test](
    	[MAU] [varchar](50) NULL,
    	[Patient_Code] [varchar](50) NULL,
    	[Store_Code] [varchar](50) NULL,
    	[RankNumber_MAU] [int] NULL,
    	[RankNumber_Patient] [int] NULL
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    INSERT [dbo].[MAU_Test] ([MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (N'X', N'p1', N's1', 1, 1)
    INSERT [dbo].[MAU_Test] ([MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (N'X', N'p1', N's2', 1, 2)
    INSERT [dbo].[MAU_Test] ([MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (N'Y', N'p2', N's1', 2, 3)
    INSERT [dbo].[MAU_Test] ([MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (N'Y', N'p2', N's2', 2, 4)
    INSERT [dbo].[MAU_Test] ([MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (N'Z', N'p2', N's2', 3, 4)
    INSERT [dbo].[MAU_Test] ([MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (N'Z', N'p5', N's5', 3, 6)
    INSERT [dbo].[MAU_Test] ([MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (N'ZZ', N'p3', N's3', 4, 5)
    INSERT [dbo].[MAU_Test] ([MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (N'ZZZ', N'p5', N's5', 5, 6)
    
    select [MAU], [Patient_Code], [Store_Code], case when (select count(*) from [dbo].[MAU_Test] b where b.Store_Code=a.Store_Code)=1 then (select min([RankNumber_MAU])-1 from [dbo].[MAU_Test] b where b.Store_Code=a.Store_Code) else (select min([RankNumber_MAU]) from [dbo].[MAU_Test] b where b.Store_Code=a.Store_Code) end ID
    from [dbo].[MAU_Test] a;
    go
    
    


    Many Thanks & Best Regards, Hua Min

    Thursday, August 1, 2013 6:48 AM
  • Hi Hua Min,

    Just try this i think it will give u the out put ...

    SELECT MAU,Patient_Code,Store_Code,dense_rank() OVER( ORDER BY (Patient_Code)) ID FROM mau_test

    thanks,

    Santosh .P

    Thursday, August 1, 2013 7:07 AM
  • Probably you need something more complex than a simple query, try this:

    --initial state
    --UPDATE MAU_Test SET RankNumber_MAU=NULL
    
    --you need an ID in the table
    
    DECLARE 
            @id INT,
    	@MAU varchar(50), 
    	@Patient_Code varchar(50), 
    	@Store_Code varchar(80),
    	@count int,
    	@prevMAU int;
        
    DECLARE patient_cursor CURSOR FOR 
    SELECT id,MAU,Patient_Code,Store_Code FROM MAU_Test WHERE RankNumber_MAU IS NULL
    ORDER BY MAU;
    
    SET @count=(SELECT MAX(RankNumber_MAU)+1 FROM MAU_Test);
    IF @count IS NULL SET @count=1
    --PRINT @count
    
    OPEN patient_cursor
    
    FETCH NEXT FROM patient_cursor 
    INTO @id, @MAU, @Patient_Code, @Store_Code
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
      --PRINT @MAU+' '+@Patient_Code+' '+@Store_Code
      
      SET @prevMAU=(SELECT TOP 1 RankNumber_MAU FROM MAU_Test WHERE MAU=@MAU OR (Patient_Code=@Patient_Code AND Store_Code=@Store_Code));
      
      IF @prevMAU IS NULL
      BEGIN
        SET @prevMAU=@count
        SET @count=@count+1
      END
      
      UPDATE MAU_Test SET RankNumber_MAU=@prevMAU WHERE id=@id
      
      --PRINT @prevMAU
      
      FETCH NEXT FROM patient_cursor 
      INTO @id, @MAU, @Patient_Code, @Store_Code
    END
    
    CLOSE patient_cursor;
    DEALLOCATE patient_cursor;

    I did not test it extensively, but it works on your example data and I think it could be a good starting point.

    ---CREATE/INSERT---

    CREATE TABLE [dbo].[MAU_Test](
    	[id] [int] IDENTITY(1,1) NOT NULL,
    	[MAU] [varchar](50) NULL,
    	[Patient_Code] [varchar](50) NULL,
    	[Store_Code] [varchar](50) NULL,
    	[RankNumber_MAU] [int] NULL,
    	[RankNumber_Patient] [int] NULL
    ) ON [PRIMARY]
    GO
    SET IDENTITY_INSERT [dbo].[MAU_Test] ON
    INSERT [dbo].[MAU_Test] ([id], [MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (1, N'X', N'p1', N's1', NULL, 1)
    INSERT [dbo].[MAU_Test] ([id], [MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (2, N'X', N'p1', N's2', NULL, 2)
    INSERT [dbo].[MAU_Test] ([id], [MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (3, N'Y', N'p2', N's1', NULL, 3)
    INSERT [dbo].[MAU_Test] ([id], [MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (4, N'Y', N'p2', N's2', NULL, 4)
    INSERT [dbo].[MAU_Test] ([id], [MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (5, N'Z', N'p2', N's2', NULL, 4)
    INSERT [dbo].[MAU_Test] ([id], [MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (6, N'Z', N'p5', N's5', NULL, 6)
    INSERT [dbo].[MAU_Test] ([id], [MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (7, N'ZZ', N'p3', N's3', NULL, 5)
    INSERT [dbo].[MAU_Test] ([id], [MAU], [Patient_Code], [Store_Code], [RankNumber_MAU], [RankNumber_Patient]) VALUES (8, N'ZZZ', N'p5', N's5', NULL, 6)
    SET IDENTITY_INSERT [dbo].[MAU_Test] OFF


    • Proposed as answer by dsmwb Thursday, August 1, 2013 8:32 AM
    • Edited by dsmwb Thursday, August 1, 2013 3:10 PM typo
    • Marked as answer by SaeedB Monday, August 5, 2013 2:51 AM
    Thursday, August 1, 2013 7:48 AM
  • Thanks dsmwb, it worked beautifully.

    Cheers mate.


    http://thebipalace.wordpress.com

    Monday, August 5, 2013 2:52 AM