locked
Need Difference RRS feed

  • Question

  • User2033107836 posted

    Hello

    This is my Gridview Data 

    Date_Of_Pack CustomerName Article Color Packed_Pcs Packed_meter Packed_Avg Second_Card_No First_Card_No WB_Pcs WB_meter WB_Avg
    08 November 2017 AA Apple Red 316 1613.25 5.11 F703-17 W1027-17 600 2682 4.47
    08 November 2017 BB Apple Red 240 1213.25 5.06 F703-17 W1043-17 600 2718 4.53
    09 November 2017 CC Apple Red 922 4231 4.59 F703-17 W1053-17 627 3110 4.96
    09 November 2017 DD Apple Red 1800 8788 4.88 F703-17 W1058-17 600 3384 5.64
    10 November 2017 RR Mango Yellow 125 501 4.01 F705-17 S229A-17 125 538 4.3
    25 October 2017 MM Orange Light 25 117.5 4.7 F652-17 W830-17 515 2421 4.7
    25 October 2017 NN Orange Light 340 1541.5 4.53 F652-17 NULL NULL NULL NULL

    I want one more last column that is "Difference column" how Difference should be caluclated 

    for example : second Job card no F703-17 Difference : Packed Pcs sum and Packed Pcs meter / Packed avg : same WB_PCS sum and WB_meter / WB_avg 

    ie : for F703-17 : 15845.5 /  3278 = packed avg 4.83 : 11894 / 2427  = wbavg 4.90 then 4.83/4.90 * 100 =98.57

    Result set should come like this

    Date_Of_Pack CustomerName Article Color Packed_Pcs Packed_meter Packed_Avg Second_Card_No First_Card_No WB_Pcs WB_meter WB_Avg Difference
    08 November 2017 AA Apple Red 316 1613.25 5.11 F703-17 W1027-17 600 2682 4.47 98.57
    08 November 2017 BB Apple Red 240 1213.25 5.06 F703-17 W1043-17 600 2718 4.53
    09 November 2017 CC Apple Red 922 4231 4.59 F703-17 W1053-17 627 3110 4.96
    09 November 2017 DD Apple Red 1800 8788 4.88 F703-17 W1058-17 600 3384 5.64
    10 November 2017 RR Mango Yellow 125 501 4.01 F705-17 S229A-17 125 538 4.3 93.03
    25 October 2017 MM Orange Light 25 117.5 4.7 F652-17 W830-17 515 2421 4.7 96.59
    25 October 2017 NN Orange Light 340 1541.5 4.53 F652-17 NULL NULL NULL NULL

    Thursday, November 23, 2017 11:54 AM

Answers

  • User991499041 posted

    Hi Asp.ambur,

    Hello ZXJ 

    Thanks For Code

    Can we show the difference data one time coz its keep on repeating for example

    Firs four rows combine and show only one time 98.57

    5th row 93.26

    Sixth and seventh row combine and show only one time 96.81

    Screenshot

    how to do.. 

    Thanking You

    You can use below sql scriprt

    declare @tb table
    (Date_Of_Pack nvarchar(50),
    CustomerName nvarchar(10),
    Articlen varchar(10),
    Color nvarchar(10),
    Packed_Pcs nvarchar(10),
    Packed_meter decimal(8,2),
    Packed_Avg decimal(8,2),
    Second_Card_No varchar(20),
    First_Card_No varchar(20),
    WB_Pcs nvarchar(10),
    WB_meter nvarchar(10),
    WB_Avg nvarchar(10)
    )
    
    insert into @tb values
    ('08 November 2017',	'AA',	'Apple',	'Red',	316,	1613.25,	5.11,	'F703-17',	'W1027-17',	600,	2682,	4.47),
    ('08 November 2017',	'BB',	'Apple',	'Red',	240,	1213.25,	5.06,	'F703-17',	'W1043-17',	600,	2718,	4.53),
    ('09 November 2017',	'CC',	'Apple',	'Red',	922,	4231,	4.59,	'F703-17',	'W1053-17',	627,	3110,	4.96),
    ('09 November 2017',	'DD',	'Apple',	'Red',	1800,	8788,	4.88,	'F703-17',	'W1058-17',	600,	3384,	5.64),
    ('10 November 2017',	'RR',	'Mango',	'Yellow',	125,	501,	4.01,	'F705-17',	'S229A-17',	125,	538,	4.3),
    ('25 October 2017',	'MM',	'Orange',	'Light',	25,	117.5,	4.7,	'F652-17',	'W830-17',	515,	2421,	4.7),
    ('25 October 2017',	'NN',	'Orange',	'Light',	340,	1541.5,	4.53,	'F652-17',	NULL,	NULL,	NULL,	NULL)
    
    
    ;WITH CTE
    AS
    (
    select *,
    cast(
    cast(
    (sum(isnull(cast(Packed_meter as decimal(12,2)),0)) OVER(PARTITION BY Second_Card_No)
     / sum(isnull(cast(Packed_Pcs as decimal(12,2)),0)) OVER(PARTITION BY Second_Card_No)
    ) as decimal(8,2)
    )
    /
    cast(
    (sum(isnull(cast(WB_meter as decimal(12,2)),0)) OVER(PARTITION BY Second_Card_No)
    / sum(isnull(cast(WB_Pcs as decimal(12,2)),0)) OVER(PARTITION BY Second_Card_No)
    ) as decimal(8,2) 
    )*100 as decimal(8,2)
    )
    as [Difference]
    from @tb
    )
    
    SELECT Date_Of_Pack,
    CustomerName,
    Articlen,
    Color,
    Packed_Pcs,
    Packed_meter,
    Packed_Avg,
    Second_Card_No,
    First_Card_No,
    WB_Pcs,
    WB_meter,
    WB_Avg,
    CASE ROW_NUMBER() OVER(PARTITION BY [Difference] ORDER BY(SELECT 1))
        WHEN 1 THEN [Difference]
        ELSE NULL
      END AS [Difference]
    FROM CTE
    order by Articlen

    Regards,

    zxj

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 28, 2017 1:53 AM

All replies

  • User-707554951 posted

    Hi asp.ambur,

    I want one more last column that is "Difference column" how Difference should be caluclated 

    for example : second Job card no F703-17 Difference : Packed Pcs sum and Packed Pcs meter / Packed avg : same WB_PCS sum and WB_meter / WB_avg 

    ie : for F703-17 : 15845.5 /  3278 = packed avg 4.83 : 11894 / 2427  = wbavg 4.90 then 4.83/4.90 * 100 =98.57

    After understand your description above, I still don’t know how you  get 98.57, 93.03,96.59

    So, would you please provide us with detailed expression about this?

    Best regards

    Cathy

    Friday, November 24, 2017 8:04 AM
  • User2033107836 posted
    Hello Cathy

    In My Grid view one column Second_Card_No

    In Second_Card_No I have three no

    F703-17
    F705-17
    F652-17

    Now sum each card no Packed Pcs and Packed meter
    Then divide 15845.5 / 3278 = packed avg 4.83

    Same for wb_pcs and wb_meter ie 11894 / 2427 = wbavg 4.90

    Now take packed _avg/wb_avg *100

    You Will get Difference Output...
    Friday, November 24, 2017 9:35 AM
  • User991499041 posted

    Hi asp.ambur,

    In my opinion, I'll compute the results via SQL and then consider how to display it in the GridView.

    declare @tb table
    (Date_Of_Pack nvarchar(50),
    CustomerName nvarchar(10),
    Articlen varchar(10),
    Color nvarchar(10),
    Packed_Pcs nvarchar(10),
    Packed_meter decimal(8,2),
    Packed_Avg decimal(8,2),
    Second_Card_No varchar(20),
    First_Card_No varchar(20),
    WB_Pcs nvarchar(10),
    WB_meter nvarchar(10),
    WB_Avg nvarchar(10)
    )
    
    insert into @tb values
    ('08 November 2017',	'AA',	'Apple',	'Red',	316,	1613.25,	5.11,	'F703-17',	'W1027-17',	600,	2682,	4.47),
    ('08 November 2017',	'BB',	'Apple',	'Red',	240,	1213.25,	5.06,	'F703-17',	'W1043-17',	600,	2718,	4.53),
    ('09 November 2017',	'CC',	'Apple',	'Red',	922,	4231,	4.59,	'F703-17',	'W1053-17',	627,	3110,	4.96),
    ('09 November 2017',	'DD',	'Apple',	'Red',	1800,	8788,	4.88,	'F703-17',	'W1058-17',	600,	3384,	5.64),
    ('10 November 2017',	'RR',	'Mango',	'Yellow',	125,	501,	4.01,	'F705-17',	'S229A-17',	125,	538,	4.3),
    ('25 October 2017',	'MM',	'Orange',	'Light',	25,	117.5,	4.7,	'F652-17',	'W830-17',	515,	2421,	4.7),
    ('25 October 2017',	'NN',	'Orange',	'Light',	340,	1541.5,	4.53,	'F652-17',	NULL,	NULL,	NULL,	NULL)
    
    select *,
    cast(
    cast(
    (sum(isnull(cast(Packed_meter as decimal(12,2)),0)) OVER(PARTITION BY Second_Card_No)
     / sum(isnull(cast(Packed_Pcs as decimal(12,2)),0)) OVER(PARTITION BY Second_Card_No)
    ) as decimal(8,2)
    )
    /
    cast(
    (sum(isnull(cast(WB_meter as decimal(12,2)),0)) OVER(PARTITION BY Second_Card_No)
    / sum(isnull(cast(WB_Pcs as decimal(12,2)),0)) OVER(PARTITION BY Second_Card_No)
    ) as decimal(8,2) 
    )*100 as decimal(8,2)
    )
    
    as [Difference]
    
    from @tb
    order by Articlen

    Regards,

    zxj

    Friday, November 24, 2017 9:48 AM
  • User2033107836 posted

    Hello ZXJ 

    Thanks For Code

    Can we show the difference data one time coz its keep on repeating for example

    Firs four rows combine and show only one time 98.57

    5th row 93.26

    Sixth and seventh row combine and show only one time 96.81

    Screenshot

    how to do.. 

    Thanking You

    Monday, November 27, 2017 10:15 AM
  • User991499041 posted

    Hi Asp.ambur,

    Hello ZXJ 

    Thanks For Code

    Can we show the difference data one time coz its keep on repeating for example

    Firs four rows combine and show only one time 98.57

    5th row 93.26

    Sixth and seventh row combine and show only one time 96.81

    Screenshot

    how to do.. 

    Thanking You

    You can use below sql scriprt

    declare @tb table
    (Date_Of_Pack nvarchar(50),
    CustomerName nvarchar(10),
    Articlen varchar(10),
    Color nvarchar(10),
    Packed_Pcs nvarchar(10),
    Packed_meter decimal(8,2),
    Packed_Avg decimal(8,2),
    Second_Card_No varchar(20),
    First_Card_No varchar(20),
    WB_Pcs nvarchar(10),
    WB_meter nvarchar(10),
    WB_Avg nvarchar(10)
    )
    
    insert into @tb values
    ('08 November 2017',	'AA',	'Apple',	'Red',	316,	1613.25,	5.11,	'F703-17',	'W1027-17',	600,	2682,	4.47),
    ('08 November 2017',	'BB',	'Apple',	'Red',	240,	1213.25,	5.06,	'F703-17',	'W1043-17',	600,	2718,	4.53),
    ('09 November 2017',	'CC',	'Apple',	'Red',	922,	4231,	4.59,	'F703-17',	'W1053-17',	627,	3110,	4.96),
    ('09 November 2017',	'DD',	'Apple',	'Red',	1800,	8788,	4.88,	'F703-17',	'W1058-17',	600,	3384,	5.64),
    ('10 November 2017',	'RR',	'Mango',	'Yellow',	125,	501,	4.01,	'F705-17',	'S229A-17',	125,	538,	4.3),
    ('25 October 2017',	'MM',	'Orange',	'Light',	25,	117.5,	4.7,	'F652-17',	'W830-17',	515,	2421,	4.7),
    ('25 October 2017',	'NN',	'Orange',	'Light',	340,	1541.5,	4.53,	'F652-17',	NULL,	NULL,	NULL,	NULL)
    
    
    ;WITH CTE
    AS
    (
    select *,
    cast(
    cast(
    (sum(isnull(cast(Packed_meter as decimal(12,2)),0)) OVER(PARTITION BY Second_Card_No)
     / sum(isnull(cast(Packed_Pcs as decimal(12,2)),0)) OVER(PARTITION BY Second_Card_No)
    ) as decimal(8,2)
    )
    /
    cast(
    (sum(isnull(cast(WB_meter as decimal(12,2)),0)) OVER(PARTITION BY Second_Card_No)
    / sum(isnull(cast(WB_Pcs as decimal(12,2)),0)) OVER(PARTITION BY Second_Card_No)
    ) as decimal(8,2) 
    )*100 as decimal(8,2)
    )
    as [Difference]
    from @tb
    )
    
    SELECT Date_Of_Pack,
    CustomerName,
    Articlen,
    Color,
    Packed_Pcs,
    Packed_meter,
    Packed_Avg,
    Second_Card_No,
    First_Card_No,
    WB_Pcs,
    WB_meter,
    WB_Avg,
    CASE ROW_NUMBER() OVER(PARTITION BY [Difference] ORDER BY(SELECT 1))
        WHEN 1 THEN [Difference]
        ELSE NULL
      END AS [Difference]
    FROM CTE
    order by Articlen

    Regards,

    zxj

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 28, 2017 1:53 AM