SitePoint Sponsor |
|
User Tag List
Results 1 to 25 of 27
-
Dec 1, 2008, 10:06 #1
- Join Date
- Feb 2003
- Location
- Florida, USA
- Posts
- 2,322
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
Problem with multiple records returned in query
This is on a MS SQL 2005 Server on a Windows 2003 Server box.
We are attempting to pull back some unique records on a rather large database, however I am encountering an error in my SQL where I am getting back multiple results when I expect one.
For example.
In this database (a law enforcement one) A unit that belongs to an Incident can have multiple dispositions. My SQL is returning back that same unit and incident for every disposition is has. For example: if the unit has 3 dispositions, rather than getting back 1 record of that single unit, I get back 3, with a unique disposition for each record.
My SQL looks like this :
SELECT
DISTINCT LEI.IncidentNumber,
U.UnitNumber
FROM
LEIncident AS LEI
LEFT JOIN LEIncidentUnit AS LEIU
ON LEIU.IncidentID = LEI.IncidentID
LEFT JOIN Unit AS U
ON LEIUP.UnitID = U.UnitID
LEFT JOIN LEIncidentCFSDisposition AS LEICFSD
ON LEICFSD.IncidentID = LEI.IncidentID
LEFT JOIN ValidationSetEntry AS VSE1
ON LEICFSD.vsDisposition = VSE1.EntryID
WHERE
LEIU.PrimaryUnitFlag = 1 -- Unit is the Primary
AND Month( LEI.CreateDateTime ) = '10'
AND Year( LEI.CreateDateTime ) = '2008'
ORDER BY
LEI.IncidentNumber ASC
(In the above code, we are pulling back records for just 10/2008, and the VALIDATIONSETENTRY table acts as a LOOKUP for fields that are prefaced with 'vs' That is how we can determine the actual "description" of the Disposition, rather that its record ID.
Sorry if this is rather intricate, but I appreciate any help offered. Thank you in advance.
-
Dec 1, 2008, 10:18 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
that's the way SQL is supposed to work
if you want to "collapse" multiple rows into one, one way is with GROUP BY
however, this means you won't be able to show detailed information, but rather, only aggregate information, like for example only a count of the number of incidents, not the incidents themselves
make sense?
-
Dec 1, 2008, 10:28 #3
- Join Date
- Feb 2003
- Location
- Florida, USA
- Posts
- 2,322
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
That makes sense. I kinda thought that the LEFT JOINS would individually mark a separate record for each one of those.
And what you say is EXACTLY what we're looking for. We want an aggregate ability to just count the right number of rows.
But I know in the few times I've used GROUP BY, I get errors saying that the SELECT does not contain an AGGRAGATE function (or something to that effect)
Would I have a single GROUP BY or multiple? If I use GROUP BY, don't I have to modify my SELECT statement?
-
Dec 1, 2008, 10:40 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
okay, let's analyze what's happening in your FROM clause first
to start with, i think you're using LEFT OUTER JOINs incorrectly
the FROM clause starts out like this --
FROM LEIncident AS LEI
LEFT JOIN LEIncidentUnit AS LEIU
then you have this WHERE clause --
WHERE
LEIU.PrimaryUnitFlag = 1 -- Unit is the Primary
AND Month( LEI.CreateDateTime ) = '10'
AND Year( LEI.CreateDateTime ) = '2008'
the WHERE condition on the LEI date column means that you are interested only in certain incidents, and that's good
however, the WHERE condition on the LEIU column means that the LEFT OUTER JOIN will never return unmatched LEI rows, because unmatched LEI rows would have NULLs in all the columns from the LEIU table, but then the WHERE condition stipulates that PrimaryUnitFlag has to be non-NULL
so basically i think you really want INNER JOINs, right?
with me so far?
to carry on, would you mind indicating the cardinality (many-to-one, or one-to-many) of each of the joins
-
Dec 1, 2008, 11:14 #5
- Join Date
- Feb 2003
- Location
- Florida, USA
- Posts
- 2,322
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
Trying to grasp the concepts you're teaching me. I know that with SQL, you have to read it in a different order than just top to bottom (thus you start me out with reading the FROM clause, etc.)
My problem offhand was understanding the difference between a LEFT JOIN and an INNER JOIN (I know it has to do with the left/right tables being joined and whether or not to include the matching rows.
Let's see. One of the problems I noted was WHERE I put my post-WHERE clauses. For example, I noticed that I got a different recordcount returned when I did
LEFT JOIN X
ON X.field = Y.field
AND X.other_field = 'foo'
vs.
LEFT JOIN X
ON X.field = Y.field
WHERE
X.other_field = 'foo'
I find that when I put the conditional clauses after the WHERE, I get better results seemingly.
As far as Many To One or One To Many, here's the best I can explain it.
There a multiple incidents
For 1 incident, it can have MULTIPLE units (but only ONE is marked as Primary, indicated by the LEIU.PrimaryUnitFlag flag - we only want primary units back)
But incident can be given MULTIPLE dispositions. We don't care which disposition, we just don't want to pull back 1 record for each disposition on the incident.
I didn't even give you the entire problem. I have a separate query that is (successfully) pulling back officers from the PERSONNEL table who belong to 'D Platoon'. It pulls back their Personnel.PersonnelID which you'll see is used later on:
What we do, is that for each Person this query pulls back, we run the query you and I are working on. The sequence is:
1 Incident can have X Units (but only 1 is Primary) and that Unit references what can be 1 OR X Personnel in that Unit (because a Unit can be manned by 1 person OR sometimes there can be multiple personnel in a unit)
So we can plugin the Officer's Personnel ID as a stipulation to the records returned. All in all, the tables used (which are, thankfully, self explanatory) are:
LEIncidents (LE is a preface for Law Enforcement) which I call LEI
LEUnits which I call LEIU (Units assigned to the Incident)
Unit which I call U (a lookup of the Unit's info, such as number, etc.)
LEUnitPersonnel which I call LEIP (Personnel belonging to the Unit at the time of the incident)
Personnel which I call P (a cross-reference of the UnitPersonnel's info, such as last/first name, etc.)
LEUnitCFSDisposition which I call LEICFSD (a cross-referencing table which contains a vsDisposition field we run against ValidationSetEntry)
ValidationSetEntry which I call VSE (a lookup table that gives us back the description of the Disposition pulled from the LEUnitCFSDisposition) btw (CFS = Call For Service)
I really hope I gave you what you needed to know here without confusing the both of us. If not, feel free to explain and I will do my best to get you the info. I have a bad habit of just LEFT JOINING a table when I need information from it.
-
Dec 1, 2008, 11:41 #6
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
let's take things slowly, building up the join as we go
Code:FROM LEIncident AS LEI INNER JOIN LEIncidentUnit AS LEIU ON LEIU.IncidentID = LEI.IncidentID AND LEIU.PrimaryUnitFlag = 1 INNER JOIN Unit AS U ON U.UnitID = LEIU.UnitID WHERE LEI.CreateDateTime >= '2008-10-01' AND LEI.CreateDateTime < '2008-11-01'
- inner joins
- a join condition in the ON clause
- corrected typo in LEIU alias
- initial filter conditions in the WHERE clause (written in the most efficient way)
now, what do you want to join next? just one step at a time, okay?
you said that incidents can have multiple dispositions, but you don't care what they are, so my question is, do you really need to join to the dispositions, and if so, why?
-
Dec 1, 2008, 11:53 #7
- Join Date
- Feb 2003
- Location
- Florida, USA
- Posts
- 2,322
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
Code:-- Test Code SELECT LEI.IncidentNumber, U.UnitNumber FROM LEIncident AS LEI INNER JOIN LEIncidentUnit AS LEIU ON LEIU.IncidentID = LEI.IncidentID AND LEIU.PrimaryUnitFlag = 1 INNER JOIN LEIncidentUnitPersonnel AS LEIUP ON LEIUP.UnitID = LEIU.UnitID INNER JOIN Unit AS U ON U.UnitID = LEIUP.UnitID WHERE LEI.CreateDateTime > '2008-10-01' AND LEI.CreateDateTime < '2008-10-31'
Code:-- Test Code SELECT LEI.IncidentNumber, U.UnitNumber, P.LastName, P.FirstName FROM LEIncident AS LEI INNER JOIN LEIncidentUnit AS LEIU ON LEIU.IncidentID = LEI.IncidentID AND LEIU.PrimaryUnitFlag = 1 INNER JOIN LEIncidentUnitPersonnel AS LEIUP ON LEIUP.UnitID = LEIU.UnitID INNER JOIN Unit AS U ON U.UnitID = LEIUP.UnitID INNER JOIN Personnel AS P ON P.PersonnelID = LEIUP.PersonnelID WHERE LEI.CreateDateTime > '2008-10-01' AND LEI.CreateDateTime < '2008-10-31' AND P.PersonnelID = -325
-
Dec 1, 2008, 12:03 #8
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
i'm lost
from the name of the table, i would expect that LEIncidentUnit is the relationship table between incidents and their units
and yet, you're joining LEIncidentUnit to LEIncidentUnitPersonnel and then LEIncidentUnitPersonnel to Unit
why?
also, why did you exclude all of the incidents on the last day of october?
-
Dec 1, 2008, 12:15 #9
- Join Date
- Feb 2003
- Location
- Florida, USA
- Posts
- 2,322
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
Oh, wait, that should be >= and <= for the dates, my bad.
Ok, here's how I'll try to explain it.
LEIncident (LEI) holds unique Incidents
LEIncidentUnit (LEIU) has Foreign Keys that link:
LEIU.IncidentID to LEI.IncidentID and
LEIU.UnitID which links to U.UnitID
LEIncidentUnitPersonnel (LEIUP) has Foreign Keys that link :
LEIUP.IncidentID = LEI.IncidentID and
LEIUP.UnitID = U.UnitID and
LEIUP.PersonnelID = P.PersonnelID
Personnel (aliased as P) contains fields we use to verify the person (LastName, FirstName) and have the record identifier, PersonnelID
Units (aliased as U) contains fields we use to verify the unit (UnitNumber) So with modifications, we have:
Code:-- Test Code SELECT TOP 100 LEI.IncidentNumber, U.UnitNumber, P.LastName, P.FirstName FROM LEIncident AS LEI INNER JOIN LEIncidentUnit AS LEIU ON LEIU.IncidentID = LEI.IncidentID AND LEIU.PrimaryUnitFlag = 1 INNER JOIN LEIncidentUnitPersonnel AS LEIUP ON LEIUP.UnitID = LEIU.UnitID INNER JOIN Unit AS U ON U.UnitID = LEIUP.UnitID INNER JOIN Personnel AS P ON P.PersonnelID = LEIUP.PersonnelID WHERE LEI.CreateDateTime >= '2008-10-01' AND LEI.CreateDateTime <= '2008-10-31' AND P.PersonnelID = -265 -- This is the individual we're currently checking
-
Dec 1, 2008, 12:36 #10
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
nope, still wrong
if you are indeed storing datetime values (and not date values), then you will eliminate all incidents on october 31st except for any that just happened to occur at midnight precisely
yeah, i guess this works, i was just expecting to see it as LEI > LEIU > P > LEIUP
the colour coding helps, doesn't it?
as for the last condition in the WHERE clause, this should be moved to the approriate join's ON condition
the WHERE clause should contain only the most limiting filter conditions for the first table in the chain
-
Dec 1, 2008, 12:58 #11
- Join Date
- Feb 2003
- Location
- Florida, USA
- Posts
- 2,322
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
(Forego all previous colors - I changed them now to introduce a table I had forgotten, LEIncidentCFSDisposition). Also, I temporarily added "TOP 100" to the SELECT to prevent times where 600,000+ records are coming back (don't want to tax our live database like that with faulty SELECTS I'm making) The account I'm connected as only has SELECT rights, so no worry on accidentally screwing up like database.
Code:-- Test Code SELECT TOP 100 LEI.IncidentNumber, U.UnitNumber, P.LastName, P.FirstName, P.MiddleName, VSE1.Description AS Disposition, LEI.CreateDateTime FROM LEIncident AS LEI INNER JOIN LEIncidentUnit AS LEIU ON LEIU.IncidentID = LEI.IncidentID AND LEIU.UnitID = U.UnitID AND LEIU.PrimaryUnitFlag = 1 -- Unit must be primary INNER JOIN LEIncidentUnitPersonnel AS LEIUP ON LEIUP.PersonnelID = P.PersonnelID AND LEIUP.UnitID = U.UnitID AND LEIUP.IncidentID = LEI.IncidentID INNER JOIN LEIncidentCFSDisposition AS LEICFSD ON LEICFSD.IncidentID = LEI.IncidentID INNER JOIN Personnel AS P ON P.PersonnelID = LEIUP.PersonnelID AND P.PersonnelID = -265 -- Tested Individual INNER JOIN Unit AS U ON U.UnitID = LEIU.UnitID INNER JOIN ValidationSetEntry AS VSE ON VSE.EntryID = LEICFSD.vsDisposition WHERE LEI.CreateDateTime >= '2008-10-01' AND LEI.CreateDateTime < '2008-11-01'
Code:Msg 4104, Level 16, State 1, Line 2 The multi-part identifier "U.UnitID" could not be bound. Msg 4104, Level 16, State 1, Line 2 The multi-part identifier "P.PersonnelID" could not be bound. Msg 4104, Level 16, State 1, Line 2 The multi-part identifier "U.UnitID" could not be bound. Msg 4104, Level 16, State 1, Line 2 The multi-part identifier "VSE1.Description" could not be bound.
You've been very helpful as always Rudy. Thanks.
-
Dec 1, 2008, 14:03 #12
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
you're going a bit too fast for me, i wanted to do one table at a time
when i last knew what was going on (remember, i'm still trying to understand the one-to-many relationships), we had reached this point --Code:FROM LEIncident AS LEI INNER JOIN LEIncidentUnit AS LEIU ON LEIU.IncidentID = LEI.IncidentID AND LEIU.PrimaryUnitFlag = 1 INNER JOIN LEIncidentUnitPersonnel AS LEIUP ON LEIUP.UnitID = LEIU.UnitID INNER JOIN Unit AS U ON U.UnitID = LEIUP.UnitID INNER JOIN Personnel AS P ON P.PersonnelID = LEIUP.PersonnelID AND P.PersonnelID = -265 -- This is the individual WHERE LEI.CreateDateTime >= '2008-10-01' AND LEI.CreateDateTime < '2008-11-01'
you have LEI > LEIU > LEIUP which retrieves every LEIUP connected to the LEIU
later you return the P for every LEIUP, and only then throw everybody away except the one guy you want
so i would move the condition up higher in the chain of joins --Code:FROM LEIncident AS LEI INNER JOIN LEIncidentUnit AS LEIU ON LEIU.IncidentID = LEI.IncidentID AND LEIU.PrimaryUnitFlag = 1 INNER JOIN LEIncidentUnitPersonnel AS LEIUP ON LEIUP.UnitID = LEIU.UnitID AND LEIUP.PersonnelID = -265 -- This is the individual INNER JOIN Unit AS U ON U.UnitID = LEIUP.UnitID INNER JOIN Personnel AS P ON P.PersonnelID = LEIUP.PersonnelID WHERE LEI.CreateDateTime >= '2008-10-01' AND LEI.CreateDateTime < '2008-11-01'
-
Dec 1, 2008, 14:46 #13
- Join Date
- Feb 2003
- Location
- Florida, USA
- Posts
- 2,322
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
Your code works, but hits my TOP 100 cap. I took off the cap and it returned 628,104 rows.
With the 100 cap in place, I noticed that (I returned LEI.IncidentNumber from the SELECT), the IncidentNumber is the exact same for all 100 records returned.
-
Dec 1, 2008, 14:56 #14
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
and the problem with that is... ?
in other words, maybe it returns that many rows because there are that many rows to return, based on the conditions
the nice thing about writing the FROM clause in a logical sequence is that you can "peel off" the later joins to see where something is going amiss
what do you get for this queryCode:SELECT COUNT(*) FROM LEIncident AS LEI WHERE LEI.CreateDateTime >= '2008-10-01' AND LEI.CreateDateTime < '2008-11-01'
Code:SELECT COUNT(*) FROM LEIncident AS LEI INNER JOIN LEIncidentUnit AS LEIU ON LEIU.IncidentID = LEI.IncidentID AND LEIU.PrimaryUnitFlag = 1 WHERE LEI.CreateDateTime >= '2008-10-01' AND LEI.CreateDateTime < '2008-11-01'
-
Dec 1, 2008, 16:14 #15
- Join Date
- Feb 2003
- Location
- Florida, USA
- Posts
- 2,322
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
The initial problem is that we are wanting a tally back, but at most, 1 incident can only have 1 primary unit (with 1 OR more personnel) but for us to have 100+ records where the IncidentID is the same, then the tally would be wrong. **edit** To expand on that. They want to know how many incidents said personnel responded to where he was the Primary Unit of that incident. Hopefully this explains why 100+ records of the same incident won't work for us, as we're wanting a distinct tally of the separate incidents.
To the first code you provided, I got back:
(no column name) 13626
And for the second I got back:
(no column name) 12878
-
Dec 1, 2008, 16:37 #16
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
thank you, that is a most descriptive requirements spec
as for the tally, we're getting there, hold on
the first thing in SQL development is joining the tables correctly
i think you will agree that if you get this step wrong, there's not much else you can do in the rest of the query to come up with a correct result (not that your approach was wrong, because it wasn't)
but your spec suggests to me that you want to tally a particular person's incidents, rather than some kind of analysis of last month's incidents in which a particular person was involved
note that our where clause, which i thought would be most restrictive, was on the LEI table, but these specs suggest we should start the FROM clause with P, and a WHERE condition on this person's unique id
i know that this seems like splitting hairs, but when the joins are made from PK to FK, rather than the other way around--and i do realize this may seem obscure when stated with such generality--you often get better performance in addition to clarity of intent
does any of this help?
-
Dec 1, 2008, 16:41 #17
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Last edited by r937; Dec 1, 2008 at 16:58. Reason: Edit: primary units
-
Dec 1, 2008, 18:27 #18
- Join Date
- Feb 2003
- Location
- Florida, USA
- Posts
- 2,322
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
Still stumped as ever, but I tried to put my best foot forward
Code:-- Reverse Order SELECT TOP 200 P.LastName, P.FirstName, P.PersonnelID, LEI.IncidentNumber, LEIU.PrimaryUnitFlag, LEI.CreateDateTime FROM Personnel AS P INNER JOIN LEIncidentUnitPersonnel AS LEIUP ON LEIUP.PersonnelID = P.PersonnelID LEFT JOIN LEIncidentUnit AS LEIU ON LEIU.IncidentUnitID = LEIUP.UnitID --AND LEIU.PrimaryUnitFlag = 1 INNER JOIN LEIncident AS LEI ON LEIUP.IncidentID = LEI.IncidentID AND LEI.CreateDateTime >= '2008-10-01' AND LEI.CreateDateTime < '2008-11-01' WHERE P.PersonnelID = -265 -- Test Person
The same results tell me that the CREATEDATETIME and INCIDENTNUMBER are unique, which is a good indicator as far as data goes. Unique dates/time created along with their respective incident numbers - that's a step in the right direction.
However, understandably, once I remove the comment for the PrimaryUnitFlag, I get back 0 records (because as-is, all PrimaryUnitFlag fields are coming back NULL)
Also, the LAST/FIRSTNAME on each record is the same, so I think the clause on the P table is working fine.
I have a bad feeling the people who created this table may have screwed up. There is a LEIncidentUnits table (LEIU) but there is also a Units table (U). In the LEIU, the PK is called IncidentUnitID but there is also a FK called UnitID. In the Unit table, the PK is called UnitID. I would assume by the current naming structure that the LEIU.UnitID FK points to the Unit.UnitID PK field, but if that's the case, why the heck would the LEIncidentUnitPersonnel (LEIUP) table have a FK UnitID as well (cause it does)?? Very confusing why they set this up, but I have a stronger feeling it's just because I don't know their schema, and they did everything right in the end.
-
Dec 1, 2008, 18:45 #19
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Dec 2, 2008, 08:29 #20
- Join Date
- Feb 2003
- Location
- Florida, USA
- Posts
- 2,322
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
Absolutely not a problem Rudy. I'm VERY appreciative of how helpful you've been.
-
Dec 4, 2008, 10:31 #21
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
sorry for the delay
i've re-read this thread and the last part of it makes me very uneasy about going any further
one thing i do hope you may have learned is how to do "forensic" querying
start with first table that will have the most restriction (WHERE conditions), and then build up your FROM clause one table at a time, at each step verifying that the correct rows are being joined
later, when all the steps work, and the join is returning the correct data, only then should you start with the GROUP BY
make sense?
-
Dec 4, 2008, 10:37 #22
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
identifying relationships are transitive
for example if A has many Bs, and each B has many Cs, then C will have a foreign key to A by virtue of its foreign key to B which is the same as B's primary key, which will include the foreign key to A (because it's an identifying relationship)
if that made no sense, don't worry about it
you need to find out what each relationship means
two different FKs to the UnitID might have different roles, e.g. one unit initiated the incident, another unit handled the incident (i'm obviously making this up)
-
Dec 8, 2008, 09:39 #23
- Join Date
- Feb 2003
- Location
- Florida, USA
- Posts
- 2,322
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
I hear what you're saying. Sadly, the developer of our database in question won't hand over their schema (understandably) but at least they DO help us out when we want to know what field/table houses the data we're looking for and how they get it.
(I've asked them SO MANY TIMES for some kind of tool that would just show us the SQL that's being run on the DB from the frontend tool they built for us, but it's been a no-go)
Here's what I have so far, and I feel it's so far, pulling back the right data.
Code:SELECT TOP 200 P.LastName, P.FirstName, P.PersonnelID, LEI.IncidentNumber, LEIU.PrimaryUnitFlag, LEI.CreateDateTime FROM Personnel AS P INNER JOIN LEIncidentUnitPersonnel AS LEIUP ON LEIUP.PersonnelID = P.PersonnelID LEFT JOIN LEIncidentUnit AS LEIU ON LEIU.IncidentUnitID = LEIUP.UnitID AND COALESCE(LEIU.PrimaryUnitFlag,1) = 1 INNER JOIN LEIncident AS LEI ON LEIUP.IncidentID = LEI.IncidentID AND LEI.CreateDateTime >= '2008-10-01' AND LEI.CreateDateTime < '2008-11-01' WHERE P.PersonnelID = -265 -- Test Person AND LEI.vsSource IN (72989, 72997) ORDER BY IncidentNumber ASC
-
Dec 8, 2008, 10:45 #24
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Dec 8, 2008, 16:52 #25
- Join Date
- Feb 2003
- Location
- Florida, USA
- Posts
- 2,322
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
Ah, OK, that makes sense.
I've taken what you've taught me and got this code, and it REALLY seems to be on the mark. (We're verifying on the front-end side best we can)
Code:SELECT TOP 200 LEI.IncidentID, LEI.IncidentNumber, (P.LastName + ', ' + P.FirstName + ' ' + P.MiddleName) AS OfficerName, LEIU.PrimaryUnitFlag, VSE1.Description AS Disposition, VSE2.EntryValue AS IncidentSource, VSE3.EntryValue AS IncidentType, LEI.vsIncidentType FROM LEIncident AS LEI INNER JOIN LEIncidentUnit AS LEIU ON LEIU.IncidentID = LEI.IncidentID AND LEIU.PrimaryUnitFlag = 1 -- Unit is the primary INNER JOIN Unit AS U ON U.UnitID = LEIU.UnitID AND U.UnitID = LEIU.UnitID INNER JOIN LEIncidentUnitPersonnel AS LEIUP ON LEIUP.IncidentID = LEI.IncidentID AND LEIUP.UnitID = U.UnitID INNER JOIN Personnel AS P ON P.PersonnelID = LEIUP.PersonnelID AND P.PersonnelID = -1361 -- Personnel to test INNER JOIN LEIncidentUnitDisposition AS LEIUD ON LEIUD.IncidentID = LEI.IncidentID AND LEIUD.UnitID = U.UnitID INNER JOIN ValidationSetEntry AS VSE1 ON LEIUD.vsDisposition = VSE1.EntryID INNER JOIN ValidationSetEntry AS VSE2 ON LEI.vsSource = VSE2.EntryID INNER JOIN ValidationSetEntry AS VSE3 ON LEI.vsIncidentType = VSE3.EntryID AND VSE3.EntryID IN (-20456, -20486, -21157, 72918, -20524) -- Traffic Arrest, Traffic Incident, Follow-Up, Suspicious Incident, Suspicious Person WHERE LEI.CreateDateTime >= '2008-10-01' -- From the beginning time AND LEI.CreateDateTime < '2008-11-01' -- To the ending time AND LEI.vsSource IN (72989, 72997) -- Call source is 'officer' or 'officer-initiated' ORDER BY LEI.IncidentNumber ASC
Bookmarks