MSSQL : Behöver hjälp med en query

[Mr Andersson]

Jag har använt en query-byggare för att ta fram en rapport från en databas till ett Aptus inpasseringsystem. Nu vill jag lägga till en kolumn för antal inpasseringar per användare men jag vet inte hur jag ska göra eftersom det inte är en existerande kolumn i databasen.
Det jag vill ha är (antalet rader från tabellen Event där Event.UserID = Users.Id och Event.eventNo = 2) som en egen kolumn.
Här är queryn jag har nu

Kod: Markera allt

Select Distinct
  Users.Name As Namn,
  Users.Blocked As Spärrad,
  Event.CreatedTime As Senaste_Passering,
  Users.Start As StartDatum,
  Users.Stop As StopDatum,
  Users.Card As TaggNummer,
  Users.CardLabel As Taggmärkning,
  TimeZone.Name As Tidzon,
  AuthorityInUser.start As Tidzon_StartDatum,
  AuthorityInUser.stop As Tidzon_StopDatum 
From
  Users Inner Join
  Event On Event.UserID = Users.Id Inner Join
  AuthorityInUser On AuthorityInUser.UserId = Users.Id Inner Join
  Authority On Authority.Id = AuthorityInUser.AuthorityId Inner Join
  TzInAuth On TzInAuth.AuthorityId = Authority.Id Inner Join
  TimeZone On TzInAuth.TimezoneId = TimeZone.Id
Where
  (Event.CreatedTime) In (Select
    Max(Event.CreatedTime)
  From
    Event
  Where
    Event.UserID = Users.Id And
    Event.eventNo = 2) And
  AuthorityInUser.removedDate Is Null
Order By
  Tidzon,
  Namn

Enligt det jag hittar på google ska man kunna göra det med union men jag är väldigt dålig på SQL så jag klarar inte att lösa det själv.
Jag testade lägga till "Union Select Count(*) As Antal_Passeringar From Event Where Event.UserID = Users.Id And Event.eventNo = 2" men då får jag felet "The multi-part identifier "Users.Id" could not be bound"

[johano]

Det kan enkelt göras med en "deriverad tabell" (typ som en subselect som "blir" en egen tabell)
Typ såhär:

Kod: Markera allt

Select Distinct
  Users.Name As Namn,
  Users.Blocked As Spärrad,
  Passering.Senaste,
  COALESCE(Passering.Antal, 0) as "Antal",
  Users.Start As StartDatum,
  Users.Stop As StopDatum,
  Users.Card As TaggNummer,
  Users.CardLabel As Taggmärkning,
  TimeZone.Name As Tidzon,
  AuthorityInUser.start As Tidzon_StartDatum,
  AuthorityInUser.stop As Tidzon_StopDatum 
From Users
Inner Join AuthorityInUser On AuthorityInUser.UserId = Users.Id 
Inner Join Authority On Authority.Id = AuthorityInUser.AuthorityId 
Inner Join TzInAuth On TzInAuth.AuthorityId = Authority.Id 
Inner Join TimeZone On TzInAuth.TimezoneId = TimeZone.Id
Left Join
(
SELECT UserID, Count(*), Max(CreatedTime) FROM Event WHERE Event.eventNo = 2 GROUP BY UserId
) AS Passering(UserId, Antal, Senaste) ON Users.Id = Passering.UserId
Where 
AuthorityInUser.removedDate Is Null
Order By
Tidzon,
Namn

Edit: glömde where EventNo-villkoret
Edit2: fixade Passering.Id => Passering.UserId
/johan

[Mr Andersson]

Tack!
Fungerar klockrent förutom en liten miss.
AS Passering(UserId, Antal, Senaste) ON Users.Id = Passering.UserId

Glömde nämna i OP ifall någon annan vill använda queryn är att MultiAccess har stöd för flera tidszoner per användare men detta listar bara den första som hittas. Det är långt över mina SQL-kunskaper att försöka lista alla, och vi använder bara en tidzon per användare så det är inte en komplett rapport.

I övrigt så är Aptussystemet ganska trevligt att använda. De har ett watchdog-liknade program som bevakar databasen så vill man t.ex. fjärrstyra en dörr eller ändra i användardatabasen lägger man bara in en rad i Commands-tabellen så sköter deras mjukvara om att ladda upp det till hårdvaran, och sen skriver den tillbaka en resultatkod.

[johano]

Ah, såklart