I have one query for lab analysis of storages. Every storages has a lot of lab analysis (columns KG, KS and KD) and I want to see total sum of bad analysis (out of range in column Status) by storage (column Sifra).
How to do this...?
- New column with Average value of columns KG, KS and KD
- New column with value Status...
This value is average compared with minimum (MDKMin) and maximum (MDKMax).
If value is in range between MDKMin and MDKMax (or MDKMin and MDKMax is NULL) then value is 0.
Out of range is 1.
If MDKMin have value and MDKMax not then Status must be 0 if Average => MDKMin, else 1
If MDKMax have value and MDKMin not then Status must be 0 if Average <= MDKMax, else 1
When we have calculated values 0 or 1 in column Status, then i need sumarise this by storage (column Sifra).
So, result must be (example):
Sifra other columns Status
A001 ... ... 3
A002 ... ... 0
A003 ... ... 1
B001 ... ... 0
,b.[DatumUzimanjaUzorka] as Date
,b.[KorigiranoGornji] as KG
,b.[KorigiranoSrednji] as KS
,b.[KorigiranoDonji] as KD
, ROW_NUMBER() OVER (PARTITION BY a.[SifraSAP],b.[FKSID]
ORDER BY b.[DatumUzimanjaUzorka] DESC
INNER JOIN [Pren_RNR].[dbo].[UzorakArhiva] b
WHERE a.[SifraSAP] like '%RT%'
a.[SAKRIVEN] = 0
a.[OBRISANO] = 0
a.[VRSTA] = 8
WHERE seqnum = 1
ORDER BY ab.[SifraSAP] ASC;