Need Difference

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

Answer 1

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

Answer 2

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...

Answer 3

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

Answer 4

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

Answer 5

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