AnsweredAssumed Answered

Sumarise query

Question asked by igreguri Champion on Jun 8, 2017
Latest reply on Jun 10, 2017 by igreguri

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

STEP 1:

  1. New column with Average value of columns KG, KS and KD
  2. 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

 

STEP 2:

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

 

QUERY:

SELECT *

FROM(

      SELECT       

              a.[Sifra]

              ,a.[SifraSAP]

              ,a.[Naziv]

              ,b.[MedijID]

              ,b.[FKSID]  

              ,b.[MDKMin]

              ,b.[MDKMax]

              ,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

                                    )as seqnum       

      FROM

            [Pren_RNR].[dbo].[Objekti] a

            INNER JOIN [Pren_RNR].[dbo].[UzorakArhiva] b

            ON a.[ID]=b.[MjestoUzorkovanjaID]

      WHERE a.[SifraSAP] like '%RT%'

        and

        a.[SAKRIVEN] = 0

        and

        a.[OBRISANO] = 0

        and

        a.[VRSTA] = 8

        ) ab

WHERE seqnum = 1

ORDER BY ab.[SifraSAP] ASC;

 

Tablica.jpg

Regards,

Igor

Outcomes