• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

missing dates on query output.

Explorer ,
Aug 21, 2016 Aug 21, 2016

Copy link to clipboard

Copied

I'm using cf 9 on a Windows platform

I can run an SQL statement wherein my students who have been absent for 30 days will appear on this output list. Where I am having an issue is that next to each instructor I would like to show the student's last date of attendance. This somehow I was not showing up when I run my query. The query that I am using is as follows:

SELECT Stuid, trim(concat(name.fname,' ',name.lname)) AS OVER_30_Days, ltfname as instructor, Max(meedate) FROM name LEFT JOIN meeting ON meeStuid = Stuid and meedate BETWEEN NOW() - INTERVAL 31 DAY AND NOW() LEFT JOIN squadlt on squadlt = ltid WHERE meeStuid is null AND  type = 'pupil' AND city = 'jackson' AND status = 'a' AND Stuid <> '432' and Stuid <> '5' and Stuid <> '183'  ORDER BY ltfname, OVER_30_Days

The resulting list below displays as such with null values in the maxdate column . Is there something that I am doing wrong?

image.jpeg

Views

355

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Aug 22, 2016 Aug 22, 2016

Copy link to clipboard

Copied

What about something like

and meedate BETWEEN DATEADD(day,-31,NOW()) AND NOW()

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Aug 22, 2016 Aug 22, 2016

Copy link to clipboard

Copied

I don't know what database you're using, but in Oracle, if you do a MAX(anything) without giving it an alias, it just shows up as an undefined column.  Try:

SELECT Stuid, trim(concat(name.fname,' ',name.lname)) AS OVER_30_Days, ltfname as instructor,

     Max(meedate) as meedate

FROM name LEFT JOIN meeting ON meeStuid = Stuid

HTH,

^_^

PS.. it's too late, I'm sure, but I wouldn't use reserved words for table or column names (you named a table "name".)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Aug 22, 2016 Aug 22, 2016

Copy link to clipboard

Copied

I suspect you have a problem with your joins. Try changing "name LEFT JOIN meeting" to "name RIGHT JOIN meeting" and see if you get what you expect.

Cheers

Eddie

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Aug 22, 2016 Aug 22, 2016

Copy link to clipboard

Copied

You are lucky. Looking again at your query, I am surprised it gave you any result at all. It needs to be improved some more after you apply the suggestions from WolfShade and EddieLotter.

You should:

1) qualify all column names with the respective table name followed by a dot;

2) move the meedate condition to the WHERE clause;

3) avoid givig a table and a column the same name, squadlt.

The same query would then end up looking like the following (I have guessed which columns beloong to which tables):

SELECT name.Stuid, trim(concat(name.fname,' ',name.lname)) AS OVER_30_Days, name.ltfname as instructor, Max(meeting.meedate) as maxMeeDate

FROM name 

LEFT JOIN meeting ON meeting.meeStuid = name.Stuid

LEFT JOIN squadlt ON squadlt.squadlt = name.ltid

WHERE meeting.meeStuid is null

AND meeting.meedate BETWEEN DATEADD(day,-31,NOW()) AND NOW()

AND  name.type = 'pupil'

AND name.city = 'jackson'

AND name.status = 'a'

AND name.Stuid <> '432'

AND name.Stuid <> '5'

AND name.Stuid <> '183' 

ORDER BY name.ltfname, OVER_30_Days

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Aug 22, 2016 Aug 22, 2016

Copy link to clipboard

Copied

LEFT JOIN meeting ON meeting.meeStuid = name.Stuid

LEFT JOIN squadlt ON squadlt.squadlt = name.ltid

Then again, one would expect, for a join, something like

LEFT JOIN meeting ON meeting.stuid = name.stuid

LEFT JOIN squadlt ON squadlt.ltid= name.ltid

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Aug 26, 2016 Aug 26, 2016

Copy link to clipboard

Copied

LATEST

Thanks BKBK,

I spent a few days reviewing my code as well as yours and the other suggestions. The output for each is done correctly however the last date of attendance or max(meedate) still shows up as a empty value. I think this is because my query is set up to find the stuid that does not exist or has a null value within that 30 day date span. Then when i ask mysql to pull the student's most recent date of attendance within the same query it does not include it because to mysql no max(meedate) exist for a stuid that is null anyway for dates that i am specifying. i will try re-writing the entire query differently to see if the output will show both the students who are over 30 days absent from class and their last date of attendance in that class.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Aug 23, 2016 Aug 23, 2016

Copy link to clipboard

Copied

This question is also posted to StackOverflow here: http://stackoverflow.com/questions/39082069/missing-dates-on-query-output

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation