SQL Query :S

ok this is to those of you who know sql and even more that know about the cmis database too

strSQL = "SELECT DISTINCT(nstupersonal.StudentId), nstupersonal.Forename, nstupersonal.Surname from nstupersonal inner join ( Students INNER JOIN SENSTUSTAGES ON Students.StudentId = SENSTUSTAGES.StudentId ) ON students.StudentId = nstupersonal.studentid where nstupersonal.SetId = '"& varDataSet &"' and students.courseyear=" & varYr & " And nstupersonal.LeftSchool ='N' and NOT senstustages.stageid = 6 Order By NStupersonal.Surname, Nstupersonal.Forename"


Above is my sql query to pull through all the kids in the year with a sen statement problem is where the stageid is 6 it still pulls those through so any suggestions?

Try this one. I'm not 100% but I think moving the 'NOT senstustages=6' to the join on that table will work.

Code:

---

SELECT DISTINCT(nstupersonal.StudentId), nstupersonal.Forename, nstupersonal.Surname
        FROM nstupersonal
        INNER JOIN(
                Students INNER JOIN senstustages
                ON Students.StudentId = SENSTUSTAGES.StudentId
                WHERE NOT senstustages.stageid = 6
        )
        ON students.StudentId = nstupersonal.studentid
        WHERE nstupersonal.SetId = '"& varDataSet &"'
        AND students.courseyear=" & varYr & "
        AND nstupersonal.LeftSchool = 'N'
        ORDER BY NStupersonal.Surname, Nstupersonal.Forename

---

That is some SQL statement.

What client, language are you running this script in? You are referencing variables that are not shown. So would take time for me to replicate.

One suggestoin is that I prefer to use != rather than NOT.

I suspect that the Select distinct and inner join are not working in the way you hoped. Could you paste and an example without variables in ? So i can try and run it locally.

Would it not be easier to write a report within Facility, you do know that reports have been extended to support running queries againt many to many tables now. So you can link reports together better using different report focuses ?

Looks pretty trivial to me :-)

Definitely avoid using "not stageid = 6"
Assuming this is MS SQL then you can do that as stageid<>6 - I've seen weird problems using NOT (basically, it's trying to decide if something is true or false but you really just want to know that it's not 6)

I think I would do:

Code:

---

SELECT DISTINCT(nstupersonal.StudentId), nstupersonal.Forename, nstupersonal.Surname
  FROM nstupersonal INNER JOIN Students on students.StudentId = nstupersonal.studentid
  INNER JOIN senstustages ON Students.StudentId = SENSTUSTAGES.StudentId
  WHERE nstupersonal.SetId = '" & varDataSet & "'
  AND students.courseyear=" & varYr & "
  AND nstupersonal.LeftSchool = 'N'
  and senstustages.stageid <> 6
  ORDER BY NStupersonal.Surname, Nstupersonal.Forename

---

because I find it easier to read (show the joins, then the conditions) but I'm not a real SQL expert. (A real SQL expert going by the title of his book is Joe Celko - book called SQL for Smarties which is much better than "for dummies" :-))

Sorry i havent replied guys had problems getting on the site, i know facility has improved its reports but its still just not enough for my school.

I got the above query working i added in an extra datset check in one of the joined tables that solved the issue.

Thanks for all the input it is appreciated

i now have a assessment view that does all the traffic light effects plus tells me where iam missing assement data, also it allows us to filter for children with sen and down to different groups male female etc at at any time create a spreadsheet output with the applied filters for staff to use in analysis.

Kained, can you give us a bit more background on this, it sounds very interesting.

We use SIMS here and I'm looking at creating a graph for each student showing their progress from before secondary school through to their year 11 GCSE stage. The staff would like the visual aid a graph to instantly show whether the student is above or below their predicted grades.

Our current plan is to export data to a spreadsheet and then parse a csv version of it to create the graph images, that could then be automatically imported into their report templates, using mail-merge. At this stage it is only a plan (we're not sure how cunning) and will require a fair bit of programming.

I seem to remember reading here somewhere that accessing SIMS data with SQL is not recommended or supported?

Your thoughts?

Kained I did start an eportal.net project that started to look at data, not had much time to work on it though. Have you written any SQL statements that can pull data efficiently from the nsturesults table ?

I am making use of the internal document server to store information that is not readily available in Eportal right now and it is working better than my initial eportal.net attempt :)

I do intend to go back and play with the eportal.net project though, so would great if you would be prepared to share any useful sql statements you have created :)

Replying to my own reply :)

Made further progress with this in that I have created SQL statements that match singlesetexams and singlesetexamsubject

From there I am going to use some c# to match the exam and assessment criteria data to the returned row.

I only have time time to work on this out of work (which makes me very sad!)

Will hopefully with community when I have a working prototype.

Damn keep missing these replies, sorry about the slow response been busy, i use asp and link through with sql statements to the CMIS database i will give help to anyone that needs it i dont know how "efficient" my code is but all i can say is that in under 20 seconds the staff have the information they desire so it will do.

I am not familiar with dot.net so i am not sure how i would go about it with that language but the approach i took was return all rows for a student for the required exam from nsturesults then i split the information in criteria data on the mapped id to give me my result i santised it then did some comparisions on that data.

with the sims question they never recommend you use there database because you may save the school spending money they can take, although there probably more legit reasons but every thing i have done so far hasnt caused any problems.

Not sure how well this will show up. But the black arrows are showing the comparison between the schools department targets(K3DTarg) and FFT targets (K3FTarg) First students K3Dtarg boxes are red as they are lower than the K3FTarg. The pink arrow is showing that the Y7 target is higher than the Department target set for year 7, and the Yellow arrow is showing that the Year 8 target progress and the Y8 target.

http://www.monkeyx.net/eportal.png

This is what is possible in eportal directly. This view is available at teaching group level. The side I am working on will allow to view at year group level and still relate top teaching group that the result came from. Teachers would not be able to see data analysed at this level anyway.

I am using asp.net as ado.net gives faster and easier access to data, it also allows for auto sorted columns/paged data with virtually no code :) I am just wanting to see I can make life easier on the data analysis side, as opposed to teaching staff looking at data they entered.

In theory (according to Serco Helpdesk) what is shown above is not actually possible :P

we dont have as many comparisons, we simply compare between data and target and give 5+ result amount and percentage for ks3 and A* - C for KS4 in a year view i will get a screenie up for ya