Issue with basic calculation

Transact-SQL
1

Hi, I am new to SQL programming. Just looking to amend some queries. Basically I want to eliminate part of results when specific value is gather than 98% see query below:

USE [Counts];

DECLARE @startDateTime datetime =DATEADD(HOUR, -12, GETDATE())
DECLARE @endDateTime datetime =GETDATE()

;WITH cteData AS (

   SELECT

         [Scanner],

         [OldDateTime] = LAG([DateAndTime]) OVER (PARTITION BY [Scanner] ORDER BY [DateAndTime]),

         [OldNoRead] = LAG([NoRead]) OVER (PARTITION BY [Scanner] ORDER BY [DateAndTime]),

         [OldNoData] = LAG([NoData]) OVER (PARTITION BY [Scanner] ORDER BY [DateAndTime]),

         [OldTotal] = LAG([Total]) OVER (PARTITION BY [Scanner] ORDER BY [DateAndTime]),

         [DateAndTime] AS [DateTime],

         [NoRead],

         [NoData],

         [Total]

   FROM [Counts].[dbo].ScannerCounts

   WHERE ([DateAndTime] BETWEEN @startDateTime AND @endDateTime)

),

cteSummarisedData AS (

   SELECT

         [Scanner],

         [OldDateTime],

         [DateTime],

         CASE WHEN [NoRead] >= [OldNoRead] THEN [NoRead] - [OldNoRead] END AS [NoRead],

         CASE WHEN [NoData] >= [OldNoData] THEN [NoData] - [OldNoData] END AS [NoData],

         CASE WHEN [Total] >= [OldTotal] THEN [Total] - [OldTotal] ELSE ((65536 - [OldTotal]) + [Total]) END AS [Total]

   FROM cteData

   WHERE [OldDateTime] IS NOT NULL

)

SELECT

   [Scanner],

   SUM([NoRead]) AS [No Read],

   SUM([NoData]) AS [No Data],

   SUM([Total] - [NoRead]) AS [Good Read],

   SUM([Total] - [NoData]) AS [Good Data],

   SUM([Total]) AS [Total],

   SUM(CAST([Total] - [NoRead] AS decimal)) / SUM([Total]) * 100 AS **GR**,

   SUM(CAST([Total] - [NoData] AS decimal)) / SUM([Total]) * 100 AS **GD**

FROM cteSummarisedData

GROUP BY [Scanner]

ORDER BY [No Read] DESC

I want to eliminate GR and GD from results when they are higher than 98%

I just started with SQL and this code is just causing my headache.
I will be grateful if anybody point me in a good direction

Cheers

2

Welcome!

Please provide sample data for ScannerCounts so we dont have headache also :slight_smile:

3
Scanner No Read No Data Good Read Good Data Total GR GD
15AF1_2 912 2 7163 8073 8075 88.7058 99.9752
15AG1_1 303 2 5666 5967 5969 94.9237 99.9664
22EB1_S 243 0 2686 2929 2929 91.7036 100
15AF1_1 208 2 7867 8073 8075 97.4241 99.9752
15AH1_2 162 0 3661 3823 3823 95.7624 100
15AA2_1 158 0 7737 7895 7895 97.9987 100
19DD1 157 0 1650 1807 1807 91.3115 100
11FA1 142 6 389 525 531 73.258 98.87
15AG1_2 124 1 5845 5968 5969 97.9226 99.9832
13BA1 122 0 709 831 831 85.3188 100
22EA3_2_S 122 5 2801 2918 2923 95.8262 99.8289
13FA1 113 0 691 804 804 85.9452 100
15AH1_1 113 0 3710 3823 3823 97.0442 100
14GC1 97 0 5529 5626 5626 98.2758 100
13DA1 95 3 604 696 699 86.4091 99.5708
15AA2_2 93 0 7803 7896 7896 98.8221 100
14GA3_1 87 0 3295 3382 3382 97.4275 100
19FA1 76 0 496 572 572 86.7132 100
19CA1 76 0 233 309 309 75.4045 100
13DD1 75 6 2477 2546 2552 97.0611 99.7648
14ED1 75 0 1301 1376 1376 94.5494 100
19DA1 69 0 504 573 573 87.9581 100
15AJ1_1 67 0 2203 2270 2270 97.0484 100
15AJ1_2 66 0 2204 2270 2270 97.0925 100
13CA1 64 2 403 465 467 86.2955 99.5717
12FA1 64 1 350 413 414 84.541 99.7584
12BA1 59 0 225 284 284 79.2253 100
22BB1_S 55 0 738 793 793 93.0643 100
11DA1 54 0 501 555 555 90.2702 100
12EA1 50 0 420 470 470 89.3617 100
14HD1 49 7 6271 6313 6320 99.2246 99.8892
11BA1 48 2 324 370 372 87.0967 99.4623
13AB3 44 15 1417 1446 1461 96.9883 98.9733
19EA1 43 2 428 469 471 90.8704 99.5753
11CA1 42 0 442 484 484 91.3223 100
13EA1 40 0 418 458 458 91.2663 100
14EA4 40 0 4059 4099 4099 99.0241 100
23AB1_S_1 38 0 1929 1967 1967 98.0681 100
23AB1_S_2 38 0 1929 1967 1967 98.0681 100
14BD1 37 1 4062 4098 4099 99.0973 99.9756
12CA1 34 0 311 345 345 90.1449 100
19BA1 33 2 309 340 342 90.3508 99.4152
11EA1 32 1 506 537 538 94.052 99.8141
12DA1 31 0 423 454 454 93.1718 100
18AF1 30 0 155 185 185 83.7837 100
14GB1 29 0 4545 4574 4574 99.3659 100
11DD1 27 0 1955 1982 1982 98.6377 100
17AF1 26 22 4560 4564 4586 99.433 99.5202
13FG1_1_S 25 0 1941 1966 1966 98.7283 100
13FG1_2_S 25 0 1941 1966 1966 98.7283 100
14FD1 24 0 2528 2552 2552 99.0595 100
13BD1 21 0 1433 1454 1454 98.5557 100
12BD1 21 0 1141 1162 1162 98.1927 100
12DD1 21 0 1528 1549 1549 98.6442 100
14HB1 20 0 2995 3015 3015 99.3366 100
14GD1 18 1 5608 5625 5626 99.68 99.9822
11BD1 16 0 1226 1242 1242 98.7117 100
12FC2 14 0 682 696 696 97.9885 100
12FC2_2 14 0 682 696 696 97.9885 100
13FC2 13 7 736 742 749 98.2643 99.0654
13FC2_2 13 7 736 742 749 98.2643 99.0654
11FC2 12 11 626 632 643 97.3561 98.2892
11FC2_2 12 11 626 632 643 97.3561 98.2892
18FC2 12 0 263 275 275 95.6363 100
18FE2 12 4 275 283 287 95.8188 98.6062
19BD1 12 3 1205 1214 1217 99.0139 99.7534
14PF1 11 0 2148 2159 2159 99.4905 100
14MG2 11 2 280 289 291 96.2199 99.3127
14NC2 10 2 179 187 189 94.7089 98.9417
14FB1 10 0 3042 3052 3052 99.6723 100
14EB1 9 0 3072 3081 3081 99.7078 100
17BB3 8 0 2151 2159 2159 99.6294 100
19FD1 5 0 428 433 433 98.8452 100
19FD1_2 5 0 428 433 433 98.8452 100
20AA7_S 5 27 971 949 976 99.4877 97.2336
18GE2 5 0 302 307 307 98.3713 100
18BB1 4 2 4220 4222 4224 99.9053 99.9526
18AB1 4 0 3764 3768 3768 99.8938 100
18AD1 4 0 3764 3768 3768 99.8938 100
14NE2 4 0 181 185 185 97.8378 100
14NG2 3 0 196 199 199 98.4924 100
14JC1 3 0 2251 2254 2254 99.8669 100
14JD1 3 0 1009 1012 1012 99.7035 100
21BA1_S 3 0 821 824 824 99.6359 100
18HC2 3 0 449 452 452 99.3362 100
22FB1_S 3 0 1009 1012 1012 99.7035 100
18EC2 2 1 251 252 253 99.2094 99.6047
18EE2 2 1 264 265 266 99.2481 99.624
18GG2 2 0 315 317 317 99.369 100
14KG2 2 1 236 237 238 99.1596 99.5798
11AB3 2 0 1245 1247 1247 99.8396 100
12AB3 1 7 1165 1159 1166 99.9142 99.3996
14LC2 1 0 219 220 220 99.5454 100
14LG2 1 0 215 216 216 99.537 100
14MC2 1 1 230 230 231 99.567 99.567
14KE2 1 1 202 202 203 99.5073 99.5073
14JB1 1 0 2911 2912 2912 99.9656 100
18AA2 1 0 4454 4455 4455 99.9775 100
18FA2 1 0 155 156 156 99.3589 100
18HG2 1 0 251 252 252 99.6031 100
18GC2 1 0 269 270 270 99.6296 100
20BA1_S 1 2 618 617 619 99.8384 99.6768
21AA7_S 0 0 824 824 824 100 100
18JB1 0 0 7 7 7 100 100
19AB3 0 1 1216 1215 1216 100 99.9177
18HE2 0 0 248 248 248 100 100
18HA2 0 0 225 225 225 100 100
18FG2 0 0 141 141 141 100 100
18GA2 0 3 261 258 261 100 98.8505
18EG2 0 0 34 34 34 100 100
18BC1 0 2 3309 3307 3309 100 99.9395
18BD1 0 0 2284 2284 2284 100 100
18BE1 0 2 1320 1318 1320 100 99.8484
18EA2 0 1 345 344 345 100 99.7101
14KC2 0 0 157 157 157 100 100
14ME2 0 2 330 328 330 100 99.3939
14LE2 0 3 233 230 233 100 98.7124
14PB1 0 0 2 2 2 100 100
17EB1_S 0 64 3476 3412 3476 100 98.1588
4

Good job, and now please do the following with that data.

create table #sample(Scanner varchar(50), [No Read] int, [No Data] int) --etc for all columns

insert into #sample
select '15Af1_1', 912, 2 union
select '15AG1_1', 303, 2 union
--etc for all of the tables

help us help you by providing us the sample data as above.

1 Like
5

Cheers. I will not be able to do it tonight. I will do it tomorrow ? will you be available tomorrow to have look?

Cheers pal

6

We aint got nowhere to go :wink:

7

Cheers much appreciated. I will try to do it tonight but if not get chance will post results tomorrow

8

hi

i have created the Drop Create Sample Data Script ..

Hope this helps :slight_smile:

Please click arrow to the left for SAMPLE DATA Script 
CREATE TABLE #Sample  
(
Scanner_No varchar(100)
,No_Read int 
,No_Data int 
,Good_Read int 
,Good_DATA int
,Total	int 
,GR	FLOAT
,GD FLOAT
)
GO 

insert into #Sample Select 	'15AF1_2',	912	,	2	,	7163	,	8073	,	8075	,	88.7058	,	99.9752
insert into #Sample Select 	'15AG1_1',	303	,	2	,	5666	,	5967	,	5969	,	94.9237	,	99.9664
insert into #Sample Select 	'22EB1_S',	243	,	0	,	2686	,	2929	,	2929	,	91.7036	,	100
insert into #Sample Select 	'15AF1_1',	208	,	2	,	7867	,	8073	,	8075	,	97.4241	,	99.9752
insert into #Sample Select 	'15AH1_2',	162	,	0	,	3661	,	3823	,	3823	,	95.7624	,	100
insert into #Sample Select 	'15AA2_1',	158	,	0	,	7737	,	7895	,	7895	,	97.9987	,	100
insert into #Sample Select 	'19DD1',	157	,	0	,	1650	,	1807	,	1807	,	91.3115	,	100
insert into #Sample Select 	'11FA1',	142	,	6	,	389	,	525	,	531	,	73.258	,	98.87
insert into #Sample Select 	'15AG1_2',	124	,	1	,	5845	,	5968	,	5969	,	97.9226	,	99.9832
insert into #Sample Select 	'13BA1',	122	,	0	,	709	,	831	,	831	,	85.3188	,	100
insert into #Sample Select 	'22EA3_2_S',	122	,	5	,	2801	,	2918	,	2923	,	95.8262	,	99.8289
insert into #Sample Select 	'13FA1',	113	,	0	,	691	,	804	,	804	,	85.9452	,	100
insert into #Sample Select 	'15AH1_1',	113	,	0	,	3710	,	3823	,	3823	,	97.0442	,	100
insert into #Sample Select 	'14GC1',	97	,	0	,	5529	,	5626	,	5626	,	98.2758	,	100
insert into #Sample Select 	'13DA1',	95	,	3	,	604	,	696	,	699	,	86.4091	,	99.5708
insert into #Sample Select 	'15AA2_2',	93	,	0	,	7803	,	7896	,	7896	,	98.8221	,	100
insert into #Sample Select 	'14GA3_1',	87	,	0	,	3295	,	3382	,	3382	,	97.4275	,	100
insert into #Sample Select 	'19FA1',	76	,	0	,	496	,	572	,	572	,	86.7132	,	100
insert into #Sample Select 	'19CA1',	76	,	0	,	233	,	309	,	309	,	75.4045	,	100
insert into #Sample Select 	'13DD1',	75	,	6	,	2477	,	2546	,	2552	,	97.0611	,	99.7648
insert into #Sample Select 	'14ED1',	75	,	0	,	1301	,	1376	,	1376	,	94.5494	,	100
insert into #Sample Select 	'19DA1',	69	,	0	,	504	,	573	,	573	,	87.9581	,	100
insert into #Sample Select 	'15AJ1_1',	67	,	0	,	2203	,	2270	,	2270	,	97.0484	,	100
insert into #Sample Select 	'15AJ1_2',	66	,	0	,	2204	,	2270	,	2270	,	97.0925	,	100
insert into #Sample Select 	'13CA1',	64	,	2	,	403	,	465	,	467	,	86.2955	,	99.5717
insert into #Sample Select 	'12FA1',	64	,	1	,	350	,	413	,	414	,	84.541	,	99.7584
insert into #Sample Select 	'12BA1',	59	,	0	,	225	,	284	,	284	,	79.2253	,	100
insert into #Sample Select 	'22BB1_S',	55	,	0	,	738	,	793	,	793	,	93.0643	,	100
insert into #Sample Select 	'11DA1',	54	,	0	,	501	,	555	,	555	,	90.2702	,	100
insert into #Sample Select 	'12EA1',	50	,	0	,	420	,	470	,	470	,	89.3617	,	100
insert into #Sample Select 	'14HD1',	49	,	7	,	6271	,	6313	,	6320	,	99.2246	,	99.8892
insert into #Sample Select 	'11BA1',	48	,	2	,	324	,	370	,	372	,	87.0967	,	99.4623
insert into #Sample Select 	'13AB3',	44	,	15	,	1417	,	1446	,	1461	,	96.9883	,	98.9733
insert into #Sample Select 	'19EA1',	43	,	2	,	428	,	469	,	471	,	90.8704	,	99.5753
insert into #Sample Select 	'11CA1',	42	,	0	,	442	,	484	,	484	,	91.3223	,	100
insert into #Sample Select 	'13EA1',	40	,	0	,	418	,	458	,	458	,	91.2663	,	100
insert into #Sample Select 	'14EA4',	40	,	0	,	4059	,	4099	,	4099	,	99.0241	,	100
insert into #Sample Select 	'23AB1_S_1',	38	,	0	,	1929	,	1967	,	1967	,	98.0681	,	100
insert into #Sample Select 	'23AB1_S_2',	38	,	0	,	1929	,	1967	,	1967	,	98.0681	,	100
insert into #Sample Select 	'14BD1',	37	,	1	,	4062	,	4098	,	4099	,	99.0973	,	99.9756
insert into #Sample Select 	'12CA1',	34	,	0	,	311	,	345	,	345	,	90.1449	,	100
insert into #Sample Select 	'19BA1',	33	,	2	,	309	,	340	,	342	,	90.3508	,	99.4152
insert into #Sample Select 	'11EA1',	32	,	1	,	506	,	537	,	538	,	94.052	,	99.8141
insert into #Sample Select 	'12DA1',	31	,	0	,	423	,	454	,	454	,	93.1718	,	100
insert into #Sample Select 	'18AF1',	30	,	0	,	155	,	185	,	185	,	83.7837	,	100
insert into #Sample Select 	'14GB1',	29	,	0	,	4545	,	4574	,	4574	,	99.3659	,	100
insert into #Sample Select 	'11DD1',	27	,	0	,	1955	,	1982	,	1982	,	98.6377	,	100
insert into #Sample Select 	'17AF1',	26	,	22	,	4560	,	4564	,	4586	,	99.433	,	99.5202
insert into #Sample Select 	'13FG1_1_S',	25	,	0	,	1941	,	1966	,	1966	,	98.7283	,	100
insert into #Sample Select 	'13FG1_2_S',	25	,	0	,	1941	,	1966	,	1966	,	98.7283	,	100
insert into #Sample Select 	'14FD1',	24	,	0	,	2528	,	2552	,	2552	,	99.0595	,	100
insert into #Sample Select 	'13BD1',	21	,	0	,	1433	,	1454	,	1454	,	98.5557	,	100
insert into #Sample Select 	'12BD1',	21	,	0	,	1141	,	1162	,	1162	,	98.1927	,	100
insert into #Sample Select 	'12DD1',	21	,	0	,	1528	,	1549	,	1549	,	98.6442	,	100
insert into #Sample Select 	'14HB1',	20	,	0	,	2995	,	3015	,	3015	,	99.3366	,	100
insert into #Sample Select 	'14GD1',	18	,	1	,	5608	,	5625	,	5626	,	99.68	,	99.9822
insert into #Sample Select 	'11BD1',	16	,	0	,	1226	,	1242	,	1242	,	98.7117	,	100
insert into #Sample Select 	'12FC2',	14	,	0	,	682	,	696	,	696	,	97.9885	,	100
insert into #Sample Select 	'12FC2_2',	14	,	0	,	682	,	696	,	696	,	97.9885	,	100
insert into #Sample Select 	'13FC2',	13	,	7	,	736	,	742	,	749	,	98.2643	,	99.0654
insert into #Sample Select 	'13FC2_2',	13	,	7	,	736	,	742	,	749	,	98.2643	,	99.0654
insert into #Sample Select 	'11FC2',	12	,	11	,	626	,	632	,	643	,	97.3561	,	98.2892
insert into #Sample Select 	'11FC2_2',	12	,	11	,	626	,	632	,	643	,	97.3561	,	98.2892
insert into #Sample Select 	'18FC2',	12	,	0	,	263	,	275	,	275	,	95.6363	,	100
insert into #Sample Select 	'18FE2',	12	,	4	,	275	,	283	,	287	,	95.8188	,	98.6062
insert into #Sample Select 	'19BD1',	12	,	3	,	1205	,	1214	,	1217	,	99.0139	,	99.7534
insert into #Sample Select 	'14PF1',	11	,	0	,	2148	,	2159	,	2159	,	99.4905	,	100
insert into #Sample Select 	'14MG2',	11	,	2	,	280	,	289	,	291	,	96.2199	,	99.3127
insert into #Sample Select 	'14NC2',	10	,	2	,	179	,	187	,	189	,	94.7089	,	98.9417
insert into #Sample Select 	'14FB1',	10	,	0	,	3042	,	3052	,	3052	,	99.6723	,	100
insert into #Sample Select 	'14EB1',	9	,	0	,	3072	,	3081	,	3081	,	99.7078	,	100
insert into #Sample Select 	'17BB3',	8	,	0	,	2151	,	2159	,	2159	,	99.6294	,	100
insert into #Sample Select 	'19FD1',	5	,	0	,	428	,	433	,	433	,	98.8452	,	100
insert into #Sample Select 	'19FD1_2',	5	,	0	,	428	,	433	,	433	,	98.8452	,	100
insert into #Sample Select 	'20AA7_S',	5	,	27	,	971	,	949	,	976	,	99.4877	,	97.2336
insert into #Sample Select 	'18GE2',	5	,	0	,	302	,	307	,	307	,	98.3713	,	100
insert into #Sample Select 	'18BB1',	4	,	2	,	4220	,	4222	,	4224	,	99.9053	,	99.9526
insert into #Sample Select 	'18AB1',	4	,	0	,	3764	,	3768	,	3768	,	99.8938	,	100
insert into #Sample Select 	'18AD1',	4	,	0	,	3764	,	3768	,	3768	,	99.8938	,	100
insert into #Sample Select 	'14NE2',	4	,	0	,	181	,	185	,	185	,	97.8378	,	100
insert into #Sample Select 	'14NG2',	3	,	0	,	196	,	199	,	199	,	98.4924	,	100
insert into #Sample Select 	'14JC1',	3	,	0	,	2251	,	2254	,	2254	,	99.8669	,	100
insert into #Sample Select 	'14JD1',	3	,	0	,	1009	,	1012	,	1012	,	99.7035	,	100
insert into #Sample Select 	'21BA1_S',	3	,	0	,	821	,	824	,	824	,	99.6359	,	100
insert into #Sample Select 	'18HC2',	3	,	0	,	449	,	452	,	452	,	99.3362	,	100
insert into #Sample Select 	'22FB1_S',	3	,	0	,	1009	,	1012	,	1012	,	99.7035	,	100
insert into #Sample Select 	'18EC2',	2	,	1	,	251	,	252	,	253	,	99.2094	,	99.6047
insert into #Sample Select 	'18EE2',	2	,	1	,	264	,	265	,	266	,	99.2481	,	99.624
insert into #Sample Select 	'18GG2',	2	,	0	,	315	,	317	,	317	,	99.369	,	100
insert into #Sample Select 	'14KG2',	2	,	1	,	236	,	237	,	238	,	99.1596	,	99.5798
insert into #Sample Select 	'11AB3',	2	,	0	,	1245	,	1247	,	1247	,	99.8396	,	100
insert into #Sample Select 	'12AB3',	1	,	7	,	1165	,	1159	,	1166	,	99.9142	,	99.3996
insert into #Sample Select 	'14LC2',	1	,	0	,	219	,	220	,	220	,	99.5454	,	100
insert into #Sample Select 	'14LG2',	1	,	0	,	215	,	216	,	216	,	99.537	,	100
insert into #Sample Select 	'14MC2',	1	,	1	,	230	,	230	,	231	,	99.567	,	99.567
insert into #Sample Select 	'14KE2',	1	,	1	,	202	,	202	,	203	,	99.5073	,	99.5073
insert into #Sample Select 	'14JB1',	1	,	0	,	2911	,	2912	,	2912	,	99.9656	,	100
insert into #Sample Select 	'18AA2',	1	,	0	,	4454	,	4455	,	4455	,	99.9775	,	100
insert into #Sample Select 	'18FA2',	1	,	0	,	155	,	156	,	156	,	99.3589	,	100
insert into #Sample Select 	'18HG2',	1	,	0	,	251	,	252	,	252	,	99.6031	,	100
insert into #Sample Select 	'18GC2',	1	,	0	,	269	,	270	,	270	,	99.6296	,	100
insert into #Sample Select 	'20BA1_S',	1	,	2	,	618	,	617	,	619	,	99.8384	,	99.6768
insert into #Sample Select 	'21AA7_S',	0	,	0	,	824	,	824	,	824	,	100	,	100
insert into #Sample Select 	'18JB1',	0	,	0	,	7	,	7	,	7	,	100	,	100
insert into #Sample Select 	'19AB3',	0	,	1	,	1216	,	1215	,	1216	,	100	,	99.9177
insert into #Sample Select 	'18HE2',	0	,	0	,	248	,	248	,	248	,	100	,	100
insert into #Sample Select 	'18HA2',	0	,	0	,	225	,	225	,	225	,	100	,	100
insert into #Sample Select 	'18FG2',	0	,	0	,	141	,	141	,	141	,	100	,	100
insert into #Sample Select 	'18GA2',	0	,	3	,	261	,	258	,	261	,	100	,	98.8505
insert into #Sample Select 	'18EG2',	0	,	0	,	34	,	34	,	34	,	100	,	100
insert into #Sample Select 	'18BC1',	0	,	2	,	3309	,	3307	,	3309	,	100	,	99.9395
insert into #Sample Select 	'18BD1',	0	,	0	,	2284	,	2284	,	2284	,	100	,	100
insert into #Sample Select 	'18BE1',	0	,	2	,	1320	,	1318	,	1320	,	100	,	99.8484
insert into #Sample Select 	'18EA2',	0	,	1	,	345	,	344	,	345	,	100	,	99.7101
insert into #Sample Select 	'14KC2',	0	,	0	,	157	,	157	,	157	,	100	,	100
insert into #Sample Select 	'14ME2',	0	,	2	,	330	,	328	,	330	,	100	,	99.3939
insert into #Sample Select 	'14LE2',	0	,	3	,	233	,	230	,	233	,	100	,	98.7124
insert into #Sample Select 	'14PB1',	0	,	0	,	2	,	2	,	2	,	100	,	100
insert into #Sample Select 	'17EB1_S',	0	,	64	,	3476	,	3412	,	3476	,	100	,	98.1588

SELECT * FROM #Sample

DROP TABLE #Sample
GO
1 Like
9

Your specific requirements are unclear.

I'm assuming you meant not to list the row if BOTH values, GR and GD, are >= 98%. If that's not what you meant, please clarify your requirements.

SELECT
   [Scanner],
   SUM([NoRead]) AS [No Read],
   SUM([NoData]) AS [No Data],
   SUM([Total] - [NoRead]) AS [Good Read],
   SUM([Total] - [NoData]) AS [Good Data],
   SUM([Total]) AS [Total],
   SUM(CAST([Total] - [NoRead] AS decimal)) / SUM([Total]) * 100 AS [GR],
   SUM(CAST([Total] - [NoData] AS decimal)) / SUM([Total]) * 100 AS [GD]
FROM cteSummarisedData
GROUP BY [Scanner]
HAVING NOT (
   SUM(CAST([Total] - [NoRead] AS decimal)) / SUM([Total]) * 100 > 98.0 AND
   SUM(CAST([Total] - [NoData] AS decimal)) / SUM([Total]) * 100 > 98.0 )
ORDER BY [No Read] DESC
1 Like
10

Hi Scott I just ran your quarry and it if filtering good reads but good data is not. I wanted to filter data so it will only display good read below 98% and I want filter good data as well so it will only show results where good data is below 98%
see below : results of your query:

Scanner No Read No Data Good Read Good Data Total GR GD
15AF1_2 971 2 6687 7656 7658 87.320400 99.973800
15AG1_1 265 2 5515 5778 5780 95.415200 99.965300
15AF1_1 167 2 7491 7656 7658 97.819200 99.973800
15AH1_2 157 0 3687 3844 3844 95.915700 100.000000
22EB1_S 155 0 2026 2181 2181 92.893100 100.000000
13DA1 131 1 751 881 882 85.147300 99.886600
19DD1 124 0 1466 1590 1590 92.201200 100.000000
13BA1 117 1 558 674 675 82.666600 99.851800
14GC1 110 0 5066 5176 5176 97.874800 100.000000
14GA3_1 91 0 3901 3992 3992 97.720400 100.000000
13FA1 86 1 597 682 683 87.408400 99.853500
12BA1 86 1 322 407 408 78.921500 99.754900
15AH1_1 84 0 3760 3844 3844 97.814700 100.000000
18AF1 81 0 410 491 491 83.503000 100.000000
11FA1 81 1 363 443 444 81.756700 99.774700
22EA3_2_S 76 8 2096 2164 2172 96.500900 99.631600
14HB1 73 0 2846 2919 2919 97.499100 100.000000
19FA1 72 0 506 578 578 87.543200 100.000000
12FA1 66 4 330 392 396 83.333300 98.989800
13CA1 64 2 419 481 483 86.749400 99.585900
12EA1 63 1 492 554 555 88.648600 99.819800
13DD1 60 3 2446 2503 2506 97.605700 99.880200
15AJ1_2 60 0 1840 1900 1900 96.842100 100.000000
11DA1 59 2 394 451 453 86.975700 99.558400
19CA1 57 0 289 346 346 83.526000 100.000000
14ED1 56 0 1407 1463 1463 96.172200 100.000000
13AB3 47 7 916 956 963 95.119400 99.273100
11BD1 41 0 1304 1345 1345 96.951600 100.000000
12DA1 40 0 448 488 488 91.803200 100.000000
12DD1 39 1 1720 1758 1759 97.782800 99.943100
11EA1 35 4 385 416 420 91.666600 99.047600
12BD1 35 2 765 798 800 95.625000 99.750000
12CA1 34 1 271 304 305 88.852400 99.672100
19BA1 33 0 310 343 343 90.379000 100.000000
19DA1 31 0 344 375 375 91.733300 100.000000
13EA1 28 0 366 394 394 92.893400 100.000000
14MG2 26 1 316 341 342 92.397600 99.707600
11BA1 26 1 246 271 272 90.441100 99.632300
11CA1 25 0 305 330 330 92.424200 100.000000
18AB1 25 0 2787 2812 57808 4.821100 4.864300
18AD1 25 0 2787 2812 57808 4.821100 4.864300
19EA1 25 1 397 421 422 94.075800 99.763000
21BA1_S 22 8 626 640 648 96.604900 98.765400
22BB1_S 21 0 515 536 536 96.082000 100.000000
11FC2 20 1 651 670 671 97.019300 99.850900
11FC2_2 20 1 651 670 671 97.019300 99.850900
14KG2 4 0 141 145 145 97.241300 100.000000
18GC2 4 0 164 168 168 97.619000 100.000000
18GG2 4 0 143 147 147 97.278900 100.000000
18EC2 1 0 30 31 31 96.774100 100.000000
18EG2 1 0 14 15 15 93.333300 100.000000
17EB1_S 0 62 2778 2716 2778 100.000000 97.768100
11

Hi Harishgg1 sorry for late reply see data below:

Scanner_No No_Read No_Data Good_Read Good_DATA Total GR GD
15AF1_2 912 2 7163 8073 8075 88.7058 99.9752
15AG1_1 303 2 5666 5967 5969 94.9237 99.9664
22EB1_S 243 0 2686 2929 2929 91.7036 100
15AF1_1 208 2 7867 8073 8075 97.4241 99.9752
15AH1_2 162 0 3661 3823 3823 95.7624 100
15AA2_1 158 0 7737 7895 7895 97.9987 100
19DD1 157 0 1650 1807 1807 91.3115 100
11FA1 142 6 389 525 531 73.258 98.87
15AG1_2 124 1 5845 5968 5969 97.9226 99.9832
13BA1 122 0 709 831 831 85.3188 100
22EA3_2_S 122 5 2801 2918 2923 95.8262 99.8289
13FA1 113 0 691 804 804 85.9452 100
15AH1_1 113 0 3710 3823 3823 97.0442 100
14GC1 97 0 5529 5626 5626 98.2758 100
13DA1 95 3 604 696 699 86.4091 99.5708
15AA2_2 93 0 7803 7896 7896 98.8221 100
14GA3_1 87 0 3295 3382 3382 97.4275 100
19FA1 76 0 496 572 572 86.7132 100
19CA1 76 0 233 309 309 75.4045 100
13DD1 75 6 2477 2546 2552 97.0611 99.7648
14ED1 75 0 1301 1376 1376 94.5494 100
19DA1 69 0 504 573 573 87.9581 100
15AJ1_1 67 0 2203 2270 2270 97.0484 100
15AJ1_2 66 0 2204 2270 2270 97.0925 100
13CA1 64 2 403 465 467 86.2955 99.5717
12FA1 64 1 350 413 414 84.541 99.7584
12BA1 59 0 225 284 284 79.2253 100
22BB1_S 55 0 738 793 793 93.0643 100
11DA1 54 0 501 555 555 90.2702 100
12EA1 50 0 420 470 470 89.3617 100
14HD1 49 7 6271 6313 6320 99.2246 99.8892
11BA1 48 2 324 370 372 87.0967 99.4623
13AB3 44 15 1417 1446 1461 96.9883 98.9733
19EA1 43 2 428 469 471 90.8704 99.5753
11CA1 42 0 442 484 484 91.3223 100
13EA1 40 0 418 458 458 91.2663 100
14EA4 40 0 4059 4099 4099 99.0241 100
23AB1_S_1 38 0 1929 1967 1967 98.0681 100
23AB1_S_2 38 0 1929 1967 1967 98.0681 100
14BD1 37 1 4062 4098 4099 99.0973 99.9756
12CA1 34 0 311 345 345 90.1449 100
19BA1 33 2 309 340 342 90.3508 99.4152
11EA1 32 1 506 537 538 94.052 99.8141
12DA1 31 0 423 454 454 93.1718 100
18AF1 30 0 155 185 185 83.7837 100
14GB1 29 0 4545 4574 4574 99.3659 100
11DD1 27 0 1955 1982 1982 98.6377 100
17AF1 26 22 4560 4564 4586 99.433 99.5202
13FG1_1_S 25 0 1941 1966 1966 98.7283 100
13FG1_2_S 25 0 1941 1966 1966 98.7283 100
14FD1 24 0 2528 2552 2552 99.0595 100
13BD1 21 0 1433 1454 1454 98.5557 100
12BD1 21 0 1141 1162 1162 98.1927 100
12DD1 21 0 1528 1549 1549 98.6442 100
14HB1 20 0 2995 3015 3015 99.3366 100
14GD1 18 1 5608 5625 5626 99.68 99.9822
11BD1 16 0 1226 1242 1242 98.7117 100
12FC2 14 0 682 696 696 97.9885 100
12FC2_2 14 0 682 696 696 97.9885 100
13FC2 13 7 736 742 749 98.2643 99.0654
13FC2_2 13 7 736 742 749 98.2643 99.0654
11FC2 12 11 626 632 643 97.3561 98.2892
11FC2_2 12 11 626 632 643 97.3561 98.2892
18FC2 12 0 263 275 275 95.6363 100
18FE2 12 4 275 283 287 95.8188 98.6062
19BD1 12 3 1205 1214 1217 99.0139 99.7534
14PF1 11 0 2148 2159 2159 99.4905 100
14MG2 11 2 280 289 291 96.2199 99.3127
14NC2 10 2 179 187 189 94.7089 98.9417
14FB1 10 0 3042 3052 3052 99.6723 100
14EB1 9 0 3072 3081 3081 99.7078 100
17BB3 8 0 2151 2159 2159 99.6294 100
19FD1 5 0 428 433 433 98.8452 100
19FD1_2 5 0 428 433 433 98.8452 100
20AA7_S 5 27 971 949 976 99.4877 97.2336
18GE2 5 0 302 307 307 98.3713 100
18BB1 4 2 4220 4222 4224 99.9053 99.9526
18AB1 4 0 3764 3768 3768 99.8938 100
18AD1 4 0 3764 3768 3768 99.8938 100
14NE2 4 0 181 185 185 97.8378 100
14NG2 3 0 196 199 199 98.4924 100
14JC1 3 0 2251 2254 2254 99.8669 100
14JD1 3 0 1009 1012 1012 99.7035 100
21BA1_S 3 0 821 824 824 99.6359 100
18HC2 3 0 449 452 452 99.3362 100
22FB1_S 3 0 1009 1012 1012 99.7035 100
18EC2 2 1 251 252 253 99.2094 99.6047
18EE2 2 1 264 265 266 99.2481 99.624
18GG2 2 0 315 317 317 99.369 100
14KG2 2 1 236 237 238 99.1596 99.5798
11AB3 2 0 1245 1247 1247 99.8396 100
12AB3 1 7 1165 1159 1166 99.9142 99.3996
14LC2 1 0 219 220 220 99.5454 100
14LG2 1 0 215 216 216 99.537 100
14MC2 1 1 230 230 231 99.567 99.567
14KE2 1 1 202 202 203 99.5073 99.5073
14JB1 1 0 2911 2912 2912 99.9656 100
18AA2 1 0 4454 4455 4455 99.9775 100
18FA2 1 0 155 156 156 99.3589 100
18HG2 1 0 251 252 252 99.6031 100
18GC2 1 0 269 270 270 99.6296 100
20BA1_S 1 2 618 617 619 99.8384 99.6768
21AA7_S 0 0 824 824 824 100 100
18JB1 0 0 7 7 7 100 100
19AB3 0 1 1216 1215 1216 100 99.9177
18HE2 0 0 248 248 248 100 100
18HA2 0 0 225 225 225 100 100
18FG2 0 0 141 141 141 100 100
18GA2 0 3 261 258 261 100 98.8505
18EG2 0 0 34 34 34 100 100
18BC1 0 2 3309 3307 3309 100 99.9395
18BD1 0 0 2284 2284 2284 100 100
18BE1 0 2 1320 1318 1320 100 99.8484
18EA2 0 1 345 344 345 100 99.7101
14KC2 0 0 157 157 157 100 100
14ME2 0 2 330 328 330 100 99.3939
14LE2 0 3 233 230 233 100 98.7124
14PB1 0 0 2 2 2 100 100
17EB1_S 0 64 3476 3412 3476 100 98.1588
12

Hi Scott I just realised that I requested something that can't be delivered. So I should ask can you filter data so only displays Good read below 98% and then we need another query that will display good data below 98%. Its all working now buddy thank you very much. I just created two queries 1st for Good read<98% and 2nd for Good data <98%.

Cheers pal problem SOLVED !!!

13

Yep, separate queries, you could UNION the results together to get a final single result set.

For a single condition, you can get rid of the (convoluted) NOT:

HAVING SUM(CAST([Total] - [NoRead] AS decimal)) / SUM([Total]) * 100 < 98.0

Or <= 98, depending on which you need.

1 Like