Can this be done in SQL? - I can do it in JAVA - Calculate Waitlists
I am developing some hardcoded rep开发者_开发技巧orts from embedded Java code to Crystal Reports
there is a function that calculates "waitlists"
My Datamodel is as follows (I've left out all of the columns and tables that are not relevant)
Each table is denoted as with * and the columns are denoted with a + their relationships are
Person table 1 to * Sessions table 1 to * Outcomes table*Person table
+id - primary key
*Sessions table
+parentid foreign key to Person.id
+id - primary key
+isValid
+session-name
*Outcomes table
+parentid foreign key to Sessions.id
+id -primary key
+session-name
The logic is as follows ( i need this in sql(MSSQL preferred)):
Count the number of occurrences of each outcome that complies with the following
get a list of unique outcomes for each person and for each of those outcomes
if there is no session with the same name as that-outcome & is valid & also has the same parent id as that-outcome's parent session
I hope that makes sense : any help is appreciated
Something like that:
SELECT
p.id AS personID
, s.id AS sessionID
, s.session-name AS sessionName
, o.id AS outcomeID
, o.session-name AS outcomeSessionName
FROM Person AS p
JOIN Sessions AS s
ON s.parentid = p.id
JOIN Outcomes AS o
ON o.parentid = s.id
WHERE NOT EXISTS
( SELECT *
FROM Sessions AS s2
WHERE s2.session-name = o.session-name
AND s2.isValid
AND s2.parentid = s.parentid
)
If you also want to count these outcomes, it would be more complex query. Perhaps it's better to have another query, that counts what you want (all outcomes? per person?)
精彩评论