sheltermanager

Open full view…

SQL commands to alter report: weight

fletch654
Thu, 03 Sep 2020 16:39:48 GMT

I need a report showing 2020 felines only with name, date of original entry, gender, and weight. I tried to use the “Weight History (Shelter Animals)” report, but it's looking at the log for the weight history. I need it to just list the weight on the animal screen. Also, not just the animals currently in the shelter. The original report looks at only in-house animals. Any help would be greatly appreciated.

fletch654
Thu, 03 Sep 2020 16:47:39 GMT

In date order as a list; not separated with a header for each animal.

srupe05
Fri, 04 Sep 2020 23:03:29 GMT

Very basic table with date brought in, shelter code, animal name, sex, and display weight. Includes a date selector to select animals brought in between two dates. Uses species name "Feline" - edit if using "Cat". Ordered by date brought in (unsure how to removed the time from the date brought in.) SELECT a.ShelterCode, a.AnimalName, s.SpeciesName, sx.Sex, a.Weight, a.DateBroughtIn FROM animal a INNER JOIN species s ON s.ID = a.SpeciesID INNER JOIN lksex sx ON sx.ID = a.Sex WHERE s.SpeciesName LIKE 'Feline' AND DateBroughtIn BETWEEN '$ASK DATE Enter from date$' AND '$ASK DATE Enter to date$' and Nonshelteranimal=0 ORDER BY a.DateBroughtIn html for the above SQL: $$HEADER <table border=1> <tr><td><b>Date of Entry</b></td> <td><b>Code</b></td> <td><b>Name</b></td> <td><b>Sex</b></td> <td><b>Weight</b></td></tr> HEADER$$ $$BODY<tr> <td>$DATEBROUGHTIN</td> <td>$SHELTERCODE</td> <td>$ANIMALNAME</td> <td>$SEX</td> <td>$WEIGHT</td> </tr> BODY$$ $$FOOTER </table> FOOTER$$

fletch654
Sun, 06 Sep 2020 00:34:43 GMT

Hello. Thank you so much for your time to write this report. I copied it into a new report and changed feline to cat. When I run the report I the following is displayed: Criteria: Enter from date: 9/1/19 Enter to date: 8/31/20 syntax error at or near "CAT" LINE 6: WHERE s.SpeciesName LIKE ‘CAT’ AND DateBroughtIn... ^ No data to show on the report. The report syntax changes, too. The WHERE line changes to: WHERE s.SpeciesName LIKE &#8216;CAT&#8217; AND DateBroughtIn BETWEEN &#8216;$ASK DATE Enter from date$&#8217; AND &#8216;$ASK DATE Enter to date$&#8217; and Nonshelteranimal=0 I tried to figure out the problem, but I don't know SQL. We need this data to satisfy a grant we received. Again, any help would be appreciated. Please let me know if this is outside the allowable posts for this forum. I don't want to wear out my welcome. Thank you, Tobie Fletcher Tails of Nye County Pahrump, NV 702-860-3102

fletch654
Sun, 06 Sep 2020 00:36:30 GMT

I apologize for my error above. The WHERE line changes to: WHERE s.SpeciesName LIKE &#8216;CAT&#8217; AND DateBroughtIn BETWEEN &#8216;$ASK DATE Enter from date$&#8217; AND &#8216;$ASK DATE Enter to date$&#8217; and Nonshelteranimal=0

fletch654
Sun, 06 Sep 2020 00:44:34 GMT

This is strange I paste in the correct info, but when I hit select it changes to the original. Am I losing my mind? No, I get it now. The apostrophes change to their html & codes of #8216; and #8217; I don't know if that matters to the syntax of the report. Here is the changed WHERE line. Imagine the _ characters aren't there. I put them there to disrupt the html codes. WHERE s.SpeciesName LIKE &_#_8216 ;_CAT&_#_8217_; AND DateBroughtIn BETWEEN &_#_8216 ;_$ASK DATE Enter from date$_&_#_8217; AND &_#_8216 ;_$ASK DATE Enter to date_$_&_#8217_; and Nonshelteranimal=0 I apologize if this is confusing.

fletch654
Sun, 06 Sep 2020 00:46:02 GMT

I'm going to scream. I used the underscore character, but apparently that is a command to add italics. Well, hopefully you understand what I said. My brain is officially mush.

robinrt
Sun, 06 Sep 2020 08:55:05 GMT

Yes, it does matter - your apostrophes are smartquotes instead of the apostrophe character, it does look like that's the error message you are receiving? I don't see anything else wrong with your query apart from the fact that CAT isn't likely to match unless that's what the species name is in your data as it's case sensitive.

fletch654
Tue, 08 Sep 2020 23:31:07 GMT

Hi again. I fixed the case on Cat. I'm still getting the same error message. This is the WHERE statement: WHERE s.SpeciesName LIKE 'Cat' AND DateBroughtIn BETWEEN '$ASK DATE Enter from date$' AND 'ASK DATE' Enter to date and Nonshelteranimal=0 This is the error message: syntax error at or near "DateBroughtIn" LINE 6: WHERE s.SpeciesName LIKE 'Cat' DateBroughtIn BETWEEN 'ASK DA... ^ Does the fact that we're in the USA make any difference? I couldn't find SpeciesID in our wordkeys glossary. Do we use different syntax? Thank you, again, for all your help.

robinrt
Wed, 09 Sep 2020 08:10:13 GMT

The second ASK token is broken. Your WHERE clause should look like this: --- WHERE s.SpeciesName LIKE 'Cat' AND DateBroughtIn BETWEEN ‘$ASK DATE Enter from date$’ AND ‘$ASK DATE Enter to date$' AND NonShelterAnimal=0 ---

fletch654
Wed, 09 Sep 2020 16:42:26 GMT

It's finally working. Thank you sooo much for your time and patience. After you fixed the WHERE statement I had to do a few more fixes. I used other reports to reference the correct syntax. Here's what the final looks like: SELECT a.ShelterCode, a.AnimalName, s.SpeciesName, sx.Sex, a.Weight, a.DateBroughtIn FROM animal a INNER JOIN species s ON s.ID = a.SpeciesID INNER JOIN lksex sx ON sx.ID = a.Sex WHERE s.SpeciesName LIKE 'Cat' AND DateBroughtIn BETWEEN '$ASK DATE Enter from date$' AND '$ASK DATE Enter to date$' AND NonShelterAnimal=0 ORDER BY a.DateBroughtIn, s.SpeciesName Again, I can't thank you enough.