none
Repitition of sql records

    Question

  • Budget_Revenue Budget_Traffic Actual_Revenue Actual_Traffic
    0 0 2347585 295
    0 0 8796 6
    0 0 107772 18
    0 0 153940 10
    0 0 284510 13
    3772 1 2347585 295
    3772 1 8796 6
    3772 1 107772 18
    3772 1 153940 10
    3772 1 284510 13
    143355 19 2347585 295
    143355 19 8796 6
    143355 19 107772 18
    143355 19 153940 10
    143355 19 284510 13
    7545 1 2347585 295
    7545 1 8796 6
    7545 1 107772 18
    7545 1 153940 10
    7545 1 284510 13
    0         NULL 2347585 295
    0           NULL 8796 6
    0      NULL 107772 18
    0         NULL 153940 10
    0      NULL 284510 13
    Monday, November 19, 2012 4:26 AM

Answers

All replies

  • Budget_Revenue Budget_Traffic Actual_Revenue Actual_Traffic
    0 0 2347585 295
    0 0 8796 6
    0 0 107772 18
    0 0 153940 10
    0 0 284510 13
    3772 1 2347585 295
    3772 1 8796 6
    3772 1 107772 18
    3772 1 153940 10
    3772 1 284510 13
    143355 19 2347585 295
    143355 19 8796 6
    143355 19 107772 18
    143355 19 153940 10
    143355 19 284510 13
    7545 1 2347585 295
    7545 1 8796 6
    7545 1 107772 18
    7545 1 153940 10
    7545 1 284510 13
    0         NULL 2347585 295
    0           NULL 8796 6
    0      NULL 107772 18
    0         NULL 153940 10
    0      NULL 284510 13

    I have to get like this ,

    Budget_Revenue Budget_Traffic Actual_Revenue Actual_Traffic
    0 0 2347585 295
    3772 1 8796 6
    143355 19 107772 18
    7545 1 153940 10
    0                               NULL 284510 13

    is this possible using sql ??

    Monday, November 19, 2012 4:28 AM
  • Hi,

    Try with the below Query.

    WITH CTE as (
    SELECT ROW_NUMBER() OVER(PARTITION BY Budget_Revenue ORDER BY Budget_Revenue) RN ,Budget_Revenue,Budget_Traffic,Actual_Revenue,Actual_Traffic
    FROM #TableNname
    )
    SELECT * FROM CTE WHERE RN=1


    PS.Shakeer Hussain

    Monday, November 19, 2012 6:32 AM
  • Hi,

    Find out duplicate records :

    SELECT Budget_Revenue, Budget_Traffic, Actual_Revenue, Actual_Traffic ,count(*)
    FROM tableName
    GROUP BY Budget_Revenue, Budget_Traffic, Actual_Revenue, Actual_Traffic
    HAVING count(Budget_Revenue) > 1

    Remove duplicate records

    -- Make a column
    ALTER TABLE tableName ADD AUTOID INT IDENTITY(1,1) 
     
    SELECT * FROM tableName WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _
    	FROM tableName 
    	GROUP BY Budget_Revenue, Budget_Traffic, Actual_Revenue, Actual_Traffic)
    -- Remove 
    DELETE FROM tableName WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _
    	FROM tableName GROUP BY Budget_Revenue, Budget_Traffic, Actual_Revenue, Actual_Traffic) 
    SELECT Budget_Revenue, Budget_Traffic, Actual_Revenue, Actual_Traffic ,count(*)
    FROM tableName
    GROUP BY Budget_Revenue, Budget_Traffic, Actual_Revenue, Actual_Traffic
    HAVING count(Budget_Revenue) > 1

    Notes : Because data base defination is not present so you may face problem ,try to set group properly as your table defination.other wise I checked syntax it working .Take it as example.

    Best of luck


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    • Proposed as answer by Basit Farooq Monday, November 19, 2012 12:11 PM
    Monday, November 19, 2012 6:59 AM
  • try this .it worked for me

    with cte as(
    select a.rn,b.Budget_Revenue,Budget_Traffic,Actual_Revenue,Actual_Traffic,
    ROW_NUMBER() OVER(PARTITION BY b.Budget_Revenue ORDER BY b.Budget_Revenue) RoN from
    (select  budget_revenue,ROW_NUMBER() over (ORDER BY Budget_Revenue) rn from temp group by budget_revenue) a
    inner join temp b
    on a.budget_revenue=b.budget_revenue )
    select Budget_Revenue,Budget_Traffic,Actual_Revenue,Actual_Traffic  from cte where rn=ron

    Monday, November 19, 2012 8:30 AM
  • Try this out,

    Declare @Budget Table
    (
    Budget_Revenue Int,
    Budget_Traffic Int,
    Actual_Revenue Int ,
    Actual_Traffic Int
    )
    Insert @Budget Values
    (0, 0, 2347585, 295),
    (0, 0, 8796, 6),
    (0, 0, 107772, 18),
    (0, 0, 153940, 10),
    (0, 0, 284510, 13),
    (3772, 1, 2347585, 295),
    (3772, 1, 8796, 6),
    (3772, 1, 107772, 18),
    (3772, 1, 153940, 10),
    (3772, 1, 284510, 13),
    (143355, 19, 2347585, 295),
    (143355, 19, 8796, 6),
    (143355, 19, 107772, 18),
    (143355, 19, 153940, 10),
    (143355, 19, 284510, 13),
    (7545, 1, 2347585, 295),
    (7545, 1, 8796 ,6),
    (7545, 1, 107772 ,18),
    (7545, 1, 153940 ,10),
    (7545, 1, 284510 ,13),
    (0, NULL, 2347585,295),
    (0, NULL, 8796 ,6),
    (0, NULL, 107772 ,18),
    (0, NULL, 153940 ,10),
    (0, NULL, 284510 ,13)

    ;With Cte As
    (
    Select Dense_Rank() Over (Order By (Select Case When ForSort = 295 then 0 Else ForSort End))Rnk,*
    From (
    Select T1.Budget_Revenue,T1.Budget_Traffic,Stuff(
    (Select ','+ Convert(Varchar(10),T2.Actual_Revenue) 
    From @Budget T2 
      Where T1.Budget_Revenue = T2.Budget_Revenue 
    And IsNull(T1.Budget_Traffic,1010) = IsNull(T2.Budget_Traffic,1010)
    For Xml Path('')
    ),1,1,'') As Actual_Revenue,
       
    Stuff(
    (Select ','+ Convert(Varchar(10),T2.Actual_Traffic) 
      From @Budget T2 
         Where T1.Budget_Revenue = T2.Budget_Revenue 
       And IsNull(T1.Budget_Traffic,1010) = IsNull(T2.Budget_Traffic,1010)
      For Xml Path('')
    ),1,1,'') As Actual_Traffic,T1.Actual_Traffic ForSort
     
    From @Budget t1
    ) T1

    ),
    Budget_New As(
    Select Row_Number() Over (Order By (Select Null))RwID,Budget_Revenue,Budget_Traffic,Actual_Revenue,Actual_Traffic From Cte
    Where Rnk=1
    )
    , Cte1 As
    (
    Select RwId,
           Budget_Revenue
           ,Budget_Traffic
           ,Actual_Revenue + ','Actual_RevenueCm
           ,Actual_Traffic + ',' Actual_TrafficCm
           ,Substring(Actual_Revenue,1,CharIndex(',',Actual_Revenue)-1) As Actual_Revenue
           ,Substring(Actual_Traffic,1,CharIndex(',',Actual_Traffic)-1) As Actual_Traffic
           ,CharIndex(',',Actual_Revenue) As ForActual_Revenue
           ,CharIndex(',',Actual_Traffic) As ForActual_Traffic
      From Budget_New
    Where RwId = 1
    Union All
    Select 
    T1.RwId,
    T1.Budget_Revenue
    ,T1.Budget_Traffic
    ,Substring(T2.Actual_RevenueCm,ForActual_Revenue+1,Len(T1.Actual_Revenue)) 
            ,Substring(T2.Actual_TrafficCm,ForActual_Traffic+1,Len(T1.Actual_Traffic))
            ,Substring(T2.Actual_RevenueCm,ForActual_Revenue+1,CharIndex(',',Substring(T2.Actual_RevenueCm,ForActual_Revenue+1,Len(T1.Actual_Revenue)))-1)
            ,Substring(T2.Actual_TrafficCm,ForActual_Traffic+1,CharIndex(',',Substring(T2.Actual_TrafficCm,ForActual_Traffic+1,Len(T1.Actual_Traffic)))-1)
            ,CharIndex(',',Substring(T2.Actual_RevenueCm,ForActual_Revenue+1,Len(T1.Actual_Revenue)) ) As ForActual_Revenue
            ,CharIndex(',',Substring(T2.Actual_TrafficCm,ForActual_Traffic+1,Len(T1.Actual_Traffic)) ) As ForActual_Traffic
    From Budget_New T1
    Join Cte1 T2 On T1.RwId = T2.RwId+1
    )
    Select Budget_Revenue,Budget_Traffic,Actual_Revenue,Actual_Traffic From Cte1


    Please have look on the comment

    Monday, November 19, 2012 9:30 AM
  • Hi,

    Try with the below Query.

    WITH CTE as (
    SELECT ROW_NUMBER() OVER(PARTITION BY Budget_Revenue ORDER BY Budget_Revenue) RN ,Budget_Revenue,Budget_Traffic,Actual_Revenue,Actual_Traffic
    FROM #TableNname
    )
    SELECT * FROM CTE WHERE RN=1


    PS.Shakeer Hussain

    syed ,

    above query will returrn where rn=1 whears problem statment says that for ist group 1st record and second group second record and so on.please correct if my understanding is not right?

    Monday, November 19, 2012 10:12 AM
  • Hi Nishink,

    My Query is correct and gives the correct result according to the output posted.


    PS.Shakeer Hussain

    Monday, November 19, 2012 10:16 AM
  •  

    Hi,

    Use distinct and group by

    Budget_Revenue  Budget_Traffic Actual_Revenue Actual_Traffic

    While not much details are  available,, there may be more optimizations.

     

    Regards.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help , or you may vote-up a helpful post

    Monday, November 19, 2012 10:56 AM
  • Hi all,

    I have solved by using where condition and matched all the ids of it , thaks for all

    Tuesday, November 20, 2012 10:17 AM