joining two tables by value of one column and calculating

Question

Thank you for interested in question. I have in one table some thing like this

sifrez | sifKorisnikPK | Status
  1    |    1          |  'P'
  2    |    1          |  'P'
  3    |    1          |  'U'
  4    |    2          |  'P'
  5    |    2          |  'P'
  6    |    2          |  'U'
  7    |    2          |  'U'
  8    |    3          |  'U'
  9    |    3          |  'U'
  10   |    3          |  'U'
  11   |    3          |  'U'
  12   |    4          |  'P'
  13   |    4          |  'P'

Then i created stored function which count values of P and U search ratio between them find user with that id and return username

CREATE PROCEDURE sp_getBestUsernames
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here

    SELECT SifKorisnikPK, COUNT (Status) AS BrojLosih
    INTO #LosaRez
    FROM Rezervacija 
    WHERE Status = 'P'
    GROUP BY SifKorisnikPK
    order by count(*) desc

After this part it give me

sifKorisnikPK | BrojLosih
       1      |    2
       2      |    2
       4      |    2

This only count p on status. And this:

SELECT SifKorisnikPK, COUNT (Status) AS BrojDobrih
INTO #DobraRez
FROM Rezervacija 
WHERE Status = 'U'
GROUP BY SifKorisnikPK
order by count(*) desc

i get

sifKorisnikPK | BrojDobrih
     1        |    1
     2        |    2
     3        |    4

Only count U. This part work like a charm... And here i try to join this two temp tables and calculate ratio and return SifKorisnik with best ratio

SELECT Username FROM Korisnik   
    WHERE SifKorisnik = (SELECT TOP 1 a.SifKorisnikPK 
                         FROM #DobraRez a   
                         INNER JOIN #LosaRez b  
                         ON a.SifKorisnikPK = b.SifKorisnikPK OR 
                         a.SifKorisnikPK != b.SifKorisnikPK     
                         WHERE a.BrojDobrih - b.BrojLosih > 0   
                         ORDER BY a.BrojDobrih - b.BrojLosih ) 
    END

And after joining operation i expect to get some thing like this, which i'm not getting

sifKorisnikPK | BrojDobrih | BrojLosih 1 | 1 | 2 2 | 2 | 2 3 | 4 | 0 4 | 0 | 2

so i can calculate the ratio BrojDobrih - BrojLosih and return the SifKorisnikPK which is the best ratio. In this case it would be SifKorisnik 3. But my procedure get SifKorisnik 2 which is second best. I presume that the problem is in joining operation that it doesn't input 0. So how can i solve this?

Answer 1

Try this:

SELECT 
   A.sifKorisnikPK,
   IsNull(BrojDobrih,0) BrojDobrih,
   IsNull(BrojLosih,0) BrojLosih
FROM (select distinct sifKorisnikPK from Rezervacija) A
LEFT JOIN #LosaRez B
   ON A.sifKorisnikPK = B.sifKorisnikPK 
LEFT JOIN #DobraRez C
   ON A.sifKorisnikPK  = C.sifKorisnikPK 
ORDER BY (IsNull(BrojDobrih,0) - IsNull(BrojLosih,0))