SQL doubt in query

Question

Can someone give me some guidance in this consultation? How to find out how many possible shows watched?

For viewers of the day "24/07/2020" who passed the turnstile before the opening time of the show, list the ticket number, the time of entry, the name of the viewer and number of possible shows watched (considering only the entrance time).

SELECT i.numero, i.horaentrada, p.nome
FROM pessoa P
INNER JOIN espectador E ON e.cpfpessoa = p.cpf
INNER JOIN ingresso I ON e.cpfpessoa = i.cpfespectador
INNER JOIN show S ON s.datalineup = i.datalineupentrada
WHERE i.pertencedatalineup = '24/07/2020' and i.horaentrada < s.horainicio;

Tables

Person {Cpf, name}

Spectator {cpfpessoa, code}

Ticket {number, value, outgoing date, incoming time, incoming time, belonging to datalineup}

Lineup {datalineup}

Show {local name, schedule, datalineup}

Band {name, ordemapresentation, nomelocalshow, datalineupshow, scheduleshow}

Exit

HOURLY NUMBER NAME

---

   3   21:00      Corina Brandan                                    
   3   21:00      Corina Brandan                                    
   8   22:00      Cristovao Aranha 

I need you to show the amount of shows watched based on the time of entry.

Answer 1

Here’s a possible solution:

-- código #1
-- © José Diz (Porto SQL)

with Comparecimento as (
SELECT numero, datalineupentrada, horaentrada, cpfespectador
  from Ingresso
  where datalineupentrada = convert (date, '24/7/2020', 103)
)
SELECT C.numero, C.horaentrada, P.nome, count(*) as [Shows assistidos]
  from Comparecimento as C
       inner join Pessoa as P on P.cpf = C.cpfespectador
       inner join Show as S on s.datalineup = C.datalineupentrada
  where S.horainicio >= C.horaentrada
  group by C.numero, C.horaentrada, P.nome;

The CTE Attendance selects the tickets that were presented on the desired date. From this relationship are obtained the name of the person and the shows that would start after the entry of the person.

There are factors that can confuse when manipulating dates in SQL Server, leading to errors in execution almost always related to the ignorance of how SQL Server interprets literals (strings) containing date. So, whenever providing dates as literal, be aware to use in a way that does not generate misinterpretations, depending on how the LANGUAGE and/or the session DATEFORMAT is configured. Examples of errors that may occur, and their solutions, are in the article "Dominating dates and times in SQL Server”.