+ Post New Thread
Results 1 to 11 of 11
Web Development Thread, SQL Query :S in Coding and Web Development; ok this is to those of you who know sql and even more that know about the cmis database too ...
  1. #1

    Join Date
    Feb 2007
    Location
    Lincolnshire
    Posts
    120
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    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?

  2. #2

    webman's Avatar
    Join Date
    Nov 2005
    Location
    North East England
    Posts
    8,401
    Thank Post
    637
    Thanked 961 Times in 661 Posts
    Blog Entries
    2
    Rep Power
    319

    Re: SQL Query :S

    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

  3. #3
    monkeyx's Avatar
    Join Date
    Nov 2006
    Posts
    364
    Thank Post
    8
    Thanked 52 Times in 41 Posts
    Rep Power
    25

    Re: SQL Query :S

    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 ?

  4. #4

    Join Date
    Aug 2005
    Location
    London
    Posts
    3,154
    Thank Post
    114
    Thanked 527 Times in 450 Posts
    Blog Entries
    2
    Rep Power
    123

    Re: SQL Query :S

    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" :-))

  5. #5

    Join Date
    Feb 2007
    Location
    Lincolnshire
    Posts
    120
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    Re: SQL Query :S

    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.

  6. #6

    Join Date
    Jan 2007
    Location
    Lowestoft, Suffolk
    Posts
    84
    Thank Post
    6
    Thanked 4 Times in 4 Posts
    Rep Power
    16

    Re: SQL Query :S

    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?

  7. #7
    monkeyx's Avatar
    Join Date
    Nov 2006
    Posts
    364
    Thank Post
    8
    Thanked 52 Times in 41 Posts
    Rep Power
    25

    Re: SQL Query :S

    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

  8. #8
    monkeyx's Avatar
    Join Date
    Nov 2006
    Posts
    364
    Thank Post
    8
    Thanked 52 Times in 41 Posts
    Rep Power
    25

    Re: SQL Query :S

    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.

  9. #9

    Join Date
    Feb 2007
    Location
    Lincolnshire
    Posts
    120
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    Re: SQL Query :S

    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.

  10. #10
    monkeyx's Avatar
    Join Date
    Nov 2006
    Posts
    364
    Thank Post
    8
    Thanked 52 Times in 41 Posts
    Rep Power
    25

    Re: SQL Query :S

    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.



    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

  11. #11

    Join Date
    Feb 2007
    Location
    Lincolnshire
    Posts
    120
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    Re: SQL Query :S

    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

SHARE:
+ Post New Thread

Similar Threads

  1. Query of existing query data in PHP
    By markwilliamson2001 in forum Web Development
    Replies: 5
    Last Post: 5th October 2007, 08:43 AM
  2. Logon.bat query
    By speckytecky in forum Scripts
    Replies: 3
    Last Post: 20th April 2007, 10:08 AM
  3. Training Query
    By derer1 in forum General Chat
    Replies: 14
    Last Post: 12th February 2007, 02:10 PM
  4. DNS LDAP query
    By SpuffMonkey in forum Wireless Networks
    Replies: 1
    Last Post: 5th February 2007, 04:26 PM
  5. Web query in Excel
    By pinemarten in forum How do you do....it?
    Replies: 2
    Last Post: 20th January 2006, 09:22 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •