Results 1 to 9 of 9

Thread: Odd query

  1. #1
    Join Date
    May 2011
    Posts
    11

    Question Unanswered: Odd query

    Hi there people,
    i'm in one of those struck days when a
    Code:
    Select
           *
    From
           Custumers
    looks like a hard query.

    i have to retrieve two diferents counts from the same table, one for new custumer visit and one for old custumer visit.
    these are distinguished by an type of visit id, i actually came up with the query and it actually gets the data i need, but it looks just really really bad
    and i'm pretty sure there is a easier way to write it, any helpful hands are welcome
    Code:
    Select
    	Novos.Tec,
    	Novos.Novos_Count,
    	antigos.Antigos_Count
    From
    	(Select
    		tec.ID AS TecID,
    		tec.nome AS Tec,
    		Coalesce(COUNT(a.id),0) AS Novos_Count
    	From
    		Atendimentos a inner join
    		Tecnicos tec on a.ID_TecnicoResp = tec.ID
    	Where
    		a.ID_TipoAtend = 11
    	Group By
    		tec.Nome,
    		tec.id
    	) AS Novos
    	Full Join 
    	(Select
    		tec.ID AS TecID,
    		a2.ID_TecnicoResp Tec,
    		COUNT(a2.id) AS Antigos_Count
    	From
    		Atendimentos a2 inner join
    		Tecnicos tec on a2.ID_TecnicoResp = tec.ID
    	Where
    		a2.ID_TipoAtend = 12
    	Group By
    		tec.ID,
    		a2.ID_TecnicoResp
    	) AS Antigos on Novos.TecID = Antigos.TecID
    if by any case it gets hard to understand the query due to the table and fields nomeclature i can translate them.
    i've done this before with subqueryes in the select statement but today i'm anchored
    Thanks for the help

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This isn't exactly what you wrote, but I think that it is what you actually want. Please read with care (and understand) before you take it verbatim.
    Code:
    SELECT a.TecID
       SUM(CASE WHEN 11 = a.ID_TipoAtend THEN 1 END) AS Novos_Count
    ,  Sum(CASE WHEN 12 = a.ID_TipoAtend THEN 1 END) AS Antigos_Count
       FROM Atendimentos a2
       INNER JOIN Tecnicos tec
          ON (a2.ID_TecnicoResp = tec.ID)
       GROUP BY a.TecID
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    pat, just curious, why the parentheses around the join condition

    that always annoys me, because it's noise, and i like my SQL to be clean

    it's almost as annoying as when people write...
    Code:
    SELECT DISTINCT(somecol) FROM ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is one of those defensive coding tactics that I've learned, kind of like always having constants on the left in equality comparision operations to prevent the comparison from being confused with an assignement statement... These habits are rarely required, but when they are needed they are lifesavers.

    The parentheses make the experession explicit, so they will force the SQL syntax parse to recognize some coding errors that it would otherwise let slide.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    May 2011
    Posts
    11
    Quote Originally Posted by Pat Phelan View Post
    This isn't exactly what you wrote, but I think that it is what you actually want. Please read with care (and understand) before you take it verbatim.
    Code:
    SELECT a.TecID
       SUM(CASE WHEN 11 = a.ID_TipoAtend THEN 1 END) AS Novos_Count
    ,  Sum(CASE WHEN 12 = a.ID_TipoAtend THEN 1 END) AS Antigos_Count
       FROM Atendimentos a2
       INNER JOIN Tecnicos tec
          ON (a2.ID_TecnicoResp = tec.ID)
       GROUP BY a.TecID
    -PatP

    Thanks Pat, that was pretty much the result i wanted, had to make minor changes about it, but its way cleaner now, and performatic i suppose thanks for the support

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Wow.
    I have to say, (Pat/Rudy), that (Putting/Not Putting) parenthesis around clauses is (the height of ignorance/a true mark of quality). Indeed, this is exactly the type of coding practice that distinguishes the (coding noob/revered guru) from the (revered guru/coding noob). To do otherwise would be (insane/considered best practices). Other forum visitors would do well to (heed/ignore) this advice.

    Note: I am preparing for the coming flame war, but have yet to settle on which side I am going to weigh in on...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Blindman: if SQL doesn't pan out for you, you can always open a Waffle Shop!

    I code defensively because I have been burned. Others may not have had the same experience (or may not have realized just why SQL Server did some of the things that they can't understand), so they code in different ways.

    I don't have religion when it comes to making others use my coding style. They are free to format their code as they see fit, subject only to the judgement of the SQL Engine.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    May 2011
    Posts
    11
    I know it's off-topic but what's the problem with the parenthesis??

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there's (nothing (wrong)) with (them), they (do nothing), but they (((also)) work when they are omitted)

    (ze goggles, zey do nossink!!)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •