Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Without seeing some sample rows and expected results it's a little difficult to understand the question. Would this work?</p> <pre><code>SELECT Grandstand, LogHour, COUNT(TicketNo) AS TotalOccupancy FROM dbo.viewF1LogDetail WHERE LogDay = 14 AND ScanningLogLocationType = 'Grandstand' AND LogType = 1 AND LogHour &gt;= 9 AND LogHour &lt;= 15 GROUP BY Grandstand, LogHour; </code></pre> <p>If this doesn't work, an explanation of why would probably reveal the real intention of the query.</p> <p>UPDATE: </p> <p>I think I understand a bit better now. Try this:</p> <pre><code>WITH hourRows AS ( /* Get a base set of hours in the day */ SELECT TOP (24) ROW_NUMBER() OVER (ORDER BY name) hr FROM sys.objects ), ticketInfo AS ( /* Return the check in log event */ SELECT ld1.Grandstand, ld1.LogHour AS CheckInHour, checkout.LogHour AS CheckOutHour, ld1.TicketNo FROM viewF1LogDetail ld1 CROSS APPLY ( /* Apply the check out time to the set */ SELECT ld2.LogHour FROM viewF1LogDetail ld2 WHERE ld2.LogDay = 14 AND ld2.ScanningLogLocationType = 'Grandstand' AND ld2.LogType = 0 AND ld2.LogHour &gt;= 9 AND ld2.LogHour &lt;= 15 AND ld1.Grandstand = ld2.Grandstand AND ld1.TicketNo = ld2.TicketNo ) checkout WHERE ld1.LogDay = 14 AND ld1.ScanningLogLocationType = 'Grandstand' AND ld1.LogType = 1 AND ld1.LogHour &gt;= 9 AND ld1.LogHour &lt;= 15 ) SELECT ci.GrandStand, hr.hr AS LogHour, COUNT(ci.TicketNo) AS TotalOccupancy FROM hourRows hr INNER JOIN ticketInfo ci ON hr.hr &gt;= ci.CheckInHour AND hr.hr &lt;= ci.CheckOutHour GROUP BY ci.GrandStand, hr.hr ORDER BY ci.Grandstand; </code></pre> <p>Note that you should replace sys.objects in the hourRows CTE with a real Numbers table.</p>
    singulars
    1. This table or related slice is empty.
    1. POQuery Optimization for multiple query joined by union
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USJon Boulineau
    1. USJon Boulineau
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COAs far as I can tell, this does hourly change rather than aggregate up to the hour.
      singulars
      1. PO
      1. USAmy B
    2. COHi I added some sample rows now. This query will not work for me because this will only count all the check in made by the ticket. what I need is to get the status of a ticket until a given log hour. let say ticket 001 is at main grandstand until 14th hour but has left at 15th hour. so at 14th hour he will be included in the total occupancy count but will not be included in the count by 15th hour because he has already left the main grandstand.
      singulars
      1. PO
      1. USRomeo
    3. COyes, that's correct @DavidB.
      singulars
      1. PO
      1. USRomeo
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload